C-NET :: PROJECTS

cat finding-and-killing-stuck-or-mistyped-postgresql-queries.md

Finding and killing stuck or mistyped postgresql queries

Posted
Updated
Filed under
Howto's · Reference · Software

I've found these useful during development:

Listing running queries:

SELECT pid, now() - pg_stat_activity.query_start AS duration, query FROM pg_stat_activity WHERE state = 'active' AND pid <> pg_backend_pid();
sql

Listing queries that have run for more than a full minute:

SELECT pid, now() - pg_stat_activity.query_start AS duration, query FROM pg_stat_activity WHERE state = 'active' AND pid <> pg_backend_pid() AND (now() - pg_stat_activity.query_start) > interval '1 minute';
sql

Try to cancel a single query:

SELECT pg_cancel_backend(<pid>);
sql

Force kill a single query:

SELECT pg_terminate_backend(<pid>);
sql

Force kill all queries that have been running for over 10 minutes:

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'active' AND pid <> pg_backend_pid() AND (now() - pg_stat_activity.query_start) > interval '10 minutes';
sql

Force kill all queries using the user_accounts table (or mentioning user_accounts at all, this isn't limited to table names):

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'active' AND pid <> pg_backend_pid() AND query LIKE '%user_accounts%';
sql

Hope someone finds this useful.

Comments