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(at)sesda3(dot)com