Re: Inconsistent behaviour calling pg_try_advisory_xact_lock with sub-query and when JOIN'ing

På fredag 07. august 2015 kl. 20:55: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:
> The following query returns and locks 1 row as expected (only one row in
> pg_locks with locktype='advisory' and objid=sequence_id):

> begin; select qe.entity_id, qe.version, qe.queue_id, qe.sequence_id,
qe.tx_id
> fromorigo_queue_entry qe WHERE qe.queue_id = (SELECT q.entity_id FROM
> origo_queue qWHERE q.name = 'EMAIL_IMPORT_STORE') AND
pg_try_advisory_xact_lock(
> sequence_id) ORDER BY qe.sequence_id ASC LIMIT 1 FOR UPDATE ; ��

> But when JOIN'ing with origo_queue instead of using a sub-query:

> begin; select qe.entity_id, qe.version, qe.queue_id, qe.sequence_id,
qe.tx_id
> fromorigo_queue_entry qe JOIN origo_queue q ON q.entity_id = qe.queue_id
WHERE
> q.name = 'EMAIL_IMPORT_STORE' AND pg_try_advisory_xact_lock(sequence_id)
ORDER
> BYqe.sequence_id ASC LIMIT 1 FOR UPDATE ; ��

> it returns 1 row, but locks all of them; pg_locks is now full af
> advisory-locks for all "sequence_id" in origo_queue_entry

> Is this by design?

Well, there is not and never will be any guarantee of consistent behavior
when you put volatile functions into WHERE clauses.  The optimizer is
totally free to reorder the execution of different WHERE/JOIN-ON clauses,
which is basically what the problem is here AFAICS.

If you can arrange things so that the volatile function is in a SELECT
list, where it's well-defined what set of rows it'll get executed at,
it should be better.
 
I'm not sure any developer cares or knows about volatile functions in
WHERE-clauses caused by sub-selects of JOINs, they just want to get the "job
done". I certainly find it strange the the number for locked rows varies in two
queries which returns the exact same tuples.
 
All I want to do is to lock the "next" un-locked row in "origo_queue_entry"
with queue_id = (select which retrieves queue_id based on queue.name). How can
I accomplish this with a simple select in a predictable fashion?
 
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>