長時間実行のトランザクションと'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の経過時間とともに表示する。

使用する場面

肥大化が進むとき、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と、アプリケーションでの正しいトランザクション境界。