CANCEL QUERY

Gracefully stops the execution of a running query.

Syntax#

Flow chart showing the syntax of the CANCEL QUERY keyword

Description#

The CANCEL QUERY command sets a flag that is periodically checked by the running target query. Cancelling depends on how often the flag is checked. It may not be immediate.

The query_id is the unique non-negative identification number of a running query in query registry.

CANCEL QUERY returns an error if:

  1. The given query_id is negative
  2. The query can't be found in registry

A query_id is found via the query_activity() meta-function.

Examples#

Consider we have two open tabs of the QuestDB Web Console.

If we execute the following command in the first tab:

CREATE TABLE test AS (SELECT x FROM long_sequence(1000000000));

We can then check that the query is running in the second tab with the query_activity() meta-function:

SELECT * FROM query_activity();
query_idworker_idworker_poolusernamequery_startstate_changestatequery
291sharedjoe2024-01-09T10:51:05.878627Z2024-01-09T10:51:05.878627ZactiveCREATE TABLE test_tab AS (SELECT x FROM long_sequence(10000000000));
3021sharedjoe2024-01-09T10:51:10.661032Z2024-01-09T10:51:10.661032ZactiveSELECT * FROM query_activity();

We see that the two latest queries have query_id's of 29 and 30, respectively.

Want to cancel it?

There are two methods:

CANCEL QUERY 29;

Or:

SELECT cancel_query(29)

After execution, the query then gets interrupted and returns a cancelled by user error in the first tab where the query was launched.

The cancel_query() function may cancel multiple queries at the same time or cancel without the need to lookup a specific query_id. You can do so by chaining with a LIKE operator:

SELECT cancel_query(query_id)
FROM query_activity()
WHERE query LIKE 'CREATE TABLE test_tab%'

This expression returns true if query was found in the registry and if the cancellation was set. Otherwise, it returns false.


Something missing? Page not helpful? Please suggest an edit on GitHub.