Terminer en masse, de façon contrôlée, les longues sessions 'idle in transaction'

Commande

-- 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();

Ce que fait la commande

Liste d'abord les sessions qui ont laissé une transaction ouverte et inactive pendant une durée donnée, puis les termine ; le paramètre timeout attend que le processus soit réellement terminé (ou que le délai expire).

Quand l'utiliser

Pour une intervention d'urgence lorsque des sessions oubliées détenant des verrous provoquent des blocages, ou retiennent l'horizon xmin et bloquent VACUUM.

Attention

pg_cancel_backend n'a aucun effet sur une session 'idle in transaction' (il n'y a pas de requête en cours à annuler) ; pg_terminate_backend est nécessaire et tous les changements non validés sont annulés. Restreignez le filtre par usename/application_name ; excluez les sessions de réplication, de sauvegarde et de supervision. Les pools de connexions rouvriront les connexions terminées. Si le délai expire, la fonction émet un avertissement et renvoie false. Comme correction permanente, définissez idle_in_transaction_session_timeout par rôle.