Working with Advisory Locks

SELECT pg_advisory_lock(42);            -- blocks until acquired

SELECT pg_try_advisory_lock(42);        -- returns true/false immediately

SELECT pg_advisory_unlock(42);

BEGIN;
SELECT pg_advisory_xact_lock(42);       -- auto-released on COMMIT/ROLLBACK
... ;
COMMIT;

SELECT pg_advisory_lock_shared(42);
SELECT pg_try_advisory_xact_lock_shared(42);

SELECT pg_advisory_lock(1001, 42);      -- namespace, key

SELECT locktype, classid, objid, mode, granted
  FROM pg_locks WHERE locktype = 'advisory';

SELECT pg_advisory_unlock_all();

-- Singleton job runner
IF pg_try_advisory_lock(hashtext('cron:nightly')) THEN
   PERFORM run_nightly();
   PERFORM pg_advisory_unlock(hashtext('cron:nightly'));
END IF;