Cancel Vertica statement from JDBC

asher_1 · May 2016

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 · May 2016

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 · May 2016

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 · May 2016

Thank you all for your help.

Cancel Vertica statement from JDBC

Comments

Leave a Comment