PgBouncer 事务池模式下会出哪些问题，以及如何逐一修复

在 Postgres 前面放一个事务模式的 PgBouncer，成千上万个短生命周期的客户端就能共享几十个服务端连接。代价是，在事务与事务之间，“我的连接”不再有任何意义。prepared statement "s0" already exists 这样的错误就是这么来的，同样来源的还有出现在别人请求里的 SET，以及两个 worker 同时持有的 advisory lock。本文在本地的 PgBouncer 1.24.1 和 PostgreSQL 17.11 上逐一复现这些问题，配置方式与 Relyt One 的连接池连接字符串一致（事务模式、Postgres 17），给出每个问题的修复方法，最后给出一条何时绕过连接池的规则。下文的每条命令和每段输出都在这套本地环境上实际运行过。

在事务模式下，PgBouncer 把一个服务端连接借给客户端使用一个事务，在 COMMIT 或 ROLLBACK 时收回。显式事务之外的语句自成一个事务。凡是在服务端连接上存活超过一个事务的东西，要么泄漏给下一个客户端，要么在下一个事务里不见了。下面是受影响的功能，依据来自 PgBouncer 的功能表、它的配置参考以及我们的实验：

本文其余部分会逐行演示这些问题的发生过程。

PgBouncer 配置参考中的两个细节可以解释下文的大部分现象：

• 客户端之间不做任何重置。 server_reset_query（默认 DISCARD ALL）在事务池模式下“不使用”。一个事务在服务端连接上留下什么，下一个客户端就继承什么。
• 部分设置会被跟踪，其余不会。 对于 Postgres 会回报给客户端的一小组参数（1.24 中为 client_encoding、DateStyle、IntervalStyle、TimeZone、standard_conforming_strings 和 application_name），PgBouncer 会记住每个客户端的值，并在把服务端连接交给该客户端时恢复。功能表把其他所有 SET 标为与事务池不兼容：改动留在服务端连接上，留给下一个客户端。

下文的一切都在 Docker 中本地运行的 Postgres 17.11 和 PgBouncer 1.24.1 上执行。PgBouncer 镜像是社区维护的 edoburu/pgbouncer。连接池配置使用事务模式并关闭预处理语句跟踪，与 Relyt One 的连接池相同，但把池大小设为一个服务端连接。只有一个服务端连接时，每个客户端都会落到同一条连接上，所以每种故障每次都会发生，而不是偶尔发生。在生产环境中，池里有几十个服务端连接，同样的故障是间歇性的，这正是它们难以排查的原因。

; pgbouncer.ini
[databases]
* = host=pg port=5432

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
default_pool_size = 1
max_prepared_statements = 0
ignore_startup_parameters = extra_float_digits

echo '"app" "secret"' > userlist.txt
docker network create pgblab
docker run -d --name pg --network pgblab \
  -e POSTGRES_USER=app -e POSTGRES_PASSWORD=secret -e POSTGRES_DB=app \
  postgres:17.11-alpine
docker run -d --name pgbouncer --network pgblab -p 6432:6432 \
  -v "$PWD/pgbouncer.ini:/etc/pgbouncer/pgbouncer.ini:ro" \
  -v "$PWD/userlist.txt:/etc/pgbouncer/userlist.txt:ro" \
  edoburu/pgbouncer:v1.24.1-p1
export PGPASSWORD=secret

下面的每个 psql -c 都是一个独立的 PgBouncer 客户端连接，就像 Web 应用的不同请求或不同的 serverless 调用一样。先把这个辅助函数粘贴到 shell 中：

pooled() { psql -X -h localhost -p 6432 -U app app "$@"; }

客户端 A 设置了一秒的语句超时，就像应用可能在请求开始时做的那样。客户端 B 是另一个人：

pooled -c "SET statement_timeout = '1s';"
pooled -c "SHOW statement_timeout;" -c "SELECT pg_sleep(2);"

SET
 statement_timeout
-------------------
 1s
(1 row)

ERROR:  canceling statement due to statement timeout

客户端 B 从没设置过超时，它两秒的查询却照样被取消了。SET 在它自己的事务中执行，服务端连接带着 statement_timeout = 1s 回到池中，B 拿到了这条连接。池更大时，相反的情况也会发生：客户端 A 的下一条语句落到另一条服务端连接上，那里从没设置过这个超时。在生产环境中两种情况都会看到：设置有时不生效，而从没要求过的请求里出现超时或错误的 search_path。

被跟踪的参数表现不同。同样的实验换成 SET TimeZone = 'Asia/Tokyo'，再从第二个客户端执行 SHOW TimeZone，输出的是 UTC，因为 PgBouncer 会恢复每个客户端自己的值。search_path 不在这个列表里，它和 statement_timeout 一样会泄漏：

pooled -c "SET search_path = app_private, public;"
pooled -Atc "SHOW search_path;"

SET
app_private, public

修复：把设置限定在事务内。 SET LOCAL 持续到当前事务结束，所以它作用于事务内的每条语句，并在服务端连接回到池中之前失效：

pooled -c "BEGIN; SET LOCAL statement_timeout = '1s'; SHOW statement_timeout; COMMIT;"
pooled -Atc "SHOW statement_timeout;"

BEGIN
SET
 statement_timeout
-------------------
 1s
(1 row)

COMMIT
0

（如果你在跟着操作，先运行 pooled -c "RESET statement_timeout;" -c "RESET search_path;"，清理上面泄漏留下的设置。）在应用代码中，set_config(name, value, true) 的效果与 SET LOCAL 相同，而且可以接收参数，因此它是把用户 ID 传给行级安全策略的常用方式。我们的托管认证指南用的正是这种模式，出于同样的原因，它在连接池后面依然成立。

对于每个连接都应该有的设置，改为绑定到角色上：

ALTER ROLE app SET statement_timeout = '5s';

Postgres 在服务端连接启动时应用角色设置。PgBouncer 已经打开的服务端连接会保留旧值；在我们的实验中，刚执行完 ALTER ROLE 时，通过连接池执行 SHOW statement_timeout 仍然输出 0，等 PgBouncer 打开了新的服务端连接后才输出 5s。

SQL 级预处理语句属于某个服务端连接。两个客户端预处理同一个名字，迟早会落到同一条服务端连接上：

pooled -c "PREPARE get_one AS SELECT 1;"
pooled -c "PREPARE get_one AS SELECT 1;"

PREPARE
ERROR:  prepared statement "get_one" already exists

PgBouncer 从不跟踪 SQL 级的 PREPARE：配置参考写明，无论怎么设置，它都会把 PREPARE、EXECUTE 和 DEALLOCATE 直接转发给 Postgres。通过事务级连接池时，不要使用它们。（pooled -c "DEALLOCATE ALL;" 可以清理实验环境。）

大多数人其实是通过驱动碰到这个错误的。很多驱动会使用协议级预处理语句，有的默认使用，有的在查询重复出现后使用：它们先以一个名字发送一次查询，之后按名字执行。下面是 node-postgres（pg 8.23）从两个客户端执行一个命名查询：

// named.js
const { Client } = require('pg');

async function main() {
  const url = process.env.DATABASE_URL;
  const a = new Client({ connectionString: url });
  const b = new Client({ connectionString: url });
  await a.connect();
  await b.connect();
  const query = { name: 'get-account', text: 'SELECT $1::int AS id' };
  try {
    console.log('a:', (await a.query({ ...query, values: [1] })).rows);
    console.log('b:', (await b.query({ ...query, values: [2] })).rows);
  } catch (err) {
    console.log('error:', err.message);
  } finally {
    await a.end();
    await b.end();
  }
}

main();

npm install pg@8.23.1
DATABASE_URL=postgres://app:secret@localhost:6432/app node named.js

a: [ { id: 1 } ]
error: prepared statement "get-account" already exists

反过来的情况需要一个有两个服务端连接的池。客户端 A 执行命名查询，另一个客户端开启事务并占用了 A 刚用过的服务端连接，然后 A 再次执行这个查询。驱动记得自己已经预处理过 get-account，只发送“执行”消息，结果落到了另一条服务端连接上：prepared statement "get-account" does not exist。这两种错误我们都在实验中复现了。

PgBouncer 1.21 为协议级的情况加入了修复：把 max_prepared_statements 设为大于零后，它会跟踪每个客户端预处理的语句，并在客户端拿到的任意服务端连接上预处理它们。我们用 max_prepared_statements = 200 重新运行了两个实验，两种错误都消失了，而上面 SQL 级的 PREPARE 依然失败。

在 Relyt One 的连接池连接字符串上，这项跟踪是关闭的（max_prepared_statements = 0），所以通过连接池连接时，请在驱动中关闭命名预处理语句：

你会失去“语句只预处理一次”所节省的规划时间。对于 Web 请求执行的那些短查询，这点开销通常比网络往返小得多；如果你依赖预处理语句，请使用直连连接。

会话级 advisory lock 是确保只有一个 worker 运行某个任务的常用方式。它们属于服务端连接，而且是可重入的：持有锁的会话可以再次获取它。在事务级连接池后面，结果就变成了这样：

pooled -c "SELECT pg_advisory_lock(42);"
pooled -c "SELECT pg_try_advisory_lock(42) AS got_it;"

 pg_advisory_lock
------------------

(1 row)

 got_it
--------
 t
(1 row)

Worker A 获取了 42 号锁。Worker B 请求同一把锁，Postgres 同意了，因为两个请求都经过同一条服务端连接。现在两个 worker 都以为任务归自己独占。池更大时，失败方式不同，但同样糟糕：worker A 的解锁落到另一条服务端连接上，Postgres 回一个警告说你并不持有这把锁，而 A 完成之后，这把锁仍被原来的服务端连接持有。（pooled -c "SELECT pg_advisory_unlock_all();" 可以释放实验环境中的锁。）

修复：按事务获取锁。 pg_advisory_xact_lock() 和 pg_try_advisory_xact_lock() 会在 COMMIT 或 ROLLBACK 时释放锁，因此锁和工作共享同一个事务和同一条服务端连接。要看到它把第二个 worker 挡在外面，请在实验环境中设置 default_pool_size = 2 并重启 PgBouncer，让两个 worker 可以同时运行：

docker restart pgbouncer
pooled -c "BEGIN; SELECT pg_advisory_xact_lock(42); SELECT pg_sleep(3); COMMIT;" > /dev/null &
sleep 1
pooled -c "SELECT pg_try_advisory_xact_lock(42) AS got_it;"
wait

 got_it
--------
 f
(1 row)

任务必须能放进一个事务里。如果放不进，请通过直连连接获取会话锁。

设置每个连接默认值的一种常见方式是连接字符串中的 options 参数。PgBouncer 会拒绝它：

psql -X "host=localhost port=6432 user=app dbname=app options=-cstatement_timeout=5s" -c "SELECT 1"

psql: error: connection to server at "localhost" (::1), port 6432 failed: FATAL:  unsupported startup parameter in options: statement_timeout

PgBouncer 只接受它能跟踪的启动参数。你可以让它忽略未知参数（ignore_startup_parameters），但那样设置会被悄无声息地丢掉，情况更糟。Relyt One 的连接池端点同样不会透传 options。默认值请用第 1 节中的 ALTER ROLE … SET，单个事务请用 SET LOCAL。

这三者都需要一个存活超过一个事务的会话，PgBouncer 的功能表把它们在事务池模式下标为“Never”：

• LISTEN 注册的是服务端连接，而不是你的客户端，所以通知会发给恰好持有这条服务端连接的客户端，或者谁也收不到。用 NOTIFY 发送是可以的。
• WITH HOLD 游标在 COMMIT 之后仍在服务端连接上保持打开；你的下一次 FETCH 可能落到别处。
• 存活超过所在事务的临时表会泄漏给其他客户端。下面客户端 A 创建了一张，而从没创建过它的客户端 B 可以查询它：

pooled -c "CREATE TEMP TABLE scratch (id int);"
pooled -c "SELECT count(*) FROM scratch;"

CREATE TABLE
 count
-------
     0
(1 row)

用 ON COMMIT DROP 创建的临时表会在事务结束时消失，是安全的。其他情况请使用直连连接。

Relyt One 为每个计算实例提供两个连接字符串。在 Connect（连接）对话框中，Connection pooling（连接池）开关在两者之间切换。连接池连接经过使用事务级池化的 PgBouncer，最多支持 10,000 个并发客户端连接；直连连接是普通的 Postgres 会话，支持上面所有功能。

两个连接字符串指向同一个数据库，所以你可以在一个应用中同时使用两者：连接池连接承载请求流量，直连连接用于迁移步骤和监听通知的后台 worker。

1. 在代码中搜索每一个普通的 SET（写在 BEGIN … COMMIT 里的 SET 在提交之后依然存在），把它替换为事务内的 SET LOCAL 或 set_config(…, true)。
2. 把每个连接的默认值从 options= 移到 ALTER ROLE … SET。
3. 在驱动中关闭命名预处理语句（见第 2 节中的表格）。
4. 把 pg_advisory_lock() 替换为 pg_advisory_xact_lock()，或把那个任务移到直连连接上。
5. 把 LISTEN、WITH HOLD 游标和会话级临时表移到直连连接上。
6. 迁移继续走直连连接。
7. 用小池子测试。正如实验所示，大小为一的池会把生产环境中的间歇性 bug 变成每次运行都会出现的错误。

免费开始使用