Psycopg执行SQL脚本时X.sql偶现卡顿问题排查求助

• Psycopg连接隐性状态异常：即便开启了autocommit，长时间复用同一个连接时，可能因网络波动、PostgreSQL端的连接残留状态（如未处理的异步通知、协议层面的隐性错误）导致客户端（Psycopg）陷入等待，而非数据库端在执行查询。pg_stat_activity看不到X.sql的查询，说明命令根本没传到数据库，或者客户端卡在了发送/接收环节。
• 大SQL文本的IO阻塞：X.sql逻辑复杂、内容可能较大，cursor.execute()读取完整SQL文本后发送给数据库的过程中，若连接存在IO缓冲区积压等隐性阻塞，会导致客户端挂起，数据库并未实际收到查询请求。
• 连接复用的状态累积：多次执行SQL后，连接内部的临时资源、协议状态可能出现累积性异常，引发后续执行无响应。

是，定期重建连接是解决这类偶发无响应问题的有效手段，尤其适合长时间运行、执行多批复杂SQL的自动化场景。更精准的做法是针对高复杂度SQL（如X.sql）单独使用新连接执行，或者设置连接复用的次数阈值（比如每执行N个SQL后重建），而非无规律的定时重建。

1. 为复杂SQL创建独立连接
   对X.sql这类易出问题的SQL，单独创建新连接执行，避免影响其他SQL的复用连接：

   def execute_complex_SQL(file_path, conn_params):
       with psycopg.connect(**conn_params) as conn:
           conn.autocommit = True
           with conn.cursor() as cursor:
               with open(file_path, "r") as file:
                   query = file.read()
               print("Executing complex SQL: {}".format(file_path))
               cursor.execute(query)
   

   主流程中遇到X.sql时调用该函数，其余SQL继续复用原有连接。

2. 设置连接复用上限
   维护执行计数器，达到阈值后重建连接：

   conn = psycopg.connect(**conn_params)
   conn.autocommit = True
   execute_count = 0
   MAX_EXECUTIONS_PER_CONN = 10  # 根据实际场景调整
   
   for sql_file in sql_files:
       if execute_count >= MAX_EXECUTIONS_PER_CONN:
           conn.close()
           conn = psycopg.connect(**conn_params)
           conn.autocommit = True
           execute_count = 0
       
       if sql_file == "X.sql":
           execute_complex_SQL(sql_file, conn_params)
       else:
           execute_SQL_query(conn, sql_file)
       
       execute_count += 1
   
   conn.close()
   

3. 添加执行超时机制
   为cursor.execute()设置超时，避免脚本无限挂起。Psycopg3支持直接在执行时指定超时：

   # 在execute_SQL_query的cursor.execute处修改
   cursor.execute(query, timeout=300)  # 设置5分钟超时
   

   超时后会抛出psycopg.errors.QueryCanceled异常，捕获后可重建连接并重试X.sql（需确保X.sql是幂等的，你的场景中脚本会跳过已执行SQL，重试无风险）。

4. 规范SQL文本处理
   用psycopg.sql.SQL()包装查询，避免字符串转义或解析差异：

   from psycopg import sql
   
   # 修改execute_SQL_query中的查询读取逻辑
   query = sql.SQL(file.read())
   cursor.execute(query)
   

内容的提问来源于stack exchange，提问作者rob.loh