Skip site navigation (1) Skip section navigation (2)

Re: Autovacuum vs statement_timeout

From: Heikki Linnakangas <heikki(at)enterprisedb(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Alvaro Herrera <alvherre(at)commandprompt(dot)com>, pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: Autovacuum vs statement_timeout
Date: 2007-03-30 09:50:40
Message-ID: 460CDD70.2060508@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Tom Lane wrote:
> I seem to remember that we'd agreed that autovacuum should ignore any
> globally set statement_timeout, on the grounds that a poorly chosen
> setting could indefinitely prevent large tables from being vacuumed.
> But I do not see anything in autovacuum.c that resets the variable.
> Am I just being blind?  (Quite possible, as I'm tired and under the
> weather.)
> 
> The thing that brought this to mind was the idea that Mark
> Shuttleworth's open problem might be triggered in part by a statement
> timeout interrupting autovacuum at an inopportune point --- some logs
> he sent me offlist show that he is using statement_timeout ...

statement_timeout interrupts seem to go through the PG_CATCH-block and 
clean up the entry from the vacuum cycle array as they should. But a 
SIGINT leading to a "terminating connection due to administrator 
command" error does not.

After the recent change in CVS HEAD, CREATE DATABASE tries to 
kill(SIGINT) any autovacuum process in the template database. That seems 
very dangerous now, it could easily leave stale entries in the cycle id 
array. However, it doesn't explain the Mark Shuttleworth's problem 
because the 8.2 behavior is to throw an "source database is being 
accessed by other users" error instead of killing autovacuum. Maybe 
there's something else killing autovacuum processes?

I think we need to add the xid of the vacuum transaction in the vacuum 
cycle array, and clean up orphaned entries in _bt_start_vacuum. We're 
going to have a hard time plugging every leak one-by-one otherwise.

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

In response to

Responses

pgsql-hackers by date

Next:From: Simon RiggsDate: 2007-03-30 10:15:16
Subject: Re: [PATCHES] Full page writes improvement, code update
Previous:From: ITAGAKI TakahiroDate: 2007-03-30 08:59:56
Subject: Dead Space Map version 3 (simplified)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group