Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

CANCEL

Cancels a database query that is currently running.

The CANCEL command requires the process ID of the running query and displays a confirmation message to verify that the query was cancelled.

Syntax

Copy
CANCEL process_id [ 'message' ]

Parameters

process_id

Process ID corresponding to the query that you want to cancel.

'message'

An optional confirmation message that displays when the query cancellation completes. If you do not specify a message, Amazon Redshift displays the default message as verification. You must enclose the message in single quotes.

Usage Notes

You can't cancel a query by specifying a query ID; you must specify the query's process ID (PID). You can only cancel queries currently being run by your user. Superusers can cancel all queries.

If queries in multiple sessions hold locks on the same table, you can use the PG_TERMINATE_BACKEND function to terminate one of the sessions, which forces any currently running transactions in the terminated session to release all locks and roll back the transaction. Query the STV_LOCKS system table to view currently held locks.

Following certain internal events, Amazon Redshift might restart an active session and assign a new PID. If the PID has changed, you might receive the following error message:

Copy
Session <PID> does not exist. The session PID might have changed. Check the stl_restarted_sessions system table for details.

To find the new PID, query the STL_RESTARTED_SESSIONS system table and filter on the oldpid column.

Copy
select oldpid, newpid from stl_restarted_sessions where oldpid = 1234;

Examples

To cancel a currently running query, first retrieve the process ID for the query that you want to cancel. To determine the process IDs for all currently running queries, type the following command:

Copy
select pid, starttime, duration, trim(user_name) as user, trim (query) as querytxt from stv_recents where status = 'Running'; pid | starttime | duration | user | querytxt -----+----------------------------+----------+----------+----------------- 802 | 2008-10-14 09:19:03.550885 | 132 | dwuser | select venuename from venue where venuestate='FL', where venuecity not in ('Miami' , 'Orlando'); 834 | 2008-10-14 08:33:49.473585 | 1250414 | dwuser | select * from listing; 964 | 2008-10-14 08:30:43.290527 | 326179 | dwuser | select sellerid from sales where qtysold in (8, 10);

Check the query text to determine which process id (PID) corresponds to the query that you want to cancel.

Type the following command to use PID 802 to cancel that query:

Copy
cancel 802;

The session where the query was running displays the following message:

Copy
ERROR: Query (168) cancelled on user's request

where 168 is the query ID (not the process ID used to cancel the query).

Alternatively, you can specify a custom confirmation message to display instead of the default message. To specify a custom message, include your message in quotes at the end of the CANCEL command:

Copy
cancel 802 'Long-running query';

The session where the query was running displays the following message:

Copy
ERROR: Long-running query