Stop a Stubborn Query that Won’t Cancel

Sometimes a running SQL statement hangs and cannot be stopped using the CLOSE_SESSION or INTERRUPT_STATEMENT functions. To stop it, after grabbing the culprit’s SESSION_ID, TRANSACTION_ID and STATEMENT_ID from the QUERY_REQUESTS system table, you can move it to a resource pool that has no resources!

Example:

dbadmin=> CREATE RESOURCE POOL cancel MEMORYSIZE '0K' MAXMEMORYSIZE '0K' RUNTIMECAP '0';
CREATE RESOURCE POOL

dbadmin=> SELECT MOVE_STATEMENT_TO_RESOURCE_POOL ('v_test_db_node0001-12429:0x61983', 45035996273952846, 12, 'cancel');
                                                            MOVE_STATEMENT_TO_RESOURCE_POOL
 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
  MOV_REPLAN: Target pool does not have sufficient resources. See v_monitor.resource_pool_move for details. Vertica will attempt to replan the statement on target pool.
(1 row)

After the move and subsequent re-plan, the query will “usually” fail!

Helpful Links:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/ResourceManager/ManuallyMovingQueries.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/QUERY_REQUESTS.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/VerticaFunctions/CLOSE_SESSION.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/VerticaFunctions/INTERRUPT_STATEMENT.htm

Have fun!