Re: Error that shouldn't happen?

2017-05-18 22:39 GMT+02:00 Rob Brucks <rob(dot)brucks(at)rackspace(dot)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(dot)g(dot)johnston(at)gmail(dot)com>
> *Date: *Thursday, May 18, 2017 at 3:31 PM
> *To: *Rob Brucks <rob(dot)brucks(at)rackspace(dot)com>
> *Cc: *"pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
> *Subject: *Re: [GENERAL] Error that shouldn't happen?
>
>
>
> On Thu, May 18, 2017 at 1:18 PM, Rob Brucks <rob(dot)brucks(at)rackspace(dot)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-gg(at)mail(dot)gmail(dot)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.
>
>
>