Race condition in create table
I wonder if it is considered to be expected behavior that concurrent
execution of "create table if not exists" may return errors:
knizhnik@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@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
Konstantin Knizhnik <k.knizhnik@postgrespro.ru> writes:
I wonder if it is considered to be expected behavior that concurrent
execution of "create table if not exists" may return errors:
No, there's not a guarantee that IF NOT EXISTS will work in such
scenarios. As with everything else about that feature, it's a
quick-and-dirty, not very well designed effort.
regards, tom lane