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 Monitor > Active Queries 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:
  1. 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;
    (1 row)
  2. Run the CANCEL command and supply the query_id:
    premdb=# cancel 22669;
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;
premdb=# insert into newmatchstats select * from newmatchstats;
ERROR:  canceling statement due to statement timeout
premdb=# alter role bobr set statement_timeout=1;
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