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

Andreas Joseph Krogh <andreas@visena.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.

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com