Re: Inconsistent behaviour calling pg_try_advisory_xact_lock with sub-query and when JOIN'ing - Mailing list pgsql-general

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

På fredag 07. august 2015 kl. 20:55:28, skrev Tom Lane <tgl@sss.pgh.pa.us>:

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.

 

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.

begin;
select qe.entity_id, qe.version, qe.queue_id, qe.sequence_id, qe.tx_id
from origo_queue_entry qe
WHERE
    qe.queue_id = 2
    AND pg_try_advisory_xact_lock(sequence_id)
ORDER BY qe.sequence_id ASC
LIMIT 1 FOR UPDATE

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com