Thread: BUG #16763: CREATE TABLE IF NOT EXISTS fails with "relation exists"

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.

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

IF NOT EXISTS doesn't attempt to be bulletproof: it just checks at the
start of the command to see if the object name is already there.  So
it's not sufficient to guard concurrent creations.  You can call that
a bug if you like, but it's quite unlikely to change anytime soon.

You might consider using advisory locks [1] to keep your various sessions
from trying to do this at the exact same time.

            regards, tom lane

[1] https://www.postgresql.org/docs/11/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS