Postgres pg_try_advisory_lock blocks all records

Question

I'm using pg_try_advisory_lock() in Postgres.

Next two queries lock more than one records in table1:

1)

SELECT a.id
FROM table1 a
JOIN table2 b ON a.table1_id = b.id
WHERE
    table2.id = 1
    AND
    pg_try_advisory_lock('table1'::regclass::integer, a.id)
LIMIT 1;

but

SELECT a.id
FROM table1 a
JOIN table2 b ON a.table1_id = b.id
WHERE table2.id = 1

returns one record.

2)

SELECT a.id
FROM table1 a
JOIN table2 b ON a.table1_id = b.id
JOIN table3 c ON b.table2_id = c.id
WHERE
    table3.id = 1
    AND
    pg_try_advisory_lock('table1'::regclass::integer, a.id)
LIMIT 1;

But I need pg_try_advisory_lock() to lock only one record.

What's wrong?

UPD

But the strange thing is that when I run the following query

SELECT a.id
FROM table1 a
JOIN table2 b ON a.table1_id = b.id
WHERE
    pg_try_advisory_lock('table1'::regclass::integer, a.id)
LIMIT 1;

Postgres locks only one row. So, Postgres scans the very first row then stops? I don't get it: it should scan all rows then limit the results to one row, or not?

Denis de Bernardy · Accepted Answer · 2013-11-21T11:46:15.213

You're calling pg_try_advisory_lock() once per row in the entire set that gets scanned (as part of the filtering that occurs in the where clause), whereas you only want it called once per row in table1 returned by the query.

You could try using a subquery or a CTE instead:

with rows as (
SELECT a.id
FROM table1 a
JOIN table2 b ON a.table1_id = b.id
WHERE table2.id = 1
)
select rows.*
from rows
where pg_try_advisory_lock('table1'::regclass::integer, rows.id);

But don't rely on that to necessarily work as expected either: Postgres should be tempted to rewrite it the way your initial query was.

Another possibility is this, since the select part of a statement is evaluated very late in the query:

with rows as (
SELECT a.id,
       pg_try_advisory_lock('table1'::regclass::integer, a.id) as locked
FROM table1 a
JOIN table2 b ON a.table1_id = b.id
WHERE table2.id = 1
)
select rows.id
from rows
where rows.locked;

The real issue in practice is that pg_try_advisory_lock() is something you'd normally find in app land or in a function, rather than in a query like you're doing. Speaking of which, depending on what you're doing, are you sure you shouldn't be using select … for update?

Regarding your update:

postgres scans the very first row then stops?

Yes. Due to the limit 1, it's going to find a match and immediately stop. What is probably happening, though, is that it's not evaluating the where clause in the same order depending on your queries. SQL offers no guarantee that the a <> 0 part in a <> 0 and b / a > c gets evaluated first. Applied to your case, it offers no guarantee that the advisory lock is obtained after the row from a is joined with b.

I don't need select … for update because another script must find another records. — fdoo4un, Nov 21 '13 at 11:20
I still think you should be locking in app-land, rather than in sql-land. — Denis de Bernardy, Nov 21 '13 at 11:26
CTE works good. Thanks for advice! Also I updated question. See plz. — fdoo4un, Nov 21 '13 at 11:44
Double check its query plan, though, using `explain analyze`. For your query to work as expected, Postgres *must* evaluate the CTE first, rather than rewrite it like it was in your original query. And if anything, I'd argue that Postgres would be wrong to not rewrite it. :-) — Denis de Bernardy, Nov 21 '13 at 11:51

Postgres pg_try_advisory_lock blocks all records

1 Answers1

Linked