date:20200608

checking existence of a table before updating its SERIAL

2020-06-08 Thread Matthias Apitz



Hello,

We're updating the SERIAL of a bunch of tables with a SQL script which
does for any table:

/* table: idm_tasktab */
DO $$
DECLARE
  max_id int;
BEGIN
  SELECT INTO max_id GREATEST(COALESCE(max(taskid), 0),0) + 1 FROM idm_tasktab;
  RAISE NOTICE '% % %', 'idm_tasktab', 'taskid', max_id ;
  EXECUTE 'ALTER SEQUENCE idm_tasktab_taskid_seq RESTART ' || max_id::text;
END $$ LANGUAGE plpgsql;

Can some kind soul help me with doing a test for the existence of the
table to avoid the error message about non existing relation? 

Thanks in advance.

matthias

-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

Re: checking existence of a table before updating its SERIAL

2020-06-08 Thread David G. Johnston

On Monday, June 8, 2020, Matthias Apitz  wrote:

>
> Can some kind soul help me with doing a test for the existence of the
> table to avoid the error message about non existing relation?
>


 https://www.postgresql.org/docs/12/catalogs-overview.html

David J.

Re: Multitenent architecture

2020-06-08 Thread Vasu Madhineni

Hi All,

Thanks a lot for information, I will look into it and get back to you.

Regards,
Vasu Madhineni

On Sun, Jun 7, 2020 at 1:21 AM Michel Pelletier 
wrote:

>
> On Sat, Jun 6, 2020 at 3:14 AM Vasu Madhineni 
> wrote:
>
>> Hi Rob,
>>
>> Our environment is medical clinical data, so each clinic as a tenant.
>> Approximately 500+ tenants with 6TB data.
>>
>> Thank you in advance.
>>
>>
> There's a good article on the AWS blog on multi tenancy with postgres:
>
>
> https://aws.amazon.com/blogs/database/multi-tenant-data-isolation-with-postgresql-row-level-security/
>
> This is similar to Laurenz's second suggestion.
>
> -Michel
>
>
>> Regards,
>> Vasu Madhineni
>>
>> On Fri, Jun 5, 2020 at 6:09 PM Rob Sargent  wrote:
>>
>>>
>>>
>>> On Jun 5, 2020, at 2:54 AM, Vasu Madhineni 
>>> wrote:
>>>
>>> 
>>> If the data size is more than 6TB, which approach better?
>>>
>>> On Fri, Jun 5, 2020 at 2:57 PM Laurenz Albe 
>>> wrote:
>>>
 On Thu, 2020-06-04 at 23:52 +0800, Vasu Madhineni wrote:
 > We are planning a POC on multitenant architecture in Postgres, Could
 you please
 > help us with steps for multitenant using schema for each application
 model.

 For few tenants, you can keep identical tables in several schemas and
 set "search_path" to select a tenant.

 With many tenants, you are better off with one table that holds the
 data for all clients.  You can use Row Level Security to have each
 tenant see only his or her data, and it might be a good idea to
 use list partitioning on the tenant ID.

 Yours,
 Laurenz Albe
 --
 Cybertec | https://www.cybertec-postgresql.com

 The question is How many separate data owners?
>>>
>>

Concurrenctly running CREATE TEMP TABLE IF NOT EXISTS [...] AS [...]

2020-06-08 Thread Thorsten Schöning

Hi all,

I have an app exposing web services to generate reports. Those web
services accept multiple reports per request and calculate them
concurrently. There's one transaction spanning each entire request and
used by ALL spawned threads. The app makes sure that e.g. committing
transactions is handled by ONE thread only and individual statements
are NEVER reused by multiple threads.

There are two types of reports: Some need to read data from the DB
themself, others can work on the data provided by the former ones. The
current implementation always creates temporary tables for results of
some query, so that one can simply check if some temporary table is
available and either only query that or create it before as necessary.

I'm using "CREATE TEMP TABLE IF NOT EXISTS [...] AS [...]" and the
associated queries can take a long time. So the following lists some
questions about executing those concurrently, even thouzgh I've
already read threads like the following:

> The bottom line is that CREATE TABLE IF NOT EXISTS doesn't pretend
> to handle concurrency issues any better than regular old CREATE
> TABLE, which is to say not very well.[...]

https://www.postgresql.org/message-id/ca+tgmozadyvtwbfp1fl2smzbihcwt4uprzrlnnx1nb30ku3...@mail.gmail.com

When the table needs to be created, when is it visible to other
threads using the same transaction, before or after executing the
additional query?

Am I correct that with using "IF NOT EXISTS" the associated query is
only executed as well if the table needs to be created? In theory
those two things could be independent of each other, maybe resulting
in duplicated rows or stuff like that per execution. But doesn't seem
so according to my tests.

Am I correct that in my described setup I need to make sure on my own
that only one thread creates each individual temporary table and
executes the associated query? Otherwise it might happen that multiple
attempts creating the table at the same time simply fails and the only
question is if this happens before or after the associated query.

Or would creating the table itself succeeds even with many threads,
but some of them simply wouldn't read any data, becauser the
associated query is executed in a second step by that thread that
created the table in the first place? I don't think so, though.

Am I correct that because of the same transaction used by multiple
threads I need to synchronize them on web service-level? E.g. things
like advisory locks won't work because they have session or
transaction level and would be granted to all threads instantly.

47 matches

Mail list logo