Lange 'idle in transaction'-Sessions kontrolliert in großer Zahl beenden

Befehl

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

Was es tut

Listet zuerst Sessions, die eine Transaktion eine bestimmte Zeit offen und inaktiv gelassen haben, und beendet sie dann; der Parameter timeout wartet, bis der Prozess tatsächlich beendet ist (oder das Timeout abläuft).

Wann einsetzen

Für einen Notfalleingriff, wenn vergessene Sessions mit Sperren Blockierungen verursachen oder den xmin-Horizont zurückhalten und VACUUM ausbremsen.

Vorsicht

pg_cancel_backend bewirkt bei einer 'idle in transaction'-Session nichts (es gibt keine laufende Abfrage zum Abbrechen); pg_terminate_backend ist nötig, und alle nicht committeten Änderungen werden zurückgerollt. Grenze den Filter über usename/application_name ein; schließe Replikations-, Backup- und Monitoring-Sessions aus. Connection Pools öffnen beendete Verbindungen erneut. Läuft das Timeout ab, gibt die Funktion eine Warnung aus und liefert false. Als dauerhafte Lösung idle_in_transaction_session_timeout je Rolle setzen.