Long Running SQL Commands and Queries

Audience: Data Users

Content Summary: This page details how to terminate long-running or hanging SQL commands or queries.

Query Overview

Below is a SQL Query, which contains instructions to help terminate long-running SQL commands or queries.

Note: This query excludes START_REPLICATION SLOT %.

Instructions

Use the inner select to determine if you have hanging queries, and replace pg_cancel_backend with pg_terminate_backend if they do not actually cancel. You can also change the interval if needed.

SELECT pg_cancel_backend(pid)
FROM (
SELECT
pid, now() - pg_stat_activity.query_start AS duration, query, state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '1 day'
AND query NOT LIKE 'START_REPLICATION SLOT %'
) long_pids;