PgBouncer in transaction mode broke our advisory locks

The service is a small Django app (Python 3.11, psycopg 3.1) on a two-node setup: app containers on OVH in Gravelines, Postgres 15 managed elsewhere. Two months ago we put PgBouncer 1.21 in front of Postgres with pool_mode = transaction, because we had 180 app connections and a 100-connection ceiling. It fixed the connection count and silently broke every advisory lock we had.

The symptom was not an error. A nightly job that is supposed to run once per tenant started running twice, on different workers, and a reconciliation step that uses pg_advisory_xact_lock began interleaving with itself. On a single-tenant run nothing showed up; only the batch with ~40 tenants in parallel produced duplicates, maybe twice a week.

Advisory locks are session-scoped. In transaction mode PgBouncer hands a connection back to the pool at COMMIT, so two transactions that look serialised to the application can land on different backend sessions and never see each other’s lock. The lock was taken, but by a session that was no longer the one running the next statement.

What we do now, and it is boring:

-- replace session advisory locks with row locks
SELECT id FROM tenant_job WHERE tenant_id = $1 FOR UPDATE;

and for the few places where we really want a global mutex, a dedicated pool that keeps pool_mode = session:

[pgbouncer]
pool_mode = session

with the app connecting to that pool only for the lock-holding code path. The cost is one extra connection pool and the discipline of remembering which DSN does what; the benefit is that the lock semantics are visible in the code rather than implied by the pooler config.

It breaks again when someone introduces SET LOCAL-style session state, temp tables, or LISTEN/NOTIFY through the transaction pool. Those are the same class of bug and they will not error, they will just produce duplicates under load. We caught the advisory lock one because it made duplicate rows; a LISTEN-based cache invalidation would have looked like stale data for a week.

My question for anyone running PgBouncer in front of a French-hosted Postgres: did you audit session state before switching, or did you find it the way we did?