Advisory locks aren’t all sunshine and rainbows. They can only be unlocked by the Postgres connection that acquired the lock. That means you need to track the connection, typically by dedicating a connection to the job that needs locking.
Here’s a good issue describing the tradeoffs between a lock table and advisory locks.
Do people use advisory locks as the actual locking mechanism? I've always used them to synchronize access to a flag on the target resource, so the advisory lock is only held long enough to query or update that resource as locked. The alternative seems, yes, incredibly brittle.
FreakLegion|2 months ago