查找长时间运行和 'idle in transaction' 的事务

命令

SELECT pid,
       usename,
       datname,
       application_name,
       client_addr,
       state,
       now() - xact_start AS xact_age,
       now() - state_change AS in_state_for,
       backend_xid,
       age(backend_xmin) AS xmin_age,
       left(regexp_replace(query, '\s+', ' ', 'g'), 100) AS last_query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND backend_type = 'client backend'
  AND pid <> pg_backend_pid()
  AND (now() - xact_start > interval '5 minutes' OR state LIKE 'idle in transaction%')
ORDER BY xact_start;

作用

显示打开的事务超过 5 分钟或在事务中处于空闲状态的会话；包括事务时长、当前状态持续时间、已分配的 XID 以及其持有的 xmin 的年龄。

适用场景

当膨胀增长、VACUUM 报告 'dead row versions cannot be removed yet'、出现锁等待，或怀疑连接池泄漏时。

注意

长事务（尤其是 REPEATABLE READ/SERIALIZABLE 或持有打开游标的事务）会通过 backend_xmin 阻碍 VACUUM 的清理边界（xmin horizon），在整个数据库中造成膨胀和 XID 老化。'idle in transaction (aborted)' 是遇到错误并等待 ROLLBACK 的会话。永久解决方法：按角色设置 idle_in_transaction_session_timeout，并在应用中正确设定事务边界。