Re: [GENERAL] Error that shouldn't happen?

[vinny]

On 2017-05-18 21:48, Rob Brucks wrote:

Hello Everyone,

I am unable to figure out how the trigger was able to successfully
create the table, but then fail creating the index.  I would have
expected one thread to "win" and create both the table and index, but
other threads would fail when creating the table… but NOT when
creating the index.


First, I agree whole heartedly with the other's suggestions to "not do 
this".
Create a cronjob of whatever that prepares the required tables before 
you need them, empty tables are cheap.


Second: IF EXISTS only tells you that an object exists and is ready for 
use.
So what happens when a process is in the middle of creating that object? 
Does IF EXISTS tell you it exists or not?



What you need (accepting that this whole trigger based approach is 
probably not the best option)
is a proper locking mechanism. A "thundering herd" protection. The first 
time the trigger is triggered
it should set a lock (n advisory lock for example) that subsequent calls 
to the same trigger
can lok at to see if the table they need is being created at that time, 
so they will skip the create commands

and *WAIT* for the first process to complete before using the table.

That *WaIT* is important, and also something you probably don't want, 
especially if you have a busy database.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Error that shouldn't happen?

[David G. Johnston]

On Thu, May 18, 2017 at 1:46 PM, John R Pierce  wrote:

> On 5/18/2017 1:40 PM, Andrew Kerber wrote:
>
>> It appears to me you might be making this a lot more difficult than
>> necessary. Why not just pre-create the required partitions daily or weekly
>> or monthly? Or do you have a requirement that a new partition only be
>> created the first time it is required?
>>
>
> +1
>
> we create new partitions in advance of their being needed as part of a
> maintenance process that's strictly single threaded.


​While I've been trying to explain the mechanics involved here I agree that
the whole idea of exceptionally creating a table in a trigger is just
asking for trouble.  I do get the idea of not wanting an external
maintenance process involved that needs to be setup and maintained, and
maybe there are now better options with "workers", but the trade-offs
involved would start leaning me heavily toward having a maintenance
routine, especially in a production environment, and at that point you
should mirror production in development.

David J.

Re: [GENERAL] Error that shouldn't happen?

[John R Pierce]

On 5/18/2017 1:40 PM, Andrew Kerber wrote:
It appears to me you might be making this a lot more difficult than 
necessary. Why not just pre-create the required partitions daily or 
weekly or monthly? Or do you have a requirement that a new partition 
only be created the first time it is required?


+1

we create new partitions in advance of their being needed as part of a 
maintenance process that's strictly single threaded.


--
john r pierce, recycling bits in santa cruz



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Error that shouldn't happen?

[Pavel Stehule]

2017-05-18 22:39 GMT+02:00 Rob Brucks <rob.bru...@rackspace.com>:

> Thanks.
>
>
>
> I can code an exception block to handle the table problem, and probably
> one for the index collision too.
>

Creating partitions dynamically is pretty bad idea. You have to handle a
exceptions - it enforces implicit subtransaction (some slowdown) or you
have to use a explicit locks - or some mix of all.

Writing this code without race condition is not too easy.


>
> My point is how did two concurrent threads successfully create the same
> table?  That had to have happened if one of the threads hit a duplicate
> index error.
>

PostgreSQL is based on processes. The reason was described by David well.


>
>
> It almost seems like Postgres skipped checking for duplicate tables due to
> some timing issue.  I don't want my DB to ending up hosed by something like
> that.
>
>
>
> Thanks,
>
> Rob
>
>
>
> *From: *"David G. Johnston" <david.g.johns...@gmail.com>
> *Date: *Thursday, May 18, 2017 at 3:31 PM
> *To: *Rob Brucks <rob.bru...@rackspace.com>
> *Cc: *"pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
> *Subject: *Re: [GENERAL] Error that shouldn't happen?
>
>
>
> On Thu, May 18, 2017 at 1:18 PM, Rob Brucks <rob.bru...@rackspace.com>
> wrote:
>
> According to this post, adding "if not exists" won't really help for race
> conditions.
>
>
>
> "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." - Robert Haas
>
>
>
> https://www.postgresql.org/message-id/CA+TgmoZAdYVtwBfp1FL2sMZbiHCWT4UP
> rzrlnnx1nb30ku3...@mail.gmail.com
>
>
>
> It still doesn't explain how the function got past creating the table, but
> failed on the index.  If another thread was also creating the table then
> there should have been lock contention on the create table statement.
>
>
>
>
>
> A​T1: Insert, failed, cannot find table
>
> AT2: Insert, failed, cannot find table
>
> BT2: Create Table, succeeds
>
> BT1: Create Table; fails, it exists now, if exists converts to a warning
>
> CT2: Create Index, succeeds
>
> CT1: Create Index, fails , hard error
>
> DT2: Insert, succeeds
>
> ​DT1: Never Happens
>
>
>
> What that post seems to be describing is that it is possible the "BT1"
> actually hard errors instead of just being converted into a notice.  There
> is no statement visible action to show that interleave but there is an
> underlying race condition since both BT1 and BT2 are executing concurrently.
>
>
>
> In short even with IF NOT EXISTS you are not guaranteed to not fail.  But
> at least IF NOT EXISTS makes the probability of not failing > 0.  It
> doesn't handle the concurrency any better - but it does change the outcome
> in some of those less-than-ideally handled situations.
>
>
>
> David J.
>
>
>

Re: [GENERAL] Error that shouldn't happen?

[David G. Johnston]

On Thu, May 18, 2017 at 1:39 PM, Rob Brucks 
wrote:

> My point is how did two concurrent threads successfully create the same
> table?
>

​You seem to not be understanding that "CREATE TABLE IF NOT EXISTS" can
succeed without actually creating a table...​

​David J.​

​
​

Re: [GENERAL] Error that shouldn't happen?

[Andrew Kerber]

It appears to me you might be making this a lot more difficult than
necessary. Why not just pre-create the required partitions daily or weekly
or monthly? Or do you have a requirement that a new partition only be
created the first time it is required?

On Thu, May 18, 2017 at 3:31 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Thu, May 18, 2017 at 1:18 PM, Rob Brucks 
> wrote:
>
>> According to this post, adding "if not exists" won't really help for race
>> conditions.
>>
>>
>>
>> "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." - Robert Haas
>>
>>
>>
>> https://www.postgresql.org/message-id/CA+TgmoZAdYVtwBfp1FL2s
>> mzbihcwt4uprzrlnnx1nb30ku3...@mail.gmail.com
>>
>>
>>
>> It still doesn't explain how the function got past creating the table,
>> but failed on the index.  If another thread was also creating the table
>> then there should have been lock contention on the create table statement.
>>
>>
>>
> A​T1: Insert, failed, cannot find table
> AT2: Insert, failed, cannot find table
> BT2: Create Table, succeeds
> BT1: Create Table; fails, it exists now, if exists converts to a warning
> CT2: Create Index, succeeds
> CT1: Create Index, fails , hard error
> DT2: Insert, succeeds
> ​DT1: Never Happens
>
> What that post seems to be describing is that it is possible the "BT1"
> actually hard errors instead of just being converted into a notice.  There
> is no statement visible action to show that interleave but there is an
> underlying race condition since both BT1 and BT2 are executing concurrently.
>
> In short even with IF NOT EXISTS you are not guaranteed to not fail.  But
> at least IF NOT EXISTS makes the probability of not failing > 0.  It
> doesn't handle the concurrency any better - but it does change the outcome
> in some of those less-than-ideally handled situations.
>
> David J.
>
>


-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

Re: [GENERAL] Error that shouldn't happen?

[Rob Brucks]

Thanks.

I can code an exception block to handle the table problem, and probably one for 
the index collision too.

My point is how did two concurrent threads successfully create the same table?  
That had to have happened if one of the threads hit a duplicate index error.

It almost seems like Postgres skipped checking for duplicate tables due to some 
timing issue.  I don't want my DB to ending up hosed by something like that.

Thanks,
Rob

From: "David G. Johnston" <david.g.johns...@gmail.com>
Date: Thursday, May 18, 2017 at 3:31 PM
To: Rob Brucks <rob.bru...@rackspace.com>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Error that shouldn't happen?

On Thu, May 18, 2017 at 1:18 PM, Rob Brucks 
<rob.bru...@rackspace.com<mailto:rob.bru...@rackspace.com>> wrote:
According to this post, adding "if not exists" won't really help for race 
conditions.

"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." - Robert Haas

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

It still doesn't explain how the function got past creating the table, but 
failed on the index.  If another thread was also creating the table then there 
should have been lock contention on the create table statement.


A​T1: Insert, failed, cannot find table
AT2: Insert, failed, cannot find table
BT2: Create Table, succeeds
BT1: Create Table; fails, it exists now, if exists converts to a warning
CT2: Create Index, succeeds
CT1: Create Index, fails , hard error
DT2: Insert, succeeds
​DT1: Never Happens

What that post seems to be describing is that it is possible the "BT1" actually 
hard errors instead of just being converted into a notice.  There is no 
statement visible action to show that interleave but there is an underlying 
race condition since both BT1 and BT2 are executing concurrently.

In short even with IF NOT EXISTS you are not guaranteed to not fail.  But at 
least IF NOT EXISTS makes the probability of not failing > 0.  It doesn't 
handle the concurrency any better - but it does change the outcome in some of 
those less-than-ideally handled situations.

David J.

Re: [GENERAL] Error that shouldn't happen?

[David G. Johnston]

On Thu, May 18, 2017 at 1:18 PM, Rob Brucks 
wrote:

> According to this post, adding "if not exists" won't really help for race
> conditions.
>
>
>
> "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." - Robert Haas
>
>
>
> https://www.postgresql.org/message-id/CA+TgmoZAdYVtwBfp1FL2sMZbiHCWT4UP
> rzrlnnx1nb30ku3...@mail.gmail.com
>
>
>
> It still doesn't explain how the function got past creating the table, but
> failed on the index.  If another thread was also creating the table then
> there should have been lock contention on the create table statement.
>
>
>
A​T1: Insert, failed, cannot find table
AT2: Insert, failed, cannot find table
BT2: Create Table, succeeds
BT1: Create Table; fails, it exists now, if exists converts to a warning
CT2: Create Index, succeeds
CT1: Create Index, fails , hard error
DT2: Insert, succeeds
​DT1: Never Happens

What that post seems to be describing is that it is possible the "BT1"
actually hard errors instead of just being converted into a notice.  There
is no statement visible action to show that interleave but there is an
underlying race condition since both BT1 and BT2 are executing concurrently.

In short even with IF NOT EXISTS you are not guaranteed to not fail.  But
at least IF NOT EXISTS makes the probability of not failing > 0.  It
doesn't handle the concurrency any better - but it does change the outcome
in some of those less-than-ideally handled situations.

David J.

Re: [GENERAL] Error that shouldn't happen?

[Rob Brucks]

According to this post, adding "if not exists" won't really help for race 
conditions.

"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." - Robert Haas

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

It still doesn't explain how the function got past creating the table, but 
failed on the index.  If another thread was also creating the table then there 
should have been lock contention on the create table statement.

Thanks,
Rob

From: "David G. Johnston" <david.g.johns...@gmail.com>
Date: Thursday, May 18, 2017 at 3:05 PM
To: Rob Brucks <rob.bru...@rackspace.com>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Error that shouldn't happen?

On Thu, May 18, 2017 at 12:48 PM, Rob Brucks 
<rob.bru...@rackspace.com<mailto:rob.bru...@rackspace.com>> wrote:
I am unable to figure out how the trigger was able to successfully create the 
table, but then fail creating the index.  I would have expected one thread to 
"win" and create both the table and index, but other threads would fail when 
creating the table… but NOT when creating the index.

​I don't fully comprehend the locking involved here but if you want a failure 
while creating the table you shouldn't use "IF NOT EXISTS".  ​On the other side 
adding "IF NOT EXISTS" to the CREATE INDEX will supposedly prevent the error 
you are seeing.

The trigger that failed to create the index also failed to create the table - 
it just didn't care because of the IF NOT EXISTS.  At least this is what I am 
observing from your description.

David J.

Re: [GENERAL] Error that shouldn't happen?

[David G. Johnston]

On Thu, May 18, 2017 at 12:48 PM, Rob Brucks 
wrote:

> I am unable to figure out how the trigger was able to successfully create
> the table, but then fail creating the index.  I would have expected one
> thread to "win" and create both the table and index, but other threads
> would fail when creating the table… but NOT when creating the index.
>

​I don't fully comprehend the locking involved here but if you want a
failure while creating the table you shouldn't use "IF NOT EXISTS".  ​On
the other side adding "IF NOT EXISTS" to the CREATE INDEX will supposedly
prevent the error you are seeing.

The trigger that failed to create the index also failed to create the table
- it just didn't care because of the IF NOT EXISTS.  At least this is what
I am observing from your description.

David J.