Race condition in create table

Tue, 09 Oct 2018 07:00:20 -0700

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

Attachment: create_table.sql
Description: application/sql

Attachment: create_part.sql
Description: application/sql

Reply via email to