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

On Fri, Aug 7, 2015 at 1:51 PM, Andreas Joseph Krogh <andreas(at)visena(dot)com>
wrote:

> På fredag 07. august 2015 kl. 20:55:28, skrev Tom Lane <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?
>

​While that may be what you want what you wrote is:

Of all the unlocked rows in the EMAIL_IMPORT_STORE queue; give me the first
one in order of sequence id. Since the query itself performs the locks
then, yes, at the end every item in said queue will be locked (concurrency
dynamics excluded) either by your query or by whatever query locked it
first - and thus excluding it from your result.

While optimizations may get you want you intend the query itself is much
broader than you think.

You need to set things up so the souring query will not return locked rows
in the first place and then simply lock the first row that you are provided.

SELECT try_lock(id) FROM ( SELECT id FROM tbl ORDER BY id LIMIT 1 ) src;

Other's more fluent may be able to help more. But, in short, your
"working" query worked by accident. This behavior is documented:

http://www.postgresql.org/docs/9.1/static/explicit-locking.html

and while the typical user would ideally would not need to be aware of
these dynamics, they are. This is doubly-so when you care about more than
just which rows are output in the final result.

David J.