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