pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: gatekeeper.mail@gmail.com
Subject: BUG #16763: CREATE TABLE IF NOT EXISTS fails with "relation exists"
Date: Fri, 04 Dec 2020 17:08:29 +0000
Message-ID: <16763-a1c5f839345c4e80@postgresql.org> (raw)

The following bug has been logged on the website:

Bug reference:      16763
Logged by:          Andy S
Email address:      gatekeeper.mail@gmail.com
PostgreSQL version: 11.2
Operating system:   Linux
Description:        

Our application is run with a number of simultaneous instances each of them
emits both DDL and DML to the DB.

For example, at the very start they ensure base table structure exists so
the emit queries like:

CREATE TABLE IF NOT EXISTS tblname (...);

During runtime they also emit queries to create new partitions to existing
partitioned tables. Since both startup and/or runtime DDL could be emited
simultaneously they suddenly are. This leads to errors like this:

2020-12-04 16:38:45.785 GMT [18814] ERROR:  relation
"tblname_short_60_2657_3" already exists
2020-12-04 16:38:45.785 GMT [18814] STATEMENT:  CREATE TABLE IF NOT EXISTS
tblname_short_60_2657_3 PARTITION OF tblname_short_60_2657 FOR VALUES WITH
(MODULUS 10, REMAINDER 3)
2020-12-04 16:38:45.786 GMT [18849] ERROR:  relation
"tblname_short_60_2657_3" already exists
2020-12-04 16:38:45.786 GMT [18849] STATEMENT:  CREATE TABLE IF NOT EXISTS
tblname_short_60_2657_3 PARTITION OF tblname_short_60_2657 FOR VALUES WITH
(MODULUS 10, REMAINDER 3)
2020-12-04 16:38:47.306 GMT [18868] ERROR:  relation
"tblname_short_60_2657_5" already exists
2020-12-04 16:38:47.306 GMT [18868] STATEMENT:  CREATE TABLE IF NOT EXISTS
tblname_short_60_2657_5 PARTITION OF tblname_short_60_2657 FOR VALUES WITH
(MODULUS 10, REMAINDER 5)
2020-12-04 16:38:50.118 GMT [18821] ERROR:  relation
"tblname_medium_180_620_0" already exists
2020-12-04 16:38:50.118 GMT [18821] STATEMENT:  CREATE TABLE IF NOT EXISTS
tblname_medium_180_620_0 PARTITION OF tblname_medium_180_620 FOR VALUES WITH
(MODULUS 10, REMAINDER 0)
2020-12-04 16:38:52.247 GMT [15021] ERROR:  relation
"tblname_medium_180_620_4" already exists
2020-12-04 16:38:52.247 GMT [15021] STATEMENT:  CREATE TABLE IF NOT EXISTS
tblname_medium_180_620_4 PARTITION OF tblname_medium_180_620 FOR VALUES WITH
(MODULUS 10, REMAINDER 4)
2020-12-04 16:38:52.719 GMT [14958] ERROR:  relation
"tblname_medium_180_620_5" already exists
2020-12-04 16:38:52.719 GMT [14958] STATEMENT:  CREATE TABLE IF NOT EXISTS
tblname_medium_180_620_5 PARTITION OF tblname_medium_180_620 FOR VALUES WITH
(MODULUS 10, REMAINDER 5)


The same problem persists at least at PostgreSQL 12.2. The server should not
raise exception level error on `CREATE ? IF NOT EXISTS` queries.
Here's the doc (version 11):

IF NOT EXISTS
Do not throw an error if a relation with the same name already exists. A
notice is issued in this case. Note that there is no guarantee that the
existing relation is anything like the one that would have been created.



view thread (3+ messages)  latest in thread

Message-ID: <16763-a1c5f839345c4e80@postgresql.org>
Permalink:  ../16763-a1c5f839345c4e80@postgresql.org/
Also on:    postgresql.org/message-id/16763-a1c5f839345c4e80@postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, gatekeeper.mail@gmail.com
  Subject: Re: BUG #16763: CREATE TABLE IF NOT EXISTS fails with "relation exists"
  In-Reply-To: <16763-a1c5f839345c4e80@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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