The SQL execution completes but does not return, causing the client to remain blocked indefinitely.

Question

​Environment:​​ Vertica 11, using JDBC.
​​Issue:​​ When executing a long-running SQL query (~2 hours) via JDBC, the .execute() method remains blocked even after the query completes successfully. The connection eventually times out due to server-side idle timeout, leading to an exception.
​​Check Done:​​ Verified in query_requests that the query status is marked as completed.

​​Ask:​​
Hi everyone,
In Vertica 11, when running a slow SQL query (takes ~2 hours) via JDBC, the .execute() method keeps blocking indefinitely even after the query finishes successfully. The connection is only released after an idle timeout triggers a server-side disconnect, resulting in an exception.
I confirmed in query_requests that the query status is COMPLETED.
Has anyone encountered this? How can I resolve the blocking issue post-query completion?

Thanks in advance for any suggestions!

marcothesane · Answer

I presume the JDBC client connects from remote.
From experience, the network connection between the remote client - JDBC, ODBC, or even vsql - is usually the culprit. Often - a connection problem lasting just a second or slightly more, can be the cause that the client process is not able any more to message the client that the request has completed - and the client just hangs - or the client can't acknowledge the message from the server - and the server process just hangs.

Best practice is to run long runners from, ideally, a Vertica node, or a server in the same subnet as the cluster. Best as a vsql process running a SQL script with nohup and output redirection to log files - which you can monitor live with a different process going:
tail -f logfile.log

Sign up

Please log in or register:

Welcome to the Rocket Forum!

Please log in or register: