Обсуждение: BUG #16637: Postgressql throws exception 'relation already exists' when trying to execute partition from code

The following bug has been logged on the website:

Bug reference:      16637
Logged by:          k2ibegin kd
Email address:      kulluadhikari@gmail.com
PostgreSQL version: 12.0
Operating system:   Mac OS
Description:

Hi,

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)

Expectation:
expectation is that query should just execute without throwing exception,
the same behavior as if executing the query from pgadmin/psql.

Code
var partitionSqlString =
BuildConnectionIdBasedPartition(connectionIdPartitionTableName,
dayPartitionTableName, connectionId);
await using var archiveContext = new
ArchiveMessageDbContext(_configuration);
try
{
await archiveContext.Database.ExecuteSqlRawAsync(partitionSqlString);
}
catch (Exception e)
{
_logger.LogError($"Can not create connectionID level 2 partition, exception
occured {e}: ");
_logger.LogError(partitionSql);
throw;
}

Similar bug ??

I also found similar bug opened here
https://www.postgresql.org/message-id/15968-08549e78d5269c95%40postgresql.org.

but did not find any follow up on it, so i am not sure if this is the bug
happening at postgres level OR at provider level

Setup

.Net Core 3.1
EfCore 3.1.4
NpgSql.EntityFrameworkCore.PostgreSQL 3.1.4

PG Bug reporting form <noreply@postgresql.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