We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Stop a Stubborn Query that Won’t Cancel — Vertica Forum

Stop a Stubborn Query that Won’t Cancel

Jim_KnicelyJim_Knicely - Select Field - Administrator
edited August 2019 in Tips from the Team

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!

Tagged:

Answers

  • josefpjosefp Vertica Customer ✭

    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_KnicelyJim_Knicely - Select Field - Administrator

    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)

Sign In or Register to comment.