Re: Another small bug (pg_autovacuum)

On Thu, 2003-09-11 at 18:25, Tom Lane wrote:
> "Matthew T. O'Connor" <matthew(at)zeut(dot)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)