Re: Another small bug (pg_autovacuum) - Mailing list pgsql-hackers

On Thu, 2003-09-11 at 18:25, Tom Lane wrote:
> "Matthew T. O'Connor" <matthew@zeut.net> writes:
> > hrm.... OK.  Patch forthcoming....
> 
> BTW, I am not sure it is a good idea to suppress "redundant" vacuuming
> of shared tables in the first place.  The trouble with doing so is that
> if you only vacuum pg_shadow through template1, then only template1 will
> ever have up-to-date statistics about it.  That's not good.
> 
> You might be able to get away with doing actual vacuums only through
> template1, and doing just ANALYZEs every so often in other DBs.

I made a patch to fix this, but in testing it I noticed that the stats
system doesn't work on shared tables as I was expecting it too (as my
latest patch requires it too :-). It treats instances of shared tables
in separate databases as totally unique tables.  This makes it hard to
know how much activity has really gone on for a shared table.

Is the behavior of the following example expected / desired?

template1=# select ... (query details snipped)  relname   | relisshared | n_tup_ins | n_tup_upd | n_tup_del
-------------+-------------+-----------+-----------+-----------pg_database | t           |        28 |         0 |
 28
 
(1 row)

template1=# create database foo; drop database foo;
CREATE DATABASE
DROP DATABASE
template1=# select   relname   | relisshared | n_tup_ins | n_tup_upd | n_tup_del
-------------+-------------+-----------+-----------+-----------pg_database | t           |        29 |         0 |
 29
 
(1 row)

template1=# \c matthew
You are now connected to database "matthew".
matthew=# select   relname   | relisshared | n_tup_ins | n_tup_upd | n_tup_del
-------------+-------------+-----------+-----------+-----------pg_database | t           |         2 |         0 |
  2
 
(1 row)

matthew=# create database foo; drop database foo;
CREATE DATABASE
DROP DATABASE
matthew=# select   relname   | relisshared | n_tup_ins | n_tup_upd | n_tup_del
-------------+-------------+-----------+-----------+-----------pg_database | t           |         3 |         0 |
  3
 
(1 row)

matthew=# \c template1
You are now connected to database "template1".
template1=# select   relname   | relisshared | n_tup_ins | n_tup_upd | n_tup_del
-------------+-------------+-----------+-----------+-----------pg_database | t           |        29 |         0 |
 29
 
(1 row)