pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #16637: Postgressql throws exception 'relation already exists' when trying to execute partition from code
2+ messages / 2 participants
[nested] [flat]

* BUG #16637: Postgressql throws exception 'relation already exists' when trying to execute partition from code
@ 2020-09-26 15:19  PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 1 reply; 2+ messages in thread

From: PG Bug reporting form @ 2020-09-26 15:19 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: kulluadhikari@gmail.com

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: BUG #16637: Postgressql throws exception 'relation already exists' when trying to execute partition from code
@ 2020-09-26 20:21  Tom Lane <tgl@sss.pgh.pa.us>
  parent: PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2020-09-26 20:21 UTC (permalink / raw)
  To: kulluadhikari@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2020-09-26 20:21 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-09-26 15:19 BUG #16637: Postgressql throws exception 'relation already exists' when trying to execute partition from code PG Bug reporting form <noreply@postgresql.org>
2020-09-26 20:21 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox