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

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 *.hpg_database.h:CATALOG(pg_database) BOOTSTRAP
BKI_SHARED_RELATIONpg_group.h:CATALOG(pg_group)BOOTSTRAP BKI_SHARED_RELATION
BKI_WITHOUT_OIDSpg_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'
andrelkind = '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@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073