0

My serverless redshift has thousands of running queries. I think it is stuck. I'm not sure how to cancel all queries and start fresh. Is there a way to do it?

Ori N
  • 555
  • 10
  • 22

2 Answers2

6

Since STV_RECENTS is not available in serverless endpoint, you can get all the running queries with

SELECT * FROM SYS_QUERY_HISTORY WHERE status = 'running'

, the session_id is the PID

Then you can cancel them with

CANCEL <session_id>
jrodriguez
  • 61
  • 1
  • 3
  • Thank you very much for this correct answer friend! You just saved us a lot of work re-doing the logic in our applications. – Jaysin K Nov 03 '22 at 13:59
-1

From CANCEL - Amazon Redshift:

Cancels a database query that is currently running.

CANCEL process_id [ 'message' ]

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

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:

select pid, starttime, duration,
trim(user_name) as user,
trim (query) as querytxt
from stv_recents
where status = 'Running';

See also:

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
  • Thanks. I want to cancel all running queries. Do you suggest writing a script which iterates the process ids? Is there another way to do it? – Ori N Aug 01 '22 at 18:57
  • You can't run stv_recents on a serverless endpoint. You can run sys_query_history, but that doesn't give you the pid that you need to run pg_terminate_backend(pid). – Matt Connolly Aug 23 '22 at 10:54