Re: Autovacuum keeps vacuuming a table disabled in pg_autovacuum

Erik Jones wrote:
> On Jun 1, 2007, at 9:19 AM, Csaba Nagy wrote:
>
> >On Fri, 2007-06-01 at 15:58, Alvaro Herrera wrote:
> >>Try reindexing the table; that should make the problem go away.
> >>Also,
> >>update to the latest of the 8.1 branch ASAP.
> >
> >Reindexing won't work, it would mean hours of downtime. I plan to move
> >the DB to 8.2 via slony in ~2-3 weeks, that should take care of the
> >bloating too.

I meant that reindex would probably fix the "unable to find parent"
problem. I am not sure if the index is permanently corrupt, and so you
need a reindex anyway, or rather it's just that VACUUM is unable to work
with it and you just need to update past 8.1.6 in order for vacuum to be
able to work.

peeks the logs ... Ah, found it:

2006-11-01 16:50 tgl

* src/backend/access/nbtree/: README (1.8.6.1), nbtinsert.c
(1.127.2.2), nbtpage.c (1.88.2.2):

Fix "failed to re-find parent key" btree VACUUM failure by tweaking
_bt_pagedel to recover from the failure: just search the whole parent level
if searching to the right fails. This does nothing for the underlying problem
that index keys became out-of-order in the grandparent level. However, we
believe that there is no other consequence worse than slightly inefficient
searching, so this narrow patch seems like the safest solution for the back
branches.

You don't need to reindex, just update.

> How would reindexing a table imply hours of downtime?

Because reindexing takes an exclusive lock on the table.

> >Regarding upgrade, I have to use the CVS version because I have some
> >local patches to apply. Now what is more recommended, use the latest
> >version on the REL8_1_STABLE branch or stick with REL8_1_9 ? I checked
> >and there are quite a few changed files after REL8_1_9.

HEAD from the branch is certainly better as it contains some extra
fixes (particularly one for a bug introduced in 8.1.9 which may bite you
if you just pick that one ... see about bug #3116)

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.