Stop a Stubborn Query that Won’t Cancel

Jim_Knicely

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!

josefp

Hi Jim,
I tried this solution but it doesn't work. The session cannot be moved to the resource pool as basically it do not have available resource to allocate.
Yes I got below notification, and I attempted several tries checking if the move to be successful but no luck.

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.

Jim_Knicely

What version of Vertica are you running and what is the value in the CURRENT_STATEMENT column in the SESSION table?
Also, does that session have any currently execting queries show up in QUERY_REUESTS (i.e. WHERE SESSION_ID = '...' AND IS_EXECUTING)