Найти долгие транзакции и транзакции в состоянии '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 для роли и корректные границы транзакций в приложении.