# PgBouncer 事务池模式下会出哪些问题，以及如何逐一修复 为什么在事务级连接池后面会遇到 'prepared statement already exists'、设置在请求之间泄漏、advisory lock 什么也锁不住？本文在 PgBouncer 1.24 和 PostgreSQL 17 上逐一复现，给出每个问题的修复方法，以及何时改用直连连接的判断规则。 **在 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 的功能表](https://www.pgbouncer.org/features.html)、它的配置参考以及我们的实验： | 你用的是 | 会出什么问题 | 修复 | | ------------------------------------------- | -------------------------------------- | ----------------------------------------------------------------- | | `SET statement_timeout`、`SET search_path`、… | 设置留在服务端连接上，作用于下一个拿到这条连接的客户端 | 在事务内用 `SET LOCAL` 或 `set_config(…, true)`；默认值用 `ALTER ROLE … SET` | | SQL 中的 `PREPARE` / `EXECUTE` | `already exists` / `does not exist` 错误 | 不要通过连接池使用 SQL 级预处理语句 | | 驱动的命名预处理语句 | 同样的错误，由驱动报出 | 在驱动中关闭，或使用设置了 `max_prepared_statements` 的连接池 | | `pg_advisory_lock()` | 两个客户端可能同时“持有”同一把锁 | 在事务内用 `pg_advisory_xact_lock()` | | 连接字符串中的 `options=-c …` | 连接被拒绝 | `ALTER ROLE … SET`，或每个事务用 `SET LOCAL` | | `LISTEN`、`WITH HOLD` 游标、存活超过一个事务的临时表 | 结果不可靠或错误 | 使用直连连接 | 本文其余部分会逐行演示这些问题的发生过程。 ## 事务池如何分发连接 ![三个客户端共享 PgBouncer 的一条服务端连接；每个客户端只在一个事务期间拿到它，留在连接上的会话状态会被下一个客户端看到](/blog/assets/d2754c2f0c106687.svg) [PgBouncer 配置参考](https://www.pgbouncer.org/config.html)中的两个细节可以解释下文的大部分现象： - **客户端之间不做任何重置。** `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 的连接池相同，但把池大小设为**一个**服务端连接。只有一个服务端连接时，每个客户端都会落到同一条连接上，所以每种故障每次都会发生，而不是偶尔发生。在生产环境中，池里有几十个服务端连接，同样的故障是间歇性的，这正是它们难以排查的原因。 ```ini ; 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 ``` ```bash 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 中： ```bash pooled() { psql -X -h localhost -p 6432 -U app app "$@"; } ``` ## 1. 一个请求里的 SET 作用于下一个请求 客户端 A 设置了一秒的语句超时，就像应用可能在请求开始时做的那样。客户端 B 是另一个人： ```bash pooled -c "SET statement_timeout = '1s';" pooled -c "SHOW statement_timeout;" -c "SELECT pg_sleep(2);" ``` ```text 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` 一样会泄漏： ```bash pooled -c "SET search_path = app_private, public;" pooled -Atc "SHOW search_path;" ``` ```text SET app_private, public ``` **修复：把设置限定在事务内。** `SET LOCAL` 持续到当前事务结束，所以它作用于事务内的每条语句，并在服务端连接回到池中之前失效： ```bash pooled -c "BEGIN; SET LOCAL statement_timeout = '1s'; SHOW statement_timeout; COMMIT;" pooled -Atc "SHOW statement_timeout;" ``` ```text 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 传给行级安全策略的常用方式。[我们的托管认证指南](/blog/zh-CN/managed-auth-getting-started)用的正是这种模式，出于同样的原因，它在连接池后面依然成立。 对于每个连接都应该有的设置，改为绑定到角色上： ```sql ALTER ROLE app SET statement_timeout = '5s'; ``` Postgres 在服务端连接启动时应用角色设置。PgBouncer 已经打开的服务端连接会保留旧值；在我们的实验中，刚执行完 `ALTER ROLE` 时，通过连接池执行 `SHOW statement_timeout` 仍然输出 `0`，等 PgBouncer 打开了新的服务端连接后才输出 `5s`。 ## 2. "prepared statement already exists" SQL 级预处理语句属于某个服务端连接。两个客户端预处理同一个名字，迟早会落到同一条服务端连接上： ```bash pooled -c "PREPARE get_one AS SELECT 1;" pooled -c "PREPARE get_one AS SELECT 1;" ``` ```text PREPARE ERROR: prepared statement "get_one" already exists ``` PgBouncer 从不跟踪 SQL 级的 `PREPARE`：配置参考写明，无论怎么设置，它都会把 `PREPARE`、`EXECUTE` 和 `DEALLOCATE` 直接转发给 Postgres。通过事务级连接池时，不要使用它们。（`pooled -c "DEALLOCATE ALL;"` 可以清理实验环境。） 大多数人其实是通过驱动碰到这个错误的。很多驱动会使用协议级预处理语句，有的默认使用，有的在查询重复出现后使用：它们先以一个名字发送一次查询，之后按名字执行。下面是 node-postgres（`pg` 8.23）从两个客户端执行一个命名查询： ```js // 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(); ``` ```bash npm install pg@8.23.1 DATABASE_URL=postgres://app:secret@localhost:6432/app node named.js ``` ```text 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`），所以通过连接池连接时，请在驱动中关闭命名预处理语句： | 驱动 | 通过连接池时 | | ------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | node-postgres（`pg`） | 不要在查询配置中传 `name`。普通的参数化查询（`client.query(text, values)`）使用未命名语句，可以正常工作；我们在实验中验证过 | | psycopg 3 | 连接时使用 `prepare_threshold=None`，它会[禁用预处理语句](https://www.psycopg.org/psycopg3/docs/advanced/prepare.html) | | asyncpg | 向 `connect()` 或 `create_pool()` 传入 `statement_cache_size=0`，这是[它的 FAQ](https://magicstack.github.io/asyncpg/current/faq.html) 针对 PgBouncer 的建议 | | Prisma | 在连接池 URL 中加上 `pgbouncer=true`，并通过直连 URL 运行迁移。Prisma 的指南对 PgBouncer 1.21 及以上版本去掉了这个参数，但前提是 `max_prepared_statements` 大于零，而这里并不是（见 [Prisma 的 PgBouncer 指南](https://www.prisma.io/docs/orm/prisma-client/setup-and-configuration/databases-connections/pgbouncer)） | 你会失去“语句只预处理一次”所节省的规划时间。对于 Web 请求执行的那些短查询，这点开销通常比网络往返小得多；如果你依赖预处理语句，请使用直连连接。 ## 3. 什么也锁不住的 advisory lock 会话级 advisory lock 是确保只有一个 worker 运行某个任务的常用方式。它们属于服务端连接，而且是可重入的：持有锁的会话可以再次获取它。在事务级连接池后面，结果就变成了这样： ```bash pooled -c "SELECT pg_advisory_lock(42);" pooled -c "SELECT pg_try_advisory_lock(42) AS got_it;" ``` ```text 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 可以同时运行： ```bash 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 ``` ```text got_it -------- f (1 row) ``` 任务必须能放进一个事务里。如果放不进，请通过直连连接获取会话锁。 ## 4. "unsupported startup parameter in options" 设置每个连接默认值的一种常见方式是连接字符串中的 `options` 参数。PgBouncer 会拒绝它： ```bash psql -X "host=localhost port=6432 user=app dbname=app options=-cstatement_timeout=5s" -c "SELECT 1" ``` ```text 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`。 ## 5. LISTEN、游标和临时表 这三者都需要一个存活超过一个事务的会话，PgBouncer 的功能表把它们在事务池模式下标为“Never”： - **`LISTEN`** 注册的是服务端连接，而不是你的客户端，所以通知会发给恰好持有这条服务端连接的客户端，或者谁也收不到。用 `NOTIFY` 发送是可以的。 - **`WITH HOLD` 游标**在 `COMMIT` 之后仍在服务端连接上保持打开；你的下一次 `FETCH` 可能落到别处。 - 存活超过所在事务的**临时表**会泄漏给其他客户端。下面客户端 A 创建了一张，而从没创建过它的客户端 B 可以查询它： ```bash pooled -c "CREATE TEMP TABLE scratch (id int);" pooled -c "SELECT count(*) FROM scratch;" ``` ```text CREATE TABLE count ------- 0 (1 row) ``` 用 `ON COMMIT DROP` 创建的临时表会在事务结束时消失，是安全的。其他情况请使用直连连接。 ## 在 Relyt One 上用连接池连接还是直连 Relyt One 为每个计算实例提供两个连接字符串。在 **Connect**（连接）对话框中，**Connection pooling**（连接池）开关在两者之间切换。连接池连接经过使用事务级池化的 PgBouncer，最多支持 10,000 个并发客户端连接；直连连接是普通的 Postgres 会话，支持上面所有功能。 | 适合用连接池连接的场景 | 适合用直连连接的场景 | | ----------------------------------------------- | --------------------------------------------------------------- | | 每次调用都打开一个连接的 serverless 和边缘函数 | Schema 迁移（[为什么迁移本来就需要小心](/blog/zh-CN/postgres-migration-locks)） | | 运行几个短事务的 Web 请求 | `LISTEN`、`WITH HOLD` 游标、会话级临时表 | | 否则每个都要各自持有连接的大量应用实例 | 会话级 advisory lock 和长时间运行的任务 | | 把状态保存在事务内的代码（`SET LOCAL`、`set_config(…, true)`） | 依赖预处理语句的驱动或工具 | | | `psql`、图形界面客户端和管理工作 | 两个连接字符串指向同一个数据库，所以你可以在一个应用中同时使用两者：连接池连接承载请求流量，直连连接用于迁移步骤和监听通知的后台 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 变成每次运行都会出现的错误。 [免费开始使用](/auth/signin?ref=blog-pgbouncer-transaction-pooling)