Re: BUG #16637: Postgressql throws exception 'relation already exists' when trying to execute partition from code

PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> I noticed that sometimes the npgsql provider is throwing exceptions
> "relation already exists" when i try to create a partition with parallel
> threads in functioning.

> Ex:
> printed sql that resulted in exception on console :
> CREATE TABLE IF NOT EXISTS Messages_2020_d_268_cid_45 PARTITION OF
> "public".Messages_2020_d_268 FOR VALUES in (45)

IF NOT EXISTS is not intended to provide a hard guarantee against
concurrent-creation race conditions. It just looks to see if there's
such a table right now, and if not it proceeds with creation. So
if two sessions do the same thing at more or less the same instant,
they will both conclude the table's not there yet.

It's possible to imagine adding new forms of locking to prevent that,
but it would be a lot of overhead that would be quite wasted for
most usage. So I doubt we'll ever do that.

Note that if you are doing this inside a long-running transaction,
you're more or less shooting yourself in the foot, because the new
table will not become visible to other transactions until you
commit. (Which is a feature, not a bug.) So it's advisable to
commit the table creation as quickly as possible to reduce the
length of the race-condition window.

regards, tom lane