Обсуждение: Risk of set system wise statement_timeout

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.

--
Best regards,


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

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

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

Alex Lai <mlai@sesda3.com> writes:
> 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.

autovacuum ignores any system-wide setting of statement_timeout
(and lock_timeout, where applicable) in all versions back to
at least 8.3.

That doesn't make a system-wide setting necessarily a good idea,
but this particular argument against it doesn't hold water.

            regards, tom lane