The startup parameter PgBouncer refused: statement_timeout and pgx

The backend set three PostgreSQL settings on every connection as startup parameters: the time zone, statement_timeout and idle_in_transaction_session_timeout. Against PostgreSQL that works. Through PgBouncer, which sits in front of the database on our cluster, the connection is closed before the first query with:

unsupported startup parameter: statement_timeout

The fix is small: application_name stays a startup parameter, the rest is a SET right after connecting. Why that is correct in session pooling, and would be wrong in transaction pooling, is the longer part.

What the code did

With pgx, RuntimeParams on the connection config go into the startup message:

pcfg.ConnConfig.RuntimeParams["timezone"] = "UTC"
pcfg.ConnConfig.RuntimeParams["statement_timeout"] = "15000"
pcfg.ConnConfig.RuntimeParams["idle_in_transaction_session_timeout"] = "30000"
pcfg.ConnConfig.RuntimeParams["application_name"] = "elchi-web"

PostgreSQL treats every parameter in the startup message that is not part of the protocol itself as a run-time setting, applied when the backend starts and kept as the session's default. No round trip, and the settings hold before the first query. It is a tidy pattern, which is why it is common.

Neither timeout is decoration. 15 seconds is the most any statement of this application may take; without it, a slow query holds a request and a pool connection for as long as it likes. 30 seconds is how long a connection may sit idle inside an open transaction before the server ends it, so a forgotten transaction cannot hold its locks for good.

What PgBouncer does with a startup packet

PgBouncer reads the startup packet one parameter at a time. database, user and options it handles itself, application_name too. A parameter it tracks goes into its cache of the client's settings. A parameter listed in ignore_startup_parameters is dropped. Anything else ends the connection with the message above.

What it tracks by default is a short list, from its documentation: application_name, client_encoding, DateStyle, default_transaction_read_only, IntervalStyle, scram_iterations (PostgreSQL 16 and later), search_path (PostgreSQL 18 and later), session_authorization, standard_conforming_strings and TimeZone. These are parameters PostgreSQL reports back to the client when they change, which is how PgBouncer can follow them and restore them on whichever server connection the client gets next. statement_timeout is not reported, so PgBouncer cannot follow it, and refuses it rather than pretend.

TimeZone is on the list, so that one would have passed. Which of the two timeouts the error names depends on the order of the parameters in the packet, and pgx fills the packet from a Go map, so it can be either.

Two settings that look like fixes

PgBouncer has two options that make the error go away. Neither is one I wanted.

ignore_startup_parameters = statement_timeout lets the connection through and drops the parameter. Every statement then runs without a timeout, and nothing tells you. That is worse than the error, which at least stops the start: our Open pings the database before anything else runs, so the process fails instead of serving.

track_extra_parameters asks PgBouncer to keep a value in its cache and restore it on the server. Its documentation says plainly that most parameters cannot be fully tracked this way, since it only learns of a SET for parameters PostgreSQL reports. Beyond that, PgBouncer belongs to the cluster, which every application on it shares. An application that only works with special pooler settings is one more thing to remember for the next one.

The fix

The settings moved into pgxpool's AfterConnect, which runs on every new connection before it is added to the pool:

const sessionSettings = `SET TIME ZONE 'UTC'; SET statement_timeout = 15000; SET idle_in_transaction_session_timeout = 30000`

pcfg.ConnConfig.RuntimeParams["application_name"] = "elchi-web"
pcfg.AfterConnect = func(ctx context.Context, c *pgx.Conn) error {
	_, err := c.Exec(ctx, sessionSettings)
	return err
}

pgx sends a query without arguments over the simple protocol, which allows several statements in one string, so this costs one round trip per new connection, not per query. If it fails, the connection never reaches the pool.

application_name stays a startup parameter. PgBouncer accepts it, and it is useful from the very first moment, in pg_stat_activity and in the server's logs. The time zone moved with the timeouts, although PgBouncer would have accepted it, so that every setting the code relies on lives in one statement in one place.

Why this is safe in session pooling

In session pooling, PgBouncer assigns a server connection to a client when it connects and keeps it assigned until the client disconnects. A SET right after connecting holds on that server connection for as long as the client has it, which is the life of the pool connection. When the client goes, PgBouncer runs its server_reset_query, DISCARD ALL by default, before the server connection goes to anybody else. Our 15 seconds do not leak into another application's queries.

Why it would not be safe in transaction pooling

In transaction pooling, a client has a server connection only for one transaction. The SET from AfterConnect would land on whichever server connection ran that one statement. That connection then goes back to the pool and on to another client, with our timeouts on it, while our next transaction runs on a server connection without them. The reset query does not run in transaction mode unless server_reset_query_always is on. PgBouncer's own feature table lists SET and RESET as never working in transaction pooling, and session-level advisory locks with them.

If you run transaction pooling, put the settings where the server applies them itself. ALTER ROLE ... SET statement_timeout = '15s' makes it the default for every new server session of that role, whatever sits in between, and SET LOCAL inside a transaction holds for that transaction only. pgx prepares statements by default, which in that mode also needs PgBouncer's max_prepared_statements; in session mode it needs nothing.

We run session pooling, with four connections per instance, so one server connection per pool connection is what we want anyway.

How it was tested

TestSessionSettingsThroughPgBouncer runs the same checks twice: once directly against the test database, once through a real PgBouncer in session mode in front of it. Each time it opens a pool of two, takes both connections at once, so it checks two separate connections rather than the first one twice, and reads the settings back on each:

SELECT current_setting('TimeZone'), current_setting('statement_timeout'),
       current_setting('idle_in_transaction_session_timeout'), current_setting('application_name')

It expects UTC, 15s, 30s and elchi-web. Then it runs SELECT pg_sleep(16) and expects PostgreSQL to cancel it at 15 seconds with a statement timeout. A setting that reads back correctly but does nothing would fail there.

Against the previous version of Open, the PgBouncer half fails before any of that, at the first connection, with the exact error above. The direct half passes either way, which is the whole problem in one test: a suite that only talks to PostgreSQL directly cannot see it.