When encountering error 25001: Active SQL Transaction in Postgres, the user should immediately execute the following actions for investigation:
SELECT * FROM pg_stat_activity WHERE state IN ('active', 'idle in transaction');
SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid = [PID of the transaction to terminate];
[PID of the transaction to terminate] with the actual PID of the problematic transaction.SELECT bl.pid AS blocked_pid, a.usename AS blocked_user, kl.pid AS blocking_pid, ka.usename AS blocking_user, a.query AS blocked_query
FROM pg_catalog.pg_locks bl
JOIN pg_catalog.pg_stat_activity a ON a.pid = bl.pid
JOIN pg_catalog.pg_locks kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid
WHERE NOT bl.granted;
These steps should help in identifying the cause of the “Active SQL Transaction” error and potentially resolving it by terminating any problematic transactions or addressing locking issues.
Let Dr. Droid create custom investigation plans for your infrastructure.
Start Free POC (15-min setup) →
