1

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)

gCoh
  • 2,719
  • 1
  • 22
  • 46
  • 2
    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 Dec 31 '17 at 09:37
  • 1
    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 Dec 31 '17 at 10:32
  • 1
    Hi @gCoh, did you make any progress on this? I'm trying to do something similar here https://stackoverflow.com/questions/54351783/duplicate-key-value-violates-unique-constraint-postgres-error-when-trying-to-c. Is it possible to create a postgres table asynchronously? – Ludo Jan 25 '19 at 17:54

1 Answers1

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...

Abelisto
  • 14,826
  • 2
  • 33
  • 41
  • 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 – gCoh Jan 02 '18 at 20:33