Stop a Stubborn Query that Won’t Cancel

Posted August 19, 2019 by Jim Knicely, Vertica Principal Solution Architect

Business card that says Expert Tips,
Sometimes a running SQL statement hangs and cannot be stopped using the CLOSE_SESSION or INTERRUPT_STATEMENT function. To stop it, first grab the culprit’s SESSION_ID, TRANSACTION_ID, and STATEMENT_ID from the QUERY_REQUESTS system table, then 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/latest/HTML/Content/Authoring/AdministratorsGuide/ResourceManager/ManuallyMovingQueries.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/QUERY_REQUESTS.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/VerticaFunctions/CLOSE_SESSION.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/VerticaFunctions/INTERRUPT_STATEMENT.htm

Have fun!