Canceling Queries
You can cancel a running query by using the CANCEL command. This command
takes the query_id
of an active query as its only argument. You can use the feature in the SMC or query the sys.query
view to get the ID
of the query you want to cancel.
To cancel an active query:
- Get the
query_id
of the active query that you want to cancel. For example, if only one query is running:premdb=# select query_id from sys.query; query_id ---------- 22669 (1 row)
- Run the
CANCEL
command and supply thequery_id
:premdb=# cancel 22669; CANCEL
Note: If you are canceling a query from within your own session, you can use
Ctrl-C
. The CANCEL command is really intended for cases where another
session or application runs a query that needs to be cancelled by an administrator. Aborting a Query with WLM Rules
You can cancel queries conditionally that are in certain states by writing rules with an Abort Query action. See Creating WLM Rules.
Delayed Cancellation with statement_timeout
If you want to cancel queries after they have been running for a specific period of time,
you can use the
statement_timeout
parameter. You can apply this parameter
to a specific query, a session, or all of the sessions for a specific user. For example:
premdb=# set statement_timeout=1000;
SET
premdb=# insert into newmatchstats select * from newmatchstats;
ERROR: canceling statement due to statement timeout
premdb=# alter role bobr set statement_timeout=1;
ALTER ROLE
premdb=# \c premdb bobr
Password for user bobr:
You are now connected to database "premdb" as user "bobr".
premdb=> select * from season, match where season.seasonid=match.seasonid and season_name='1999-2000';
ERROR: canceling statement due to statement timeout