Search Postgresql Archives

Re: Risk of set system wise statement_timeout

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux