Re: 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

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_id DESC
     LIMIT 1
     FOR UPDATE
    ) q
WHERE pg_try_advisory_xact_lock(sequence_id)

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com