Obtaining advisory lock using ORDER BY

Andreas Joseph Krogh <andreas@visena.com> writes:

I have this "dequeue" query which is working:
 
select qe.entity_id, qe.queue_id, qe.sequence_id, qe.tx_id , qe.payload_string
fromorigo_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?

No. You could possibly do SELECT...ORDER BY...FOR UPDATE in a
subquery and do the pg_try_advisory_xact_lock call in the outer query.

It might take some fooling around to get a plan that doesn't lock
more rows than necessary; EXPLAIN is your friend.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

På mandag 13. juli 2015 kl. 16:22:28, skrev Tom Lane <tgl@sss.pgh.pa.us
<mailto:tgl@sss.pgh.pa.us>>:
Andreas Joseph Krogh <andreas@visena.com> writes:

I have this "dequeue" query which is working:

select qe.entity_id, qe.queue_id, qe.sequence_id, qe.tx_id ,

qe.payload_string

fromorigo_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?

No.  You could possibly do SELECT...ORDER BY...FOR UPDATE in a
subquery and do the pg_try_advisory_xact_lock call in the outer query.

It might take some fooling around to get a plan that doesn't lock
more rows than necessary; EXPLAIN is your friend.

regards, tom lane
 
I'm unable to construct such a query.
 
This query blocks and tries to lock the same row (highest sequence_id):
 
SELECT * FROM (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 ORDER BY qe.
sequence_idDESC LIMIT 1 FOR UPDATE ) q WHERE
pg_try_advisory_xact_lock(sequence_id)
 
I'm trying this (on pg-9.4) as an alternative to 9.5's new SKIP LOCKED.
 
Is there a way to accomplish "lock next non-locked row with custom ordering"
using pg-9.4?
 
Thanks.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;