I have some SQL queries which will be executed in a clustered environment. I'm trying to use a Postgres advisory lock to prevent multiple executions of these queries.
- Begin transaction.
- Execute
pg_try_advisory_xact_lock
and check the boolean return value. - If
true
(the lock is acquired), then proceed with the actual business-logic queries (arbitrary). - If
false
(another instance is already holding the exclusive lock), then do nothing. - End transaction.
Note that I'm using the non-blocking _try_
variant which returns a boolean. If the lock is not immediately available, some other instance is already executing the command, so nothing needs to be done.
The question "PostgreSQL IF statement" suggests embedding a PL/pgSQL IF
clause using DO
. Is there a way to do this without this extra complexity?