fairly serious bug with pg_autovacuum in pg7.4 - Mailing list pgsql-hackers

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.