Re: blocking automatic vacuum

Hi Tom,

till now i found more blocking vacuum processes in other databases as well.
we migrated postgres from 8.3 to 8.4 in april.

on most databases we have slony running - they have a good potential for
getting to that high number of transactions. only that they do their own
vacuum on the most frequented tables.
what exactly happens during anti-wraparond vacuum in terms of locking and
for how long?

best regards,
Uwe

On 22 June 2010 16:48, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Uwe Bartels <uwe(dot)bartels(at)gmail(dot)com> writes:
> > last ween i've seen a blocking "automatic vacuum".
> > as i understood, this is not supposed to happen. in the past i saw vacuum
> > processes disappear, in case of the need of a lock.
>
> What that sounds like is it was an anti-wraparound vacuum. Autovacuum
> won't cancel those to avoid delaying other processes.
>
> Now, RowExclusiveLock doesn't conflict with an autovacuum, so there is
> more going on here than you've showed us. The other obvious question is
> how did you get to the point where an anti-wraparound vacuum became
> necessary.
>
> I speculate that you are doing something that does conflict with vacuum
> (ie, SHARE UPDATE EXCLUSIVE lock or higher), and are doing it so often
> that regular autovacuum runs on the table never manage to complete.
> This is very bad, because you're going to have a serious bloat problem
> if autovac keeps getting canceled. You need to look at what sort of DDL
> you are repetitively executing on that table, and find a way to do it a
> lot less often.
>
> regards, tom lane
>