Re: pg_advisory_lock problem

Rémi Cura wrote
> Hey dear list,
>
> following the advise of Depesz I'm trying to use advisory lock.
>
> I'm trying to perform parallel upsert.
> I have 2 different sessions in which I run a very complicated querry
> (lot's
> of CTE) concurrently. In fact this complicated querry is simply put inside
> a plpgsql function for ease of calling.
>
> the querry performs upsert in 3 different tables, each time using a
> dedicated plpgsql function that looks like this :
> ---------
> PERFORM pg_advisory_lock(#1 , 2 or 3 depending on the table#,id of the
> stuff I want to upsert) ;
> WITH stuff_to_upsert ()
> ,updating AS (update returning id)
> ,inserting AS (insert if not updated)
> PERFORM pg_advisory_unlock(same as above).
> --------
>
> The querry call such plpgsql function like this
> CTE_N(
> SELECT r.*
> FROM result_to_be_upserted, function_upserting(...) as r
> )
>
> Yet I still have errors of duplicated primary key being broken because
> trying to insert 2 times the same stuff.
> ERROR: duplicate key value violates unique constraint
> "result_intersection_pkey"
> DETAIL: Key (node_id)=(*12621*) already exists.
>
> Of course when performing the querry sequencially I don't have any errors,
> even performing it several times.
>
> I have read the 2 pages relevant to pg_advisory lock, and I clean all the
> advisory lock before executing the test that gives those errors.
>
> After the errors happens (which means that 1 process completed and the
> other failed), I can see that there is a lock in pg_locks with the id of
> the row that caused the error when being upserted.
> advisory;2953366;;;;;;3;*12621*;2;8/0;11380;ExclusiveLock;t;f
>
> Any help is greatly appreciated, I have tried everything I could think of.

Without digging into this too deeply I suspect that there is some MVCC
visibility issues going on here. Basically, while the pg_advisory_lock()
can see, and therefore wait for, the lock issued by the other session it
cannot see the newly inserted record from that other session and so once the
lock is released it goes ahead and tries to insert too and fails.

Unfortunately I am not familiar with this particular means of upset so I
cannot be of greater help.

A self-contained example would probably help.

David J.

P.S. one "r" in query...I generally avoid this kind of thing but it stood
out and you did it 4 times so I didn't think it was a typo...

--
View this message in context: http://postgresql.1045698.n5.nabble.com/pg-advisory-lock-problem-tp5814387p5814396.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.