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@xxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
