장시간 실행 및 '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분보다 오래되었거나 트랜잭션 안에서 idle 상태인 세션을 트랜잭션 경과 시간, 현재 상태 유지 시간, 할당된 XID, 보유한 xmin의 경과와 함께 보여 준다.

사용 시점

bloat이 증가할 때, VACUUM이 'dead row versions cannot be removed yet'을 보고할 때, 락 대기 중, 또는 커넥션 풀 누수가 의심될 때.

주의

장시간 트랜잭션(특히 REPEATABLE READ/SERIALIZABLE 또는 열린 커서를 보유한 트랜잭션)은 backend_xmin을 통해 VACUUM의 정리 기준선(xmin horizon)을 붙잡아 데이터베이스 전체에 bloat과 XID 노화를 일으킨다. 'idle in transaction (aborted)'는 오류가 발생해 ROLLBACK을 기다리는 세션이다. 영구 해결: 롤별 idle_in_transaction_session_timeout과 애플리케이션의 올바른 트랜잭션 경계.