Postgres pg_try_advisory_lock blocks all records

locking, postgresql, sql

Solution

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.

Problem

Original source