PostgreSQL obtain and release LOCK inside stored function
In Postgres 11 or later, consider a
PROCEDURE which allows transaction control. See:
With functions, there is no way. Functions in Postgres are atomic (always inside a transaction) and locks are released at the end of a transaction.
You might be able to work around this with advisory locks. But those are not the same thing. All competing transactions have to play along. Concurrent access that is not aware of advisory locks will spoil the party.
Code example on dba.SE:
Or you might get somewhere with "cheating" autonomous transactions with dblink:
- How do I do large non-blocking updates in PostgreSQL?
- Does Postgres support nested or autonomous transactions?
Or you re-assess your problem and split it up into a couple of separate transactions.
In pg11 you now have
PROCEDUREs which let you release locks via
COMMIT. I just converted a bunch of parallel executed functions running
ALTER TABLE ... ADD FOREIGN KEY ... with lots of deadlock problems and it worked nicely.
Not possible. From documentation: Once acquired, a lock is normally held till end of transaction. But if a lock is acquired after establishing a savepoint, the lock is released immediately if the savepoint is rolled back to. This is consistent with the principle that ROLLBACK cancels all effects of the commands since the savepoint. The same holds for locks acquired within a PL/pgSQL exception block: an error escape from the block releases locks acquired within it.