How to cancel a long running query in PostgreSQL using JDBC?
In my project I have some legacy plugins. These plugins are used to read only queries from PostgreSQL. Most plugins work well, but the other part is very slow. This is the reason for the hang when receiving a connection to C3P0. It looks like changes to the connection timeout in the pool are not affected.
How can I set a timeout to effectively cancel these requests? I think that a server side timeout might be a good solution, but I don't want to set timeouts for all servers in the config. Also I do not want to refuse this connection and only require to cancel the current request.
Can you share your ideas on how to solve this problem?
source to share
If you set a dynamic configuration parameter statement_timeout
, this parameter only affects the current session: Executing a request longer than the specified time will be canceled without dropping the connection.
test=# set statement_timeout to 1000; -- 1s
SET
test=# select pg_sleep(5); -- 5s
ERROR: canceling statement due to statement timeout
test=# set statement_timeout to 0; -- turn off
SET
Update - A quick tour of Postgres process controls.
You can specify all running server processes:
test=# select pid, query_start, state, query from pg_stat_activity;
pid | query_start | state | query
------+----------------------------+--------+--------------------------------------------------------------
4004 | 2015-07-12 20:50:01.033+02 | active | select pid, query_start, state, query from pg_stat_activity;
For example, this query lists queries that have run time exceeding 5 minutes:
select *
from pg_stat_activity
where state = 'active'
and now() - query_start > '5m';
You can manually cancel a long-running request:
select pg_cancel_backend(pid);
or terminate the process:
select pg_terminate_backend(pid);
You can run a program that automatically cancels long-running requests. This can be a background process (daemon)
or it can be done with cron
.
This request will automatically cancel requests that are longer than 5 minutes:
select pg_cancel_backend(pid)
from pg_stat_activity
where state = 'active'
and now() - query_start > '5m';
Of course, such automated decisions must be implemented with great care. and with full awareness of the consequences.
More details in the documentation: pg_stat_activity , pg_cancel_backend () .
source to share
after some research and hints about postgres behavior from @klin I solved my problem. The c3p0 connection pool is used in legacy plugins. I found it possible to add custom extensions to the pooling behavior.
http://www.mchange.com/projects/c3p0/#user_extensions_to_configurations
Added initSql extension specified in c3p0 by default. Thus, we can supply the command
set statement_timeout to 1000;
in your config file and it will work without any changes to your source code. All you need after that is just to rebuild your plugin!
source to share