long-running-transactions

SELECT
  COUNT(*) as transactions,
  MAX(EXTRACT(EPOCH FROM clock_timestamp() - pg_stat_activity.xact_start)) AS oldest_timestamp_seconds
FROM pg_catalog.pg_stat_activity
WHERE state IS DISTINCT FROM 'idle'
AND query NOT LIKE 'autovacuum:%'
AND pg_stat_activity.xact_start IS NOT NULL
AND pid <> pg_backend_pid();

pg_long_running_transactions 3
pg_long_running_transactions_oldest_timestamp_seconds 1247.83

# Transactions running longer than 5 minutes
pg_long_running_transactions_oldest_timestamp_seconds > 300

# Multiple long-running transactions
pg_long_running_transactions > 5

SELECT pid, usename, application_name, state, query, 
       now() - xact_start as duration
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND state != 'idle'
ORDER BY xact_start;

SELECT * FROM pg_stat_activity 
WHERE state = 'idle in transaction' 
AND xact_start < now() - interval '5 minutes';

SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE pid = <pid>;