Re: Risk of set system wise statement_timeout

On 09/12/2013 10:09 AM, Giuseppe Broccolo wrote:
> 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.
>
Hi Giuseppe,

The function work great.  Thanks a lot!



--
Best regards,


Alex Lai
OMI SIPS DBA ADNET Systems , Inc.
mlai@sesda3.com