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.broccolo@2ndQuadrant.it | www.2ndQuadrant.it