長時間の'idle in transaction'セッションを制御された方法で一括終了

コマンド

-- 1) Dry run first: who will be affected?
SELECT pid, usename, application_name, client_addr,
       now() - state_change AS idle_for, left(query, 80) AS last_query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
  AND now() - state_change > interval '15 minutes'
  AND backend_type = 'client backend'
  AND pid <> pg_backend_pid();

-- 2) Terminate; wait up to 5 s for each process to exit
SELECT pid, usename, now() - state_change AS idle_for,
       pg_terminate_backend(pid, 5000) AS terminated
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
  AND now() - state_change > interval '15 minutes'
  AND backend_type = 'client backend'
  AND pid <> pg_backend_pid();

概要

まず一定時間トランザクションを開いたままidleになっているセッションを一覧表示し、その後終了する。timeoutパラメータはプロセスが実際に終了するまで(またはタイムアウトが経過するまで)待機する。

使用する場面

ロックを保持したまま忘れられたセッションがブロッキングを引き起こす、またはxmin horizonを引き止めてVACUUMを停滞させるときの緊急介入に。

注意

pg_cancel_backendは'idle in transaction'セッションには何もしない(取り消す実行中のクエリがない)。pg_terminate_backendが必要で、未コミットの変更はすべてロールバックされる。usename/application_nameでフィルタを絞り込み、レプリケーション、バックアップ、監視のセッションを除外する。接続プールは終了された接続を再度開く。タイムアウトが経過すると、関数は警告を出してfalseを返す。恒久的な対策として、ロールごとにidle_in_transaction_session_timeoutを設定する。