DBI pool connection timeout and server-side session alignment

Answer

DBI core does not implement connection pooling. Pooling is provided by external modules such as DBI::Pool, DBIx::Connector, DBIx::Pool and driver-specific pools. Timeout and validation behavior is therefore module-specific, not DBI itself.

Confirmed fact: server-side session state is bound to the physical database connection. A handle returned from a pool can carry stale state from a previous checkout, including SET ROLE, search_path, time zone, temporary tables, prepared statements and transaction state.

Recommended configuration strategy

Align pool lifetimes to be strictly shorter than server limits and re-apply session state on checkout.

• Identify the exact pooling module and DB server/driver in use. Configuration names differ per module.
• Set pool idle timeout and max lifetime below the server idle close threshold. A common safe practice is pool idle_timeout ≈ 0.8 × server idle timeout, and pool max_lifetime < server max connection lifetime.
• Enable validation on checkout or a checkout callback that pings the connection and discards dead handles. Do not rely on periodic background validation alone.
• Do not rely on per-connection session state across checkouts. If session alignment is required, re-apply settings on every checkout via a pool checkout callback, DBI on_connect, or server ON CONNECT trigger. Prefer stateless design where possible.

Validation function timing

Validation does not trigger automatically on every checkout in all modules. It is module-specific and usually opt-in.

• DBI::Pool and similar modules typically validate on checkout only when validate_on_checkout is enabled, or validate on a time interval / background sweep.
• Without validate_on_checkout, a stale connection can be handed out until the next explicit use fails.

Likely explanation for your errors

Stale connection errors during checkout commonly occur when the DB server closes idle connections via wait_timeout, idle_in_transaction_session_timeout or equivalent, while the pool still marks them alive. If pool idle timeout is longer than the server timeout, or validation is interval-based rather than checkout-based, the application receives a dead handle.

Steps needed for this case

1. Confirm pooling module and DB server/version. Map pool parameters idle_timeout, max_lifetime, validate_on_checkout to server settings.
2. Reduce pool idle timeout below server idle close timeout and enable checkout validation.
3. Add a checkout callback to re-apply required session settings, or move session settings to per-statement, SET LOCAL, or server ON CONNECT.
4. Enable DBI trace and pool debug logging to observe checkout errors and reconnect events. Inspect DB server logs for idle close events and reproduce after the server idle timeout.

One missing diagnostic detail that changes the recommendation: which pooling module and DB server/version you are using, and whether session alignment is for security context such as SET ROLE versus non-security settings like time zone. The parameter names and safe defaults differ per module.