Re: Obtaining advisory lock using ORDER BY

På mandag 13. juli 2015 kl. 16:22:28, skrev Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us
<mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>>:
Andreas Joseph Krogh <andreas(at)visena(dot)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(at)visena(dot)com <mailto:andreas(at)visena(dot)com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>