Postgres Pro
Downloads
Home   >   mailing lists

pg_advisory_lock problem - Mailing list pgsql-general

From Rémi Cura
Subject pg_advisory_lock problem
Date
Msg-id CAJvUf_vsEto-OW1ZbzrGkzbNpVamLHh-becWu0EV5hKpMcnQ_g@mail.gmail.com
Whole thread Raw
Responses Re: pg_advisory_lock problem  (David G Johnston <david.g.johnston@gmail.com>)
List pgsql-general
Tree view
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.

Thanks,
Cheers,
Rémi-C

pgsql-general by date:

Previous
From: Seref Arikan
Date:
Subject: Re: Disk space available, but getting error "could not write to hash-join temporary file: No space left on device"
Next
From: David G Johnston
Date:
Subject: Re: pg_advisory_lock problem