Summary
roles/pgsql/templates/pgbouncer.ini hardcodes
server_reset_query_always = 0
There is no inventory parameter to change it (unlike pgbouncer_poolmode, pgbouncer_sslmode, pgbouncer_ignore_param, …). Any change made on the nodes is reverted the next time pgsql.yml renders the config.
Why it matters
With pgbouncer_poolmode: transaction (the default), PgBouncer only runs server_reset_query (DISCARD ALL) between sessions. Server connections are reused between transactions of different clients while still carrying their session state. We hit two problems with a schema-per-tenant application:
- Session state leaks between clients. A session-level
SET search_path from one client stays on the server connection and is inherited by the next client, which then resolves unqualified tables in another schema.
- Shared prepared statements break when schemas differ. With
max_prepared_statements > 0, PgBouncer reuses a server-side prepared statement across clients by query text. When the same query runs under a different search_path, and a result column's type differs between schemas (for example a per-schema enum), PostgreSQL fails with ERROR 0A000: cached plan must not change result type. In our load test about 40% of requests failed this way, even after the application stopped setting session-level state.
server_reset_query_always = 1 fixes both (0 errors in the same test). The cost is more Parse traffic on the server.
Request
Expose it as an inventory variable, for example:
pgbouncer_reset_query_always: false # default keeps today's behaviour
server_reset_query_always = {{ 1 if pgbouncer_reset_query_always|default(false)|bool else 0 }}The default would stay the same, so nothing changes for existing clusters. Deployments running multi-schema or session-state-sensitive applications behind transaction pooling could turn it on without patching the role template.
Version: Pigsty v4.3.0.
Summary
roles/pgsql/templates/pgbouncer.inihardcodesserver_reset_query_always = 0There is no inventory parameter to change it (unlike
pgbouncer_poolmode,pgbouncer_sslmode,pgbouncer_ignore_param, …). Any change made on the nodes is reverted the next timepgsql.ymlrenders the config.Why it matters
With
pgbouncer_poolmode: transaction(the default), PgBouncer only runsserver_reset_query(DISCARD ALL) between sessions. Server connections are reused between transactions of different clients while still carrying their session state. We hit two problems with a schema-per-tenant application:SET search_pathfrom one client stays on the server connection and is inherited by the next client, which then resolves unqualified tables in another schema.max_prepared_statements > 0, PgBouncer reuses a server-side prepared statement across clients by query text. When the same query runs under a differentsearch_path, and a result column's type differs between schemas (for example a per-schema enum), PostgreSQL fails withERROR 0A000: cached plan must not change result type. In our load test about 40% of requests failed this way, even after the application stopped setting session-level state.server_reset_query_always = 1fixes both (0 errors in the same test). The cost is moreParsetraffic on the server.Request
Expose it as an inventory variable, for example:
server_reset_query_always = {{ 1 if pgbouncer_reset_query_always|default(false)|bool else 0 }}The default would stay the same, so nothing changes for existing clusters. Deployments running multi-schema or session-state-sensitive applications behind transaction pooling could turn it on without patching the role template.
Version: Pigsty v4.3.0.