如何定位pg_stat_activity中函数/存储过程内的异常SQL语句

1. 启用pg_stat_statements扩展 #

这是捕获函数内部SQL最常用的方式，能记录所有执行过的SQL语句（包括函数触发的嵌套语句）：

• 先修改postgresql.conf配置：

  shared_preload_libraries = 'pg_stat_statements'
  pg_stat_statements.track = all
  

• 重启PostgreSQL服务后，在目标数据库创建扩展：

  CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
  

• 查询函数内部执行的SQL（可通过关键词过滤）：

  SELECT query, calls, total_time, rows
  FROM pg_stat_statements
  WHERE query LIKE '%目标操作关键词%'
  ORDER BY total_time DESC;
  

  结果会展示每条SQL的调用次数、总耗时、返回行数，可快速定位慢查询或阻塞源。

2. 用auto_explain跟踪函数内SQL的执行计划 #

如果需要排查性能问题，可通过该扩展记录函数内SQL的执行细节：

• 修改postgresql.conf配置：

  shared_preload_libraries = 'auto_explain'
  auto_explain.log_min_duration = 0  # 记录所有SQL，可按需设阈值（如100ms）
  auto_explain.log_analyze = on
  auto_explain.log_buffers = on
  auto_explain.log_nested_statements = on  # 关键：开启嵌套语句跟踪
  

• 重启服务后，查看数据库日志即可获取函数内部每条SQL的执行计划、耗时等信息。

3. 利用pg_stat_activity其他字段辅助定位 #

虽然query字段仅显示函数调用语句，但结合以下字段可判断执行状态：

• wait_event_type/wait_event：确认会话是否在等待资源（如锁、IO），可关联pg_locks查看锁持有情况。
• state：区分会话是active（正在执行）还是idle in transaction（事务未结束）。
• backend_start/query_start：计算会话运行时长，判断是否为长期阻塞的函数。

4. 临时修改函数添加日志（应急方案） #

若以上方法无法快速定位，可临时给函数添加日志输出：

CREATE OR REPLACE FUNCTION your_target_function()
RETURNS void AS $$
BEGIN
  RAISE NOTICE '执行步骤：查询table_x，时间：%', now();
  SELECT * FROM table_x;
  
  RAISE NOTICE '执行步骤：更新table_y，时间：%', now();
  UPDATE table_y SET col = ... WHERE ...;
END;
$$ LANGUAGE plpgsql;

通过数据库日志或客户端日志查看输出，即可定位到当前执行的具体语句。

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