Autovacuum keeps vacuuming a table disabled in pg_autovacuum - Mailing list pgsql-general

Hi all,

System: postgres 8.1.3p, not a released version but a CVS checkout
somewhere after 8.1.3 was released.

I have a table (quite big one, 2 columns, 2166381 DB relpages, 364447136
reltuples as reported by pg_class) which is mostly inserted into, very
rarely deleted from.

I also have a primary key on the combination of the 2 columns with the
corresponding PK index.

Initially I left this table out of the pg_autovacuum table, as like I
said it is never updated, rarely deleted from, and I have a weekly
complete DB vacuum scheduled which should theoretically be enough so
autovacuum will never touch it except maybe for analyzing, which is OK.

Recently however I discovered that my weekly DB wide vacuum started to
crash due to an error on vacuuming this table:

ERROR:  failed to re-find parent key in "pk_table"

This turned out to be a problem for autovacuum too, which spent lots of
time vacuuming this table, only to (presumably) crash and start it all
over next time (I can't explain otherwise the table bloating going on on
this DB box, which translates in slow operation).

I think I have seen some bug reports on this list with similar errors
and I guess it is fixed in 8.2, and I have planned a move to 8.2 anyway
next month, so I thought I just take this table out from autovacuum's
list.

So I did:

insert into pg_autovacuum (
    vacrelid, enabled,
    vac_base_thresh, vac_scale_factor,
    anl_base_thresh, anl_scale_factor,
    vac_cost_delay, vac_cost_limit
) values (
    (select oid from pg_class where relname='table_name'),
    false,
    5000, 0.2,
    10000, 0.2,
    -1, -1
);

Which indeed inserted one row in pg_autovacuum.

Trouble: the autovacuum daemon is still taking that table and vacuums
it... how do I know ? So:

select l.pid,c.relname from pg_locks l, pg_class c where l.pid not in
(select distinct procpid from pg_stat_activity) and l.relation=c.oid and
c.relkind='r';

  pid  |  relname
-------+------------
 16317 | table_name
(1 row)

postgres@dbname:~$ ps auxww|grep auto
postgres 16317  0.8  5.8 436008 240656 ?     D    13:26   0:55 postgres:
autovacuum process   dbname

So, the autovacuum's process is locking the table, which I interpret as
autvacuum vacuuming it.

Question: is the autovacuum daemon somehow caching it's table list ? Can
I reset somehow this ? I tried killing it's process, but it doesn't
work, next time it took the table again.

Thanks,
Csaba.