Re: autovacuum process blocks without reporting a deadlock

Thomas Chille wrote:
> On Nov 27, 2007 3:14 PM, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
> hat are the column headings? I find this difficult to read.
> >
> > Please post the whole of pg_locks. I may be missing something but I
> > think we're missing part of the picture here. Autovacuum does not seem
> > to be locking on anything.
>
> Unfortunately i logged the pg_locks-state not well formated. i added
> now the heading manually. maybe it is better or i have to wait till
> tomorrow morning.
>
> but what i think i can see, is that the process with pid 9317 is
> holding a ShareUpdateExclusiveLock

You missed that 9317 is also holding an ExclusiveLock. What are the
vacuum_cost_delay and autovacuum_vacuum_cost_delay setting? I think
what's happening here is that you're being bitten by the bug that made
autovac sleep because of vacuum_delay, with the exclusive lock held
trying to truncate the table.

The solution is to upgrade to 8.1.10.

Also I just noticed you're on 8.1.4. This is a bad idea because of
another ancient autovacuum bug. As soon as you upgrade, allow
connections to template0 (update pg_database, set datallowconn), connect
to it and issue VACUUM FREEZE. Then disallow connections to it again.

--
Alvaro Herrera http://www.PlanetPostgreSQL.org/
"La experiencia nos dice que el hombre peló millones de veces las patatas,
pero era forzoso admitir la posibilidad de que en un caso entre millones,
las patatas pelarían al hombre" (Ijon Tichy)