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
