Cancel Vertica statement from JDBC

asher_1

hello,

i would like to have the ability to cancel long running statements upon demand.

i managed to kill statements through vsql cli with the following query:

select INTERRUPT_STATEMENT('session_id', stmt_number);

(according to https://community.dev.hpe.com/t5/Vertica-Forum/How-to-end-an-endlessly-running-query-in-Vertica/td-p/207229)

 

now, i would like to do it through JDBC. it seems that preparedStatement.cancel() does not kill the statement. in addition, connection.close() or connection.abort(executorService) do not work either.

 

my next thought is to run the INTERRUPT_STATEMENT from JDBC. my questions are:

1. is there a built in way to kill statements from JDBC?

2. how can i get session id and statement id from JDBC?

 

thanks,

Asher

eli_revach

Hi ,

 

1. is there a built in way to kill statements from JDBC? 

<Eli> Cancel should do the job , however you mention its not working .

 

2. how can i get session id and statement id from JDBC?

<Eli> No native API , but you can do something like this for specific running query ->

select transaction_id,statement_id from query_requests where request like '%Part Of Your query Text that you want to kill %' and is_executing is true 

 

 

I hope you will find it useful

 

Thanks 

Chris_S

Hi asher_1, 

 

You are right in finding out that the current JDBC driver does not support canceling queries through .cancel(). 

 

I think the best alternative is to run the interrupt_statement request through JDBC. To get the required IDs, you can run 

 

SELECT session_id, transaction_id, statement_id FROM current_session;

in that same JDBC connction that will execute your long-running query. 

I found that the next PreparedStatement you create using that same Connection object will have a statement_id of 3 higher than what is returned initially. The session_id will stay the same. 

 

If you application is unable to keep track of session_ids and statement_ids at execution-time of the queries, you can run a separate command to retrieve the IDs. 

SELECT session_id, statement_id from sessions where current_statement='...'

asher_1

Thank you all for your help.