Display Canceled Queries

Jim_Knicely · November 2018

We can cancel a long running query in vsql by typing CTRL+C. The data collector table DC_CANCELS tracks queries that were stopped in this manner.

Example:

dbadmin=> SELECT table_name, component, description
dbadmin->   FROM data_collector
dbadmin->   WHERE component = 'Cancels';
table_name | component |   description
------------+-----------+------------------
dc_cancels | Cancels   | Canceled queries
(1 row)

dbadmin=> SELECT * FROM dc_cancels;
time | node_name | session_id | user_id | txn_id | user_name | active_query | reason
------+-----------+------------+---------+--------+-----------+--------------+--------
(0 rows)

Now I will run a simple SQL statement and hit CTRL+C after waiting for 2 seconds:

dbadmin=> SELECT SLEEP(30);
Cancel request sent
SLEEP
-------
    28
(1 row)

dbadmin=> \x
Expanded display is on.

dbadmin=> SELECT * FROM dc_cancels;
-[ RECORD 1 ]+----------------------------------
time         | 2018-11-08 04:30:22.246344-05
node_name    | v_test_db_node0001
session_id   | v_test_db_node0001-156234:0x7e7d2
user_id      | 45035996273704962
txn_id       | 45035996274463388
user_name    | dbadmin
active_query | SELECT SLEEP(30);
reason       | Session canceled on the initiator

Helpful Link:
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/Glossary/DataCollector.htm

Have fun!