På mandag 13. juli 2015 kl. 16:22:28, skrev Tom Lane <[email protected] <mailto:[email protected]>>: Andreas Joseph Krogh <[email protected]> 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 [email protected] <mailto:[email protected]> www.visena.com <https://www.visena.com> <https://www.visena.com>
