Thread: 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.

            regards, tom lane

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

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

På fredag 07. august 2015 kl. 23:26:14, skrev David G. Johnston <david.g.johnston@gmail.com>:

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.

 

Thanks for explaining but I still don't understand how I'm supposed to write my query. In your example above the sub-select will return the the same row for 2 separate transactions and I don't see how having try_lock lock that row will solve the "lock the next unlocked row order by sequence_id" problem.

 

If I'm not joining any other table, just using the qe.queue_id = <queue_id> directly, like this:

 

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

Am I then guaranteed that it's working predictably, always locking only one row?

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import psycopg2
import hashlib
import sys
 
 
def process_item(row):    h = hashlib.md5()    h.update(row[0])    return h.hexdigest()
 
conn = psycopg2.connect("port=5930 host=127.0.0.1 user=depesz dbname=depesz")
cur = conn.cursor()
processed = 0
while True:    cur.execute('''    select *    from queue    where pg_try_advisory_xact_lock(123, hashtext( priority::text || added_on::text ) )    order by priority desc, added_on asc limit 1 for update    ''')    row = cur.fetchone()    if row is None:        break    ignore = process_item(row)    processed = processed + 1    cur.execute('delete from queue where priority = %s and added_on = %s', (row[1], row[2]))    conn.commit()
 
cur.close()
conn.close()
print "processed %d rows." % (processed,)

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com