Race condition in create table

From: Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Race condition in create table
Date: 2018-10-09 13:59:37
Message-ID: 93336f06-86c2-71b6-abe3-0d42cc614b2b@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I wonder if it is considered to be expected behavior that concurrent
execution of "create table if not exists" may return errors:

knizhnik(at)knizhnik:~/dtm-data$ pgbench -n -c 8 -t 20 -f create_table.sql
postgres
client 2 aborted in command 0 (SQL) of script 0; ERROR:  duplicate key
value violates unique constraint "pg_type_typname_nsp_index"
DETAIL:  Key (typname, typnamespace)=(t2, 2200) already exists.

client 0 aborted in command 0 (SQL) of script 0; ERROR:  duplicate key
value violates unique constraint "pg_type_typname_nsp_index"
DETAIL:  Key (typname, typnamespace)=(t2, 2200) already exists.

client 1 aborted in command 0 (SQL) of script 0; ERROR:  duplicate key
value violates unique constraint "pg_type_typname_nsp_index"
DETAIL:  Key (typname, typnamespace)=(t2, 2200) already exists.

client 3 aborted in command 0 (SQL) of script 0; ERROR:  duplicate key
value violates unique constraint "pg_type_typname_nsp_index"
DETAIL:  Key (typname, typnamespace)=(t2, 2200) already exists.

NOTICE:  relation "t2" already exists, skipping
client 5 aborted in command 0 (SQL) of script 0; ERROR:  duplicate key
value violates unique constraint "pg_type_typname_nsp_index"
DETAIL:  Key (typname, typnamespace)=(t2, 2200) already exists.

client 6 aborted in command 0 (SQL) of script 0; ERROR:  duplicate key
value violates unique constraint "pg_type_typname_nsp_index"
DETAIL:  Key (typname, typnamespace)=(t2, 2200) already exists.

NOTICE:  relation "t2" already exists, skipping
NOTICE:  relation "t2" already exists, skipping
NOTICE:  relation "t2" already exists, skipping
NOTICE:  relation "t2" already exists, skipping
client 7 aborted in command 0 (SQL) of script 0; ERROR:  duplicate key
value violates unique constraint "pg_type_typname_nsp_index"
DETAIL:  Key (typname, typnamespace)=(t2, 2200) already exists.

NOTICE:  relation "t2" already exists, skipping
NOTICE:  relation "t2" already exists, skipping
NOTICE:  relation "t2" already exists, skipping
NOTICE:  relation "t2" already exists, skipping
NOTICE:  relation "t2" already exists, skipping
NOTICE:  relation "t2" already exists, skipping
NOTICE:  relation "t2" already exists, skipping
NOTICE:  relation "t2" already exists, skipping
NOTICE:  relation "t2" already exists, skipping
NOTICE:  relation "t2" already exists, skipping
NOTICE:  relation "t2" already exists, skipping
NOTICE:  relation "t2" already exists, skipping
NOTICE:  relation "t2" already exists, skipping
NOTICE:  relation "t2" already exists, skipping

--------------------------------------------------------------------------------

For partitions behavior is slightly different:

knizhnik(at)knizhnik:~/dtm-data$ pgbench -n -c 8 -t 20 -f create_part.sql
postgres
NOTICE:  relation "t_1" already exists, skipping
client 0 aborted in command 0 (SQL) of script 0; ERROR:  relation "t_1"
already exists

NOTICE:  relation "t_1" already exists, skipping
NOTICE:  relation "t_1" already exists, skipping
NOTICE:  relation "t_1" already exists, skipping
client 1 aborted in command 0 (SQL) of script 0; ERROR:  relation "t_1"
already exists

NOTICE:  relation "t_1" already exists, skipping
NOTICE:  relation "t_1" already exists, skipping
NOTICE:  relation "t_1" already exists, skipping
NOTICE:  relation "t_1" already exists, skipping
client 3 aborted in command 0 (SQL) of script 0; ERROR:  relation "t_1"
already exists

NOTICE:  relation "t_1" already exists, skipping
NOTICE:  relation "t_1" already exists, skipping
NOTICE:  relation "t_1" already exists, skipping
NOTICE:  relation "t_1" already exists, skipping
client 6 aborted in command 0 (SQL) of script 0; ERROR:  relation "t_1"
already exists

NOTICE:  relation "t_1" already exists, skipping
NOTICE:  relation "t_1" already exists, skipping
NOTICE:  relation "t_1" already exists, skipping
NOTICE:  relation "t_1" already exists, skipping
client 2 aborted in command 0 (SQL) of script 0; ERROR:  relation "t_1"
already exists

NOTICE:  relation "t_1" already exists, skipping
NOTICE:  relation "t_1" already exists, skipping
NOTICE:  relation "t_1" already exists, skipping
client 5 aborted in command 0 (SQL) of script 0; ERROR:  relation "t_1"
already exists

client 4 aborted in command 0 (SQL) of script 0; ERROR:  relation "t_1"
already exists

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Attachment Content-Type Size
create_table.sql application/sql 42 bytes
create_part.sql application/sql 76 bytes

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2018-10-09 14:04:35 Re: Proposal for Signal Detection Refactoring
Previous Message Dmitry Dolgov 2018-10-09 13:59:28 Re: Index Skip Scan