Session advisory locks leak into the pool when a client disconnects abruptly mid-hold (transaction mode)

[mhenrixon]

Summary

In transaction pooling mode, when a client dies abruptly (e.g. SIGKILL, container stop) while holding a session-scoped advisory lock, the pinned server connection is returned to the pool still holding the lock. The lock is never released until that backend happens to be closed, and every other client polling pg_try_advisory_lock on the same key spins for minutes (or forever on a quiet pool).

The happy path works correctly: after SELECT pg_try_advisory_lock(a, b) the client is pinned to one backend, subsequent statements stay on it, and pg_advisory_unlock(a, b) releases and unpins. The leak only happens on abrupt disconnect mid-hold.

Version / config

• pgdog v0.1.53 (official Docker image), also relevant on current main as far as I can tell
• pooler_mode = "transaction", passthrough_auth = "enabled_plain", min_pool_size = 5, max_pool_size = 40, single primary database, no sharding

Repro

1. Connect a client through pgdog and take a session advisory lock:

   psql "postgres://user:pass@pgdog:6432/db" \
     -c "SELECT pg_try_advisory_lock(999125, 0)" \
     -c "SELECT pg_sleep(30)"
   

2. While it sleeps (lock held), kill -9 the psql process.

3. On a direct connection to Postgres:

   SELECT * FROM pg_locks WHERE locktype = 'advisory';

   The lock is still granted to the pooled backend, which is back in the general pool serving other clients. It stays leaked indefinitely; in a busy pool we observed dozens of leaked locks accumulating on long-lived backends (28 locks on a single ~1h-old server connection).

Expected

When a client that pgdog knows is holding session advisory locks (it tracks them for pinning) disconnects without releasing them, the server connection should either run the dirty cleanup (SELECT pg_advisory_unlock_all() — it already exists in src/backend/pool/cleanup.rs as the DIRTY query set) or be closed so Postgres frees the session's locks.

Impact

After a deploy SIGKILLed app processes mid-hold, every request for the affected lock keys polled pg_try_advisory_lock in a loop (the common Ruby with_advisory_lock gem pattern) for 5–10 minutes per request until backends were recycled. SHOW POOLS / SHOW STATS look completely healthy while this is happening — cl_waiting 0, maxwait 0 — since the waiters are actively polling, which makes it quite hard to spot from the pgdog side.

Happy to test a fix or provide more detail.

[mhenrixon]

Found the root cause while debugging this against a local build — it's a detection gap, not a cleanup bug. At query_parser = "auto" (the default) the regex fallback only matches the base session-control set, so on a cluster where nothing else engages the parser (single primary, no sharding, no replicas) advisory lock statements bypass it entirely: no pinning, no dirty flag, and the existing pg_advisory_unlock_all() cleanup never runs on disconnect ([cleanup] no cleanup needed with AdvisoryLocks { locks: {} }). That also explains why my earlier happy-path pinning observation was misleading — a quiet pool just happened to reuse the same backend.

Workaround for anyone hitting this on a single-primary setup: set query_parser = "session_control_and_locks" — verified locally that the SIGKILL-mid-hold repro then gets cleaned up properly.

Proposed fix in #1408: match the advisory regex set at auto too.