CANCEL QUERY

​

Considerations

• Schema changes are treated differently than other SQL queries. You can use SHOW JOBS to monitor the progress of schema changes and CANCEL JOB to cancel schema changes that are taking longer than expected.
• In rare cases where a query is close to completion when a cancellation request is issued, the query may run to completion.
• In addition to the CANCEL QUERY statement, CockroachDB also supports query cancellation by client drivers and ORMs using the PostgreSQL wire protocol (pgwire). This allows CockroachDB to stop executing queries that your application is no longer waiting for, thereby reducing load on the cluster. pgwire query cancellation differs from the CANCEL QUERY statement in the following ways:
  • It is how most client drivers and ORMS implement query cancellation. For example, it is used by PGJDBC to implement the setQueryTimeout method.
  • The cancellation request is sent over a different network connection than is used by SQL connections.
  • If there are too many unsuccessful cancellation attempts, CockroachDB will start rejecting pgwire cancellations.

​

Required privileges

​

Synopsis

​

Parameters

​

Response

• If the canceled query was a single, stand-alone statement, no further action is required by the client.
• If the canceled query was part of a larger, multi-statement transaction, the client should then issue a ROLLBACK statement.

​

Examples

​

Cancel a query via the query ID

> SHOW STATEMENTS;

              query_id             | node_id |            session_id            | user_name |                start                |                query                 | client_address  | application_name | distributed |   phase
-----------------------------------+---------+----------------------------------+-----------+-------------------------------------+--------------------------------------+-----------------+------------------+-------------+------------
  1673f58fca5301900000000000000001 |       1 | 1673f583067d51280000000000000001 | demo      | 2021-04-08 18:31:29.079614+00:00:00 | SELECT * FROM rides ORDER BY revenue | 127.0.0.1:55212 | $ cockroach demo |    true     | executing
  1673f590433eaa000000000000000001 |       1 | 1673f58a4ba3c8e80000000000000001 | demo      | 2021-04-08 18:31:31.108372+00:00:00 | SHOW CLUSTER STATEMENTS              | 127.0.0.1:55215 | $ cockroach sql  |    false    | executing
(2 rows)

> CANCEL QUERY '1673f590433eaa000000000000000001';

​

Cancel a query via a subquery

> CANCEL QUERY (WITH x AS (SHOW CLUSTER STATEMENTS) SELECT query_id FROM x
      WHERE client_address = '127.0.0.1:55212'
          AND user_name = 'demo'
          AND query = 'SELECT * FROM rides ORDER BY revenue');

CANCEL QUERIES 1

​

See also

• Manage Long-Running Queries
• SHOW STATEMENTS
• CANCEL SESSION
• SQL Statements