Postgres: CREATE TABLE IF NOT EXISTS ⇒ 23505-CodePudding

I start multiple programs that all more or less simultaneously do

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

Sometimes this works perfectly. But most of the time, one or more of the programs crash with the error:

23505: duplicate key value violates unique constraint "pg_class_relname_nsp_index".

Can somebody explain to me how the actual Christmas tree CREATE TABLE IF NOT EXISTS is giving me an error message about the table already existing? Isn't that, like, the entire point of this command?? What is going on here? More to the point, how do I get it to actually work correctly?

After this command, there's also a couple of CREATE INDEX IF NOT EXISTS commands. These occasionally fail in a similar way too. But most of the time, it's the CREATE TABLE statement that fails.

CodePudding user response：

You can reproduce this with 2 parallel sessions:

First session:

begin;
create table if not exists log(id bigint generated always as identity, t timestamp with time zone, message text not null);

Notice that the first session did not commit yet, so the table does not really exists.

Second session:

begin;
create table if not exists log(id bigint generated always as identity, t timestamp with time zone, message text not null);

The second session will now block, as the name "log" is reserved by the first session. But it is not yet known, if the transaction, that reserved it, will be committed or not.

Then, when you commit the first session, the second will fail:

ERROR:  duplicate key value violates unique constraint "pg_class_relname_nsp_index"
DETAIL:  Key (relname, relnamespace)=(log_id_seq, 2200) already exists.

To avoid it you have to make sure that the check for existence of a table, is done after some common advisory lock is taken:

begin;
select pg_advisory_xact_lock(12345);
-- any bigint value, but has to be the same for all parallel sessions
create table if not exists log(id bigint generated always as identity, t timestamp with time zone, message text not null);
commit;