1

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?

metacubed
  • 7,031
  • 6
  • 36
  • 65

0 Answers0