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

På lørdag 08. august 2015 kl. 09:59:53, skrev Andreas Joseph Krogh <
andreas(at)visena(dot)com <mailto:andreas(at)visena(dot)com>>:
På fredag 07. august 2015 kl. 23:26:14, skrev David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com <mailto:david(dot)g(dot)johnston(at)gmail(dot)com>>:
On Fri, Aug 7, 2015 at 1:51 PM, Andreas Joseph Krogh <andreas(at)visena(dot)com
<mailto: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
<mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>>:
Andreas Joseph Krogh <andreas(at)visena(dot)com <mailto: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 <http://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 <http://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 onqueue.name
<http://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
<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
fromorigo_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?
 
If this isn't the case, then the example shown here:
http://www.depesz.com/2013/08/30/pick-a-task-to-work-on/
#!/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,)  

isn't really working code, as the pg_try_advisory_xact_lock is in the
WHERE-clause, right?
 
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>