本文详解 psycopg2 中连接未自动关闭导致 pg_stat_activity 中连接长期处于 idle 状态、最终引发“remaining connection slots are reserved”错误的根本原因，并提供符合最佳实践的连接管理方案。
本文详解 psycopg2 中连接未自动关闭导致 `pg_stat_activity` 中连接长期处于 idle 状态、最终引发“remaining connection slots are reserved”错误的根本原因，并提供符合最佳实践的连接管理方案。
在使用 psycopg2 连接 PostgreSQL 时，一个常见但极易被忽视的问题是：连接对象不会在 with 语句退出时自动关闭——它仅自动提交或回滚当前事务（即执行 conn.commit() 或 conn.rollback()），而底层 TCP 连接仍保持打开状态并滞留在 pg_stat_activity 中，状态为 idle。这并非 bug，而是 psycopg2 的明确设计行为（官方文档明确说明）：
"Note that the connection is not closed by the context and it can be used for several contexts."
这意味着以下代码存在严重资源泄漏风险：
with psycopg2.connect(...) as conn: # ❌ 错误认知：以为这里会 close()
with conn.cursor() as c:
psycopg2.extras.execute_values(c, query, values)
# conn 依然存活！连接未释放，进入 idle 状态
即使改写为显式调用 conn.commit() 和 conn.close()，若未妥善处理异常路径（如执行失败时未关闭），仍可能导致连接泄露。更危险的是，你提到使用 SSH 隧道 + VS Code 端口转发访问 Azure 上的 PostgreSQL —— 此类网络链路本就存在更高延迟与连接稳定性压力，未及时释放的 idle 连接会更快堆积，迅速触达 PostgreSQL 的 max_connections 限制（典型错误信息：FATAL: remaining connection slots are reserved for roles with the SUPERUSER attribute）。
✅ 正确做法：始终显式关闭连接，且推荐使用 try/finally 或上下文管理器封装连接生命周期。以下是推荐的健壮实现：
import psycopg2
from psycopg2 import sql
from psycopg2.extras import execute_values
def bulk_insert_to_postgres(
dbname, user, password, host, port, app_name,
table_name, columns, records, batch_size=1000
):
conn = None
try:
conn = psycopg2.connect(
dbname=dbname, user=user, password=password,
host=host, port=port, application_name=app_name
)
with conn.cursor() as cur:
column_names = ", ".join(columns)
query = sql.SQL("INSERT INTO {} ({}) VALUES %s").format(
sql.Identifier(table_name),
sql.SQL(", ").join(map(sql.Identifier, columns))
)
values = [tuple(r.values()) for r in records]
execute_values(cur, query, values, page_size=batch_size)
conn.commit() # 显式提交事务
except Exception as e:
if conn:
conn.rollback() # 出错时回滚
raise e
finally:
if conn:
conn.close() # ✅ 关键：必须显式关闭
⚠️ 重要注意事项：
-
不要依赖
del conn或gc.collect()：Python 的引用计数或垃圾回收不保证立即释放底层 socket，close()才是唯一可靠方式； -
避免全局/模块级长连接：除非使用连接池（如
psycopg2.pool.ThreadedConnectionPool），否则每次操作应创建并销毁连接； -
升级至
psycopg3？谨慎评估：其with connection:确实会自动关闭连接，但 API 不兼容、生态支持仍在演进中；生产环境建议优先修复 psycopg2 的使用方式； -
监控验证：执行后可通过
SELECT * FROM pg_stat_activity WHERE state = 'idle';检查残留连接，确认修复效果。
总结：psycopg2 的连接管理需“手动即安全”。牢记——with 管理事务，close() 管理连接。只有主动释放，才能让数据库连接真正归还给连接池或操作系统，从根本上杜绝 idle 积压与连接耗尽问题。
