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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html