Re: Another small bug (pg_autovacuum)

Matthew T. O'Connor wrote:
> On Thu, 2003-09-11 at 08:12, Christopher Browne wrote:
> > Something I am feeling a little suspicious of is that I haven't seen,
> > in the logs, pg_autovacuum looking at pg_ tables.
> >
> > I know that if we don't periodically vacuum such system tables as
> > pg_class, pg_attribute, pg_statistic, and pg_type, they can get to
> > "pretty evil size."
> >
> > [Rummaging around...] These tables are being added for template1, but
> > apparently not for "main" databases. That looks like a bit of a fly
> > in the ointment...
>
> I designed it that way. It was my understanding that all of the system
> tables pg_class etc... are shared tables, available in all databases,
> but actually stored as only one central set of real tables. Hence
> vacuuming pg_class from template1 helps every database that accesses
> pg_class.
>
> Did I make a design error?

Oops, no. Only a few pg_* tables are "global". pg_class isn't. In
fact, I am not sure how someone tells which are global. A grep in
/src/include/catalog shows:

$ grep BKI_SHARED_RELATION *.h
pg_database.h:CATALOG(pg_database) BOOTSTRAP BKI_SHARED_RELATION
pg_group.h:CATALOG(pg_group) BOOTSTRAP BKI_SHARED_RELATION BKI_WITHOUT_OIDS
pg_shadow.h:CATALOG(pg_shadow) BOOTSTRAP BKI_SHARED_RELATION BKI_WITHOUT_OIDS

so those are the only shared ones. I found a query to do it too:

test=> select * from pg_class where relisshared = 't' and relkind = 'r';
relname | relnamespace | reltype | relowner | relam | relfilenode | relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules | relhassubclass | relacl
-------------+--------------+---------+----------+-------+-------------+----------+-----------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+------------------------------------
pg_shadow | 11 | 86 | 1 | 0 | 1260 | 1 | 1 | 16677 | 0 | t | t | r | 8 | 0 | 1 | 0 | 0 | 0 | f | f | f | f | {postgres=a*r*w*d*R*x*t*/postgres}
pg_database | 11 | 88 | 1 | 0 | 1262 | 1 | 2 | 16662 | 0 | t | t | r | 11 | 0 | 0 | 0 | 0 | 0 | t | f | f | f | {=r/postgres}
pg_group | 11 | 87 | 1 | 0 | 1261 | 0 | 0 | 16668 | 0 | t | t | r | 3 | 0 | 1 | 0 | 0 | 0 | f | f | f | f | {=r/postgres}
(3 rows)

so those are the only ones that should be template1-only. All other
pg_* tables should be vacuumed in individual database.

I will wait for a patch. :-)

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073