postgresql - Postgresql 12.5 "relation already exists" when using "create table if not exists"

Question

I'm using PostgreSQL 12.5 on AWS for millions of events. My tables are partitioned by day by day so when the request comes, I use "CREATE TABLE IF NOT EXISTS ... PARTITION OF" syntax for every request.

I expect to skip the command if the table already exists. But PostgreSQL didn't do that. PostgreSQL tried to create tables multiple times instead of skip.

PostgreSQL Error Logs are following

2021-05-30 00:00:00 UTC:IP_ADDRESS(24921):dbname@username:[16666]:ERROR:  relation "tablename_20210530" already exists
2021-05-30 00:00:00 UTC:IP_ADDRESS(24921):dbname@username:[16666]:STATEMENT:  CREATE TABLE IF NOT EXISTS tablename_20210530 PARTITION OF tablename FOR VALUES FROM ('2021-05-30') TO ('2021-05-31')

The SQL Exception is following;

{
    "errorType": "error",
    "errorMessage": "relation \"tablename_20210530\" already exists",
    "code": "42P07",
    "length": 110,
    "name": "error",
    "severity": "ERROR",
    "file": "heap.c",
    "line": "1155",
    "routine": "heap_create_with_catalog",
    "stack": [
        "error: relation \"tablename_20210530\" already exists",
        "    at Parser.parseErrorMessage (/var/task/node_modules/pg-protocol/dist/parser.js:278:15)",
        "    at Parser.handlePacket (/var/task/node_modules/pg-protocol/dist/parser.js:126:29)",
        "    at Parser.parse (/var/task/node_modules/pg-protocol/dist/parser.js:39:38)",
        "    at Socket.<anonymous> (/var/task/node_modules/pg-protocol/dist/index.js:10:42)",
        "    at Socket.emit (events.js:315:20)",
        "    at addChunk (internal/streams/readable.js:309:12)",
        "    at readableAddChunk (internal/streams/readable.js:284:9)",
        "    at Socket.Readable.push (internal/streams/readable.js:223:10)",
        "    at TCP.onStreamRead (internal/stream_base_commons.js:188:23)"
    ]
}

Any idea why this is happening?

PostgreSQL Version:

PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit

Table Scheme:

create table tablename
(
    id bigint default nextval('tablename_partition_id_seq'::regclass) not null,
    col1 timestamp(0) default CURRENT_TIMESTAMP not null,
    col2 varchar(255) not null,
    constraint tablename_partition_pkey
        primary key (id, col1)
)
partition by RANGE ("col1");

create table tablename_20210530
partition of tablename
(
    constraint tablename_20210530_pkey
        primary key (id, col1)
)
FOR VALUES FROM ('2021-05-30 00:00:00') TO ('2021-05-31 00:00:00');

SQL Statement:

CREATE TABLE IF NOT EXISTS tablename_20210530 PARTITION OF tablename FOR VALUES FROM ('2021-05-30') TO ('2021-05-31');

My code runs the create statement before the insert statement under the thousands of requests. Because every request has a different "col1" value. And I can not control that. so the create table statement is running every time.

标签： postgresqlpartition