PostgreSQL大批量插入后出现ClientRead等待如何解决？

核心现象 #

基于PostgreSQL 15.1编写的存储过程，从分区表data_202306读取2000万行上月数据，关联其他表完成更新后插入新分区data_202307，采用CTE优化执行效率。执行时进程无法自动终止，必须通过pg_terminate_backend强制停止；通过pg_stat_activity观察到，函数执行约10分钟后进入idle状态，等待事件为ClientRead，但终止会话后发现data_202307数据已完整插入，说明核心插入逻辑已完成，存储过程后续流程出现卡顿。

可能原因 #

• 事务控制不完整：存储过程中存在未提交/未回滚的事务，或分支逻辑中遗漏事务收尾操作，导致会话挂起等待事务确认。
• 临时资源未清理：存储过程内创建的临时表、游标未在逻辑结束前显式清理，占用会话资源导致无法正常终止。
• 客户端-服务器交互异常：ClientRead状态表示服务器在等待客户端指令，可能是客户端驱动未开启自动提交，或连接池配置导致会话未主动结束。
• 版本特定bug：PostgreSQL 15.1在分区表批量插入后的事务收尾逻辑中存在已知异常，可能引发会话卡顿。

排查与解决步骤 #

1. 检查并完善事务控制

   • 若存储过程使用显式事务（BEGIN/COMMIT），需确保所有分支逻辑都有明确的COMMIT或ROLLBACK操作，避免事务遗留。
   • 示例：

     CREATE OR REPLACE PROCEDURE transfer_partition_data()
     LANGUAGE plpgsql
     AS $$
     BEGIN
         BEGIN
             -- 核心CTE插入逻辑
             WITH updated_data AS (
                 SELECT d.*, o.some_column
                 FROM data_202306 d
                 JOIN other_table o ON d.id = o.id
             )
             INSERT INTO data_202307 SELECT * FROM updated_data;
             COMMIT;
         EXCEPTION
             WHEN OTHERS THEN
                 ROLLBACK;
                 RAISE;
         END;
     END;
     $$;
     

2. 清理临时资源

   • 在存储过程末尾添加临时表、游标清理语句：

     DROP TABLE IF EXISTS temp_process_data;
     CLOSE ALL;
     

3. 优化存储过程结束逻辑

   • 在存储过程末尾添加明确的RETURN语句，确保执行到核心逻辑后主动终止会话流程。

4. 排查客户端配置

   • 检查客户端驱动（如JDBC、psycopg）是否开启自动提交，避免客户端等待手动提交指令导致服务器进入ClientRead状态。
   • 例如psycopg2中需设置autocommit=True，或调用存储过程后显式执行conn.commit()。

5. 升级PostgreSQL版本

   • 将PostgreSQL 15.1升级至15.x系列的最新稳定版本（如15.7），修复版本特定的分区表事务收尾bug。

6. 监控执行细节

   • 通过pg_stat_activity跟踪存储过程的执行步骤，确认卡顿发生的节点：

     SELECT pid, query, state, wait_event_type, wait_event 
     FROM pg_stat_activity 
     WHERE query LIKE '%transfer_partition_data%';
     

内容的提问来源于stack exchange，提问作者jjjjlau