CODEWITHSUNDEEP
postgresqllockingoperator-precedence
foldl
foldl

Reputation: 745

Advisory locks in postgres and evaluation order (how to acquire lock without using a separate query)

Is there any safe way of acquiring an advisory lock before executing a particular statement without using two separate queries? E.g., I assume that if I do something like the following, there is no guarantee that the lock will be acquired before the insert:

WITH x AS (SELECT pg_advisory_lock(1,2)) INSERT ...

But is there some similar way of getting the desired effect?

Upvotes: 3

Views: 1255

Answers (2)

kgrittn
kgrittn

Reputation: 19481

You haven't really told us enough about your use case to be sure, but in general for explicit locks to be useful in PostgreSQL they need to be acquired before the transaction acquires its snapshot. Advisory locks can be acquired before you start the transaction, and most locks with transactional scope should be acquired right after your begin your transaction; before anything which will need a transaction ID.

If you really don't need to acquire the lock before you have your transaction ID assigned and your snapshot set, and it is important to you that you issue one statement to acquire the lock and perform the insert, create a function which does both.

Upvotes: 1

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95612

I'm pretty sure that SQL standards require implementations to behave as if the very first thing they do is to effectively materialize the common table expressions in the WITH clause. PostgreSQL complies with this requirement.

Common table expressions behave (mostly) as named objects. Multiple CTEs are materialized in the order they're declared. Backward references by name work as you'd expect, and forward references by name raise an error.

So I'm pretty sure that, in the general case, the CTE will have to materialize before the INSERT statement will run. But in your case, using PostgreSQL, I'm not dead certain, and here's why.

PostgreSQL's implementation [of common table expressions] evaluates only as many rows of a WITH query as are actually fetched by the parent query.

I'm not sure an INSERT statement fetches a row in this sense.

Upvotes: 1

Related Questions