Контролируемо массово завершить долгие сессии '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();

Что делает

Сначала выводит сессии, оставившие транзакцию открытой и простаивающей заданное время, затем завершает их; параметр timeout ожидает, пока процесс действительно завершится (или истечёт тайм-аут).

Когда использовать

Для экстренного вмешательства, когда забытые сессии, удерживающие блокировки, вызывают блокирование или сдерживают горизонт xmin и останавливают VACUUM.

Внимание

pg_cancel_backend ничего не делает для сессии 'idle in transaction' (нет выполняющегося запроса для отмены); нужен pg_terminate_backend, и все незафиксированные изменения откатываются. Сужайте фильтр по usename/application_name; исключите сессии репликации, бэкапа и мониторинга. Пулы подключений заново откроют завершённые подключения. Если тайм-аут истекает, функция выдаёт предупреждение и возвращает false. В качестве постоянного решения задайте idle_in_transaction_session_timeout для роли.