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?

Answer 1

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.