Search Postgresql Archives

Re: Error that shouldn't happen?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.johnston@xxxxxxxxx> wrote:
On Thu, May 18, 2017 at 1:18 PM, Rob Brucks <rob.brucks@xxxxxxxxxxxxx> 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-gg@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.'

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux