Menu
How do I cancel a long running postgres query via JDBC or Java?
The usecase would be that an user starts a query on a postgres database via a front end, but then he decides otherwise and wants to abort/cancel the currently running query.
215
In an interactive psql session, you can simply hit Ctrl+C to send a cancel request, and GUI clients usually have a button for that purpose. But it is also possible to cancel somebody else's query by calling the database function pg_cancel_backend() .
If you want to terminate all running queries, the following statement can be executed: SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE state = 'active' and pid <> pg_backend_pid(); The above statement will kill all active queries and should only be used in special situations.
According to the docs, pg_cancel_backend will stop the current query in that process, but pg_terminate_backend will finish the session in that proceses. A connected app can recover from a failed query, but to recover from a closed session it would need to open a new cursor.
Call java.sql.PreparedStatement.cancel() method. Check whether postgres JDBC driver supports this method. As far as I can see from the source code of the latest postgres JDBC driver it does stop the query.
61
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
