To terminate an SQL statement or session to restore the system status, use the pg_stat_activity
system view to obtain the status of the current query:
select current_query,procpid from pg_stat_activity ;
current_query | procpid
-----------------------------------------------------+---------
select current_query,procpid from pg_stat_activity; | 32584
SELECT xxx | 32238
Note
current_query
indicates the ongoing query and procpid
indicates the ID of the background process.
Terminate the execution of a SELECT statement from a query other than the current query by executing:
SELECT pg_cancel_backend(pg_stat_activity.procpid)
FROM pg_stat_activity
WHERE procpid <> pg_backend_pid() and current_query like 'SELECT%';-- Note that SELECT is entered in all uppercase.
pg_cancel_backend
-------------------
t
(1 rows)
Or
SELECT pg_terminate_backend(pg_stat_activity.procpid)
FROM pg_stat_activity
WHERE procpid <> pg_backend_pid() and current_query like 'SELECT%'; -- Note that SELECT is entered in all uppercase.
pg_terminate_backend
-------------------
t
(1 rows)
Note The
cancel
and terminate
operations are only supported for queries initiated by the current user or by a user
who has equal or fewer permissions than the current user. If the system displays "ERROR: must be superuser or rds_superuser to signal other server processes"
, queries initiated by other users are running in the background. This does not interrupt
the execution of the cancel
or terminate
operations.