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