Obtaining advisory lock using ORDER BY - Mailing list pgsql-general
Hi all.
I have this "dequeue" query which is working:
select qe.entity_id, qe.queue_id, qe.sequence_id, qe.tx_id , qe.payload_string
from origo_queue_entry qe
WHERE
qe.queue_id = 2
AND pg_try_advisory_xact_lock(sequence_id)
LIMIT 1 FOR UPDATE
;
I'm not sure is this is guaranteed to lock in ASC-order on column sequence_id, is it?
To ensure this I've tried with an explicit ORDER BY on "sequence_id", like this:
select qe.entity_id, qe.queue_id, qe.sequence_id, qe.tx_id , qe.payload_string
from origo_queue_entry qe
WHERE
qe.queue_id = 2
AND pg_try_advisory_xact_lock(sequence_id)
ORDER BY qe.sequence_id ASC LIMIT 1 FOR UPDATE
;
But the latter query results in all non-locked rows being locked (but it returns only 1 row due to LIMIT 1), but I'd like the "lowest" non-loced one.
Is there a way to make the locking work on an custom ordered set, preserving the "LIMIT 1"?
Thanks.
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Attachment
pgsql-general by date: