Display Canceled Queries: Quick Tip

Posted November 8, 2018 by Jim Knicely, Big Data Solutions Architect, Vertica

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/latest/HTML/index.htm#Authoring/Glossary/DataCollector.htm

Have fun!