Poisoned Postgres connection pools

{ "title_zh": "被污染的 PostgreSQL 连接池：为什么只读故障会悄悄扩散", "body_zh": "# 被污染的 PostgreSQL 连接池：为什么只读故障会悄悄扩散\n\n最令人不安的数据库故障之一，是应用突然表现得像连上了一个“只读数据库”：查询正常，写入失败，但监控里看不到明确的主库宕机或权限变更。一个值得重点排查的方向是：问题不一定来自数据库实例本身，也可能来自连接池里被污染、并持续复用的会话状态。\n\n## 一条连接不只是一个 TCP 通道\n\nPostgreSQL 连接会携带会话级状态。事务隔离级别、search_path、角色属性、临时表、未提交事务，以及 default_transaction_read_only 等设置，都可能影响后续使用这条连接的请求。\n\n如果某个请求修改了会话状态，却没有在归还连接前恢复，连接池就可能把这份状态交给下一个请求。于是，最初只影响单个连接的问题，会随着连接复用扩散到更多请求。\n\n“只读”也有多种来源，不能看到写入失败就直接认定连接池被污染：\n\n- 当前连接的事务被设置为只读。\n- 会话参数 default_transaction_read_only 被修改。\n- 应用实际连接到了只读副本或处于恢复状态的实例。\n- 数据库角色、权限或路由配置发生了变化。\n- 连接上的事务没有正确结束，导致后续操作继承异常状态。\n\n排查时要把“数据库实例状态”和“连接会话状态”分开验证。\n\n## 先确认：实例只读，还是会话只读\n\n可以用同一条应用连接执行下面的查询。不要只在管理工具里执行，因为管理工具使用的可能是另一条连接。\n\nsql\nSELECT\n current_database() AS database_name,\n current_user AS user_name,\n inet_server_addr() AS server_addr,\n inet_server_port() AS server_port,\n pg_is_in_recovery() AS is_replica,\n current_setting('transaction_read_only') AS transaction_read_only,\n current_setting('default_transaction_read_only') AS default_transaction_read_only,\n current_setting('search_path') AS search_path;\n\n\n如果需要从命令行快速检查，可以把连接字符串替换成应用实际使用的地址、端口和用户：\n\nbash\npsql \"$DATABASE_URL\" -X -v ON_ERROR_STOP=1 <<'SQL'\nSELECT pg_is_in_recovery() AS is_replica;\nSHOW transaction_read_only;\nSHOW default_transaction_read_only;\nSELECT current_setting('search_path');\nSQL\n\n\npg_is_in_recovery() 返回 true，说明当前实例处于恢复状态，应用可能确实连到了副本。若实例不是副本，但 transaction_read_only 或 default_transaction_read_only 异常，则应继续检查事务边界、连接池复位逻辑和应用中执行过的 SET 命令。\n\n一次性的清理命令可以帮助验证假设，但不应被当成永久修复：\n\nsql\nROLLBACK;\nRESET ALL;\nDISCARD TEMP;\n\n\nROLLBACK 用于结束当前事务，RESET ALL 恢复会话参数，DISCARD TEMP 清理临时表。生产环境是否允许执行这些命令，要结合驱动、连接池和应用对会话状态的依赖进行评估。某些连接池会提供专门的连接复位机制，应该优先使用并在借出、归还连接的路径上进行测试。\n\n## 让连接池对“脏连接”负责\n\n可以这样实践：把连接视为带状态的资源，而不是用完即可丢回池中的文件描述符。应用代码需要遵守几个边界：\n\n- 每次借用连接后，明确开始并结束事务。\n- 不要把业务所需的 SET 修改永久留在会话级别；能使用事务级别设置时，优先使用事务级别设置。\n- 请求异常时显式回滚，避免把失败事务归还给连接池。\n- 连接归还前执行驱动或连接池支持的 reset 流程。\n- 对连接池耗尽、连接重建、写入失败和只读状态分别设置监控指标。\n\n下面是一个可改造的诊断脚本。它不会修改数据库，只检查同一个连接在事务前后的关键状态。运行前安装 psycopg，并设置 DATABASE_URL。\n\npython\nimport os\nimport psycopg\n\nDATABASE_URL = os.environ[\"DATABASE_URL\"]\n\nwith psycopg.connect(DATABASE_URL) as conn:\n with conn.cursor() as cur:\n cur.execute(\"\"\"\n SELECT\n pg_backend_pid(),\n pg_is_in_recovery(),\n current_setting('transaction_read_only'),\n current_setting('default_transaction_read_only')\n \"\"\")\n print(\"before:\", cur.fetchone())\n\n cur.execute(\"SET LOCAL default_transaction_read_only = on\")\n cur.execute(\"SHOW default_transaction_read_only\")\n print(\"inside transaction:\", cur.fetchone())\n\n conn.rollback()\n\n with conn.cursor() as cur:\n cur.execute(\"SHOW default_transaction_read_only\")\n print(\"after rollback:\", cur.fetchone())\n\n\n这个脚本展示了一个重要区别：SET LOCAL 的作用域绑定到当前事务，回滚后不会把该设置带到下一次事务。真实项目中仍要根据使用的连接池配置验证行为，尤其要测试异常路径、超时路径和请求取消路径，而不能只测试成功请求。\n\n## AI 可以怎样帮助定位\n\nAI 在这类故障中最适合做“证据整理和假设排序”，而不是直接替代数据库管理员执行操作。可以把以下信息脱敏后交给模型：\n\n- 写入失败的完整错误码和时间窗口。\n- 应用实例、连接池大小、活跃连接数和等待连接数。\n- 失败连接上的 pg_is_in_recovery()、只读参数和服务器地址。\n- 最近的数据库路由、故障切换、部署和连接池配置变更。\n- 同一个请求的事务开始、提交、回滚和连接归还日志。\n\n一个实用的提示词可以要求模型按证据区分实例级问题和会话级问题：\n\ntext\n你是 PostgreSQL 故障分析助手。请根据下面的脱敏日志排查“查询正常但写入失败”的原因。\n请严格输出：\n1. 已确认事实\n2. 仍缺失的证据\n3. 按可能性排序的假设\n4. 每个假设对应的只读验证 SQL\n5. 不应直接执行的高风险操作\n\n不要假设数据库一定是副本，也不要建议删除数据或修改权限。\n\n\n模型可以帮助发现日志中的时间关联，例如某次故障切换后只有部分连接失败，或者某个应用版本开始执行会话级 SET。但它无法凭空知道连接池是否真的复用了脏连接，仍需要用同一条应用连接采集状态，并对比主库、副本和不同应用实例的结果。\n\n## 上线前的检查清单\n\n修复这类问题时，可以把验证范围收敛到下面几项：\n\n- 写入失败时记录数据库地址、后端 PID 和只读状态，但避免记录密码和完整连接字符串。\n- 检查连接归还前是否发生 ROLLBACK 或池提供的 reset。\n- 对连接池中的每条连接执行一次可观测的健康检查，而不是只检查 TCP 可达性。\n- 验证故障切换后旧连接是否被丢弃并重新建立。\n- 用集成测试覆盖异常、超时、取消和事务嵌套场景。\n- 让 AI 参与日志归纳和排查步骤生成，同时保留人工审批和只读验证边界。\n\n连接池的价值是复用连接、降低建立成本；代价是会话状态也可能被复用。面对“数据库突然只读”的故障，最有效的路径不是立即重启所有服务，而是先确认应用到底连到了哪里，再确认这条连接当前处于什么状态，最后检查连接池是否在归还资源时把状态清干净。", "title_en": "Poisoned PostgreSQL Pools: How Read-Only Failures Spread Through Reused Sessions", "body_en": "# Poisoned PostgreSQL Pools: How Read-Only Failures Spread Through Reused Sessions\n\nOne of the most unsettling database incidents is an application that suddenly behaves as if it is connected to a read-only database. Queries continue to work, writes fail, and there is no obvious primary outage or permission change. The database instance may not be the only suspect: a connection pool can keep reusing sessions that carry unexpected state.\n\n## A Connection Carries Session State\n\nA PostgreSQL connection is more than a TCP channel. It carries session-level state such as transaction settings, search_path, temporary tables, role context, unfinished transactions, and values like default_transaction_read_only.\n\nIf a request changes session state and does not restore it before returning the connection, the pool can hand that state to another request. A problem that started on one connection can then appear to spread as the pool reuses it.\n\nA write failure does not prove that the pool is poisoned. Several conditions can produce similar symptoms:\n\n- The current transaction is read-only.\n- default_transaction_read_only was changed at the session level.\n- The application is connected to a read replica or a server in recovery.\n- Role permissions or routing configuration changed.\n- An unfinished transaction left the connection in an unexpected state.\n\nThe investigation should separate database-instance state from per-session state.\n\n## Verify the Instance and the Session Separately\n\nRun the following query through the same connection used by the application. A database administration tool may be using a different connection and therefore show different state.\n\nsql\nSELECT\n current_database() AS database_name,\n current_user AS user_name,\n inet_server_addr() AS server_addr,\n inet_server_port() AS server_port,\n pg_is_in_recovery() AS is_replica,\n current_setting('transaction_read_only') AS transaction_read_only,\n current_setting('default_transaction_read_only') AS default_transaction_read_only,\n current_setting('search_path') AS search_path;\n\n\nFor a quick command-line check, replace the connection string with the address, port, and user actually used by the application:\n\nbash\npsql \"$DATABASE_URL\" -X -v ON_ERROR_STOP=1 <<'SQL'\nSELECT pg_is_in_recovery() AS is_replica;\nSHOW transaction_read_only;\nSHOW default_transaction_read_only;\nSELECT current_setting('search_path');\nSQL\n\n\nIf pg_is_in_recovery() returns true, the application may genuinely be connected to a replica. If the server is not a replica but one of the read-only settings is unexpected, inspect transaction boundaries, pool reset behavior, and application code that runs SET commands.\n\nThe following commands can help test a hypothesis on a disposable or carefully selected connection, but they are not a permanent fix:\n\nsql\nROLLBACK;\nRESET ALL;\nDISCARD TEMP;\n\n\nROLLBACK ends the current transaction, RESET ALL restores session parameters, and DISCARD TEMP removes temporary tables. Whether these commands are appropriate in production depends on the driver, pool, and application’s session-state requirements. Prefer the connection reset mechanism provided by the driver or pool, and test it on both success and failure paths.\n\n## Make the Pool Own Dirty-Connection Handling\n\nA practical rule is to treat a pooled connection as a stateful resource. Application code should establish clear boundaries:\n\n- Start and finish transactions explicitly.\n- Avoid permanent session-level SET changes when a transaction-scoped setting is sufficient.\n- Roll back explicitly after errors so failed transactions are not returned to the pool.\n- Run the driver or pool’s reset procedure before a connection is reused.\n- Monitor pool exhaustion, connection creation, write failures, and read-only state separately.\n\nThe following small diagnostic script can be adapted to a real service. It does not modify persistent data. Install psycopg and set DATABASE_URL before running it.\n\npython\nimport os\nimport psycopg\n\nDATABASE_URL = os.environ[\"DATABASE_URL\"]\n\nwith psycopg.connect(DATABASE_URL) as conn:\n with conn.cursor() as cur:\n cur.execute(\"\"\"\n SELECT\n pg_backend_pid(),\n pg_is_in_recovery(),\n current_setting('transaction_read_only'),\n current_setting('default_transaction_read_only')\n \"\"\")\n print(\"before:\", cur.fetchone())\n\n cur.execute(\"SET LOCAL default_transaction_read_only = on\")\n cur.execute(\"SHOW default_transaction_read_only\")\n print(\"inside transaction:\", cur.fetchone())\n\n conn.rollback()\n\n with conn.cursor() as cur:\n cur.execute(\"SHOW default_transaction_read_only\")\n print(\"after rollback:\", cur.fetchone())\n\n\nThe example highlights why scope matters: SET LOCAL is tied to the current transaction, so the setting does not survive the rollback into the next transaction. In a real service, verify the actual pool behavior, especially for exceptions, timeouts, and request cancellation. A successful-request test alone is insufficient.\n\n## Where AI Helps During the Investigation\n\nAI is useful here for organizing evidence and ranking hypotheses, not for blindly executing database operations. Redacted material can include:\n\n- The complete write error and its time window.\n- Application instance, pool size, active connections, and waiters.\n- Server address, backend PID, pg_is_in_recovery(), and read-only settings from the failing connection.\n- Recent failover, routing, deployment, and pool-configuration changes.\n- Transaction begin, commit, rollback, and connection-return events for the same request.\n\nA focused prompt can force the analysis to distinguish instance-level and session-level causes:\n\ntext\nYou are a PostgreSQL incident-analysis assistant. Investigate the redacted logs below for a case where reads work but writes fail.\nReturn exactly:\n1. Confirmed facts\n2. Missing evidence\n3. Hypotheses ranked by likelihood\n4. Read-only SQL checks for each hypothesis\n5. High-risk actions that must not be executed directly\n\nDo not assume the server is a replica. Do not suggest deleting data or changing permissions.\n\n\nAI may uncover useful correlations, such as failures starting after a failover or appearing only on connections created by a particular application version. It cannot establish that a dirty session was reused without evidence from the application connection itself. The final diagnosis still requires comparing the primary, replicas, and affected application instances.\n\n## An Adoption Checklist\n\nFor production readiness, verify the following:\n\n- Log the database address, backend PID, and read-only state during a write failure without exposing credentials or full connection strings.\n- Confirm that every returned connection is rolled back or reset according to the pool’s contract.\n- Make health checks inspect database role and write capability, not just TCP reachability.\n- Verify that old connections are discarded and recreated after failover.\n- Cover exceptions, timeouts, cancellations, and nested transactions in integration tests.\n- Use AI for log summarization and read-only diagnostic planning, with human approval for operational changes.\n\nConnection pooling reduces connection-creation overhead, but it also reuses session state. When an application suddenly looks read-only, confirm where it is connected, inspect the state of that exact session, and then verify that the pool cleans the state before handing the connection to another request.", "seo_description_en": "Learn how poisoned PostgreSQL connection pools spread read-only session state, diagnose the cause, reset safely, and use AI to rank incident hypotheses.", }