Transaction pooling surprise (lost session state)

Transaction pooling surprise (lost session state)
Type: incident simulation · Topic: Connections & Pooling · Level: L3 · Duration: 10–15 min
Launch: ride postgres start stage-03/05-transaction-pooling-surprise

POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: with PgBouncer in transaction pooling mode, consecutive transactions
from one client can run on different server connections. Session-level state set
in one transaction — `SET search_path`, session GUCs, temp tables — is gone by
the next, because that next query may use a different backend. Queries that
relied on `SET search_path` then resolved the wrong (or no) table.

How it was found: the app behaved inconsistently under PgBouncer; SHOW CONFIG /
the pool mode showed `transaction`, which is incompatible with session-state
assumptions.

The fix (transaction-pooling-safe): make each statement self-contained — use
fully-qualified names (`public.orders`) instead of relying on `SET search_path`.

Lesson: transaction pooling trades session affinity for throughput. Don't depend
on session state (search_path, SET, temp tables, unnamed prepared statements).
Qualify names, or use session pooling for workloads that truly need session
state. It is not an indexing or locking problem.

INVESTIGATION HINTS (the staged path to diagnose and fix)
1. PgBouncer is in transaction pooling mode, so each transaction can land on a different server connection. Session-level state like `SET search_path` does NOT carry over to the next query — that's the surprise.
2. Stop relying on session state. Don't `SET search_path` and then query an unqualified table; the next statement may run on another backend without that setting.
3. Make queries self-contained: use fully-qualified names like `public.orders` so the result doesn't depend on which server connection you got.