Обсуждение: Race condition in create table

Поиск
Список
Период
Сортировка

Race condition in create table

От
Konstantin Knizhnik
Дата:
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
Вложения

Re: Race condition in create table

От
Tom Lane
Дата:
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