Re: Risk of set system wise statement_timeout

Il 11/09/2013 22:02, Alex Lai ha scritto:
> I have been reading few posted comment about the risk for autovacuum
> for older postgres liek version 8.
> I am currently running 9.2.4. We have a need to terminate any query
> running longer than 2 hours. Most of our query should finish within
> 15 minutes. We don't have very large amount of changes in the system
> and we run autovacuum daily. Running the larger table for autovacuum
> should be fast. Under my situation, setting statement_timeout =
> 7200000 which is 2 hours seems very low risk trigger fail to
> autovacuum. Any one have any idea not to do it or any workaround to
> decrease the risk of fail autovacuum
Setting statement_timeout in postgresql.conf is not recommended for many
reasons. You are interested to terminate just your query. I suggest to
use pg_stat_activity table to search query running longer than 2 hours,
and them to terminate them with pg_cancel_backend() function. I just did
a simple test where I defined a function which retrieves the pid of the
query process, and then terminate it if its running time is longer than
2 hours:

CREATE OR REPLACE FUNCTION cancel_after_2hours() RETURNS VOID AS $$
DECLARE
r RECORD;
BEGIN
FOR r IN SELECT * FROM pg_stat_activity WHERE query_start <
CURRENT_TIMESTAMP - interval '120 minutes'
LOOP
SELECT pg_cancel_backend(r.pid);
END LOOP;
END;
$$ LANGUAGE 'plpgsql';

then add a line like the following in your cron

0 * * * * psql <databasenamehere> -c "SELECT cancel_after_2hours();"

to be sure that it will be executed in automatic way.

Hope it can help,

Giuseppe.

--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
giuseppe(dot)broccolo(at)2ndQuadrant(dot)it | www.2ndQuadrant.it