Hello,
I've run across a pretty serious problem with pg_autovacuum. pg_autovacuum looses track of any table that's ever been truncated (possibly other situations too). When i truncate a table it gets a new relfilenode in pg_class. This is a problem because pg_autovacuum assumes pg_class.relfilenode will join to pg_stats_all_tables.relid. pg_stats_all_tables.relid is actallly the oid from pg_class, not the relfilenode. These two values start out equal so pg_autovacuum works initially, but it fails later on because of this incorrect assumption.
here is one query pg_autovacuum uses (from pg_autovacuum.h) to get tables that breaks.
select a.relfilenode,a.relname,a.relnamespace,a.relpages,a.relisshared,a.reltup les,b.schemaname,b
.n_tup_ins,b.n_tup_upd,b.n_tup_del from pg_class a, pg_stat_all_tables b where a.relfilenode=b.relid and a.relkind = 'r'
here's a little test case you can use to see what happens:
basement=# create table test_table ( id int4 );
CREATE TABLE
basement=# select relname, relfilenode from pg_class where relkind = 'r' and relname = 'test_table';
relname | relfilenode
------------+-------------
test_table | 28814151
(1 row)
basement=# select relid,relname from pg_stat_all_tables where relname = 'test_table';
relid | relname
----------+------------
28814151 | test_table
(1 row)
basement=# select a.relfilenode,a.relname,a.relnamespace,a.relpages,a.relisshared,a.reltup les,b.schemaname,
basement-# b.n_tup_ins,b.n_tup_upd,b.n_tup_del from pg_class a, pg_stat_all_tables b
basement-# where a.relfilenode=b.relid and a.relkind = 'r' and a.relname = 'test_table';
relfilenode | relname | relnamespace | relpages | relisshared | reltuples | schemaname | n_tup_ins | n_tup_upd | n_tup_del
-------------+------------+--------------+----------+------------- +-----------+------------+-----------+-----------+-----------
28814151 | test_table | 2200 | 10 | f | 1000 | public | 0 | 0 | 0
(1 row)
basement=#
basement=# truncate table test_table;
TRUNCATE TABLE
basement=# select relname, relfilenode from pg_class where relkind = 'r' and relname = 'test_table';
relname | relfilenode
------------+-------------
test_table | 28814153
(1 row)
basement=# select relid,relname from pg_stat_all_tables where relname = 'test_table';
relid | relname
----------+------------
28814151 | test_table
(1 row)
basement=# select a.relfilenode,a.relname,a.relnamespace,a.relpages,a.relisshared,a.reltup les,b.schemaname,
basement-# b.n_tup_ins,b.n_tup_upd,b.n_tup_del from pg_class a, pg_stat_all_tables b
basement-# where a.relfilenode=b.relid and a.relkind = 'r' and a.relname = 'test_table';
relfilenode | relname | relnamespace | relpages | relisshared | reltuples | schemaname | n_tup_ins | n_tup_upd | n_tup_del
-------------+---------+--------------+----------+------------- +-----------+------------+-----------+-----------+-----------
(0 rows)
basement=# drop table test_table; DROP TABLE basement=#
PS: i'm running pg-7.4 and pg_autovacuum from contrib.
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
