Postgres create table asynchronously

Question

i'm struggling with postgres and async task queue

i'm trying to create a new table - whatever worker reach this point first - create the table

using the statment

create table if not exists

'table already exists' exception raised

this is really wierd, because when doing so whith single worker - i.e trying to create the table twice synchronously, the second time it writes a notice (not exception)

PostgreSQL's DDL is transactional. Probably your second worker trying to create table while it the first worker table already created but transaction still not commited. However in such case I got error like ERROR: duplicate key value violates unique constraint "pg_type_typname_nsp_index" DETAIL: Key (typname, typnamespace)=(foo, 16386) already exists. — Abelisto
– Abelisto, Commented Dec 31, 2017 at 9:37
i'm debugging locally, and the problem looks like the 'if not exists' stmt is executed before the lock is release. i.e the first process creates the table, second is waiting for lock (not finishing the execution of create table), when first is commiting, lock is released, and second raise exception - table already exist — gCoh
– gCoh, Commented Dec 31, 2017 at 10:32
Hi @gCoh, did you make any progress on this? I'm trying to do something similar here stackoverflow.com/questions/54351783/…. Is it possible to create a postgres table asynchronously? — Ludo
– Ludo, Commented Jan 25, 2019 at 17:54

Abelisto · Accepted Answer · 2018-01-01 20:06:16Z

2

Not the answer, just for info how it would be reproduced, so be patient.

Open two terminals, say tty1 and tty2, open psql in each.

tty1:

nd@postgres=# begin;
BEGIN
*nd@postgres=# create table if not exists foo();
CREATE TABLE
*nd@postgres=#

tty2:

nd@postgres=# begin;
BEGIN
*nd@postgres=# create table if not exists foo();

(waiting for lock)

tty1:

*nd@postgres=# commit;
COMMIT

tty2:

ERROR:  duplicate key value violates unique constraint "pg_type_typname_nsp_index"
DETAIL:  Key (typname, typnamespace)=(foo, 16386) already exists.
!nd@postgres=#

Not sure that PostgreSQL should be more smart in such cases. IMO something wrong with the application logic...

answered Jan 1, 2018 at 20:06

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gCoh Over a year ago

The problem caused by of delay between create tbl and commit. The second process executed create tbl, waiting for the first to commit. When the first commit, second raise exception, just as you showed

Collectives™ on Stack Overflow

Postgres create table asynchronously

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related