Re: Another small bug (pg_autovacuum)

matthew@zeut.net ("Matthew T. O'Connor") writes:
> 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?

[rummaging around...  Where's a suitable system?  There it is...]

database3=# vacuum verbose pg_class;
NOTICE:  --Relation pg_class--
NOTICE:  Index pg_class_oid_index: Pages 11399; Tuples 165: Deleted 1408.       CPU 2.00s/0.41u sec elapsed 2.51 sec.
NOTICE:  Index pg_class_relname_index: Pages 30604; Tuples 171: Deleted 1408.       CPU 5.81s/1.07u sec elapsed 7.07
sec.
NOTICE:  Removed 1408 tuples in 24 pages.       CPU 0.01s/0.02u sec elapsed 0.20 sec.
NOTICE:  Pages 360: Changed 1, Empty 0; Tup 165: Vac 1408, Keep 0, UnUsed 21110.       Total CPU 7.82s/1.50u sec
elapsed9.79 sec.
 
VACUUM
database3=# \c database1
You are now connected to database database1
database1=# vacuum verbose pg_class;
NOTICE:  --Relation pg_class--
NOTICE:  Pages 22768: Changed 0, Empty 0; Tup 158: Vac 0, Keep 0, UnUsed 1434149.       Total CPU 1.35s/0.97u sec
elapsed2.38 sec.
 
VACUUM
database1=# \c template1
You are now connected to database template1.
template1=# vacuum verbose pg_class;
NOTICE:  --Relation pg_class--
NOTICE:  Pages 2: Changed 0, Empty 0; Tup 101: Vac 0, Keep 0, UnUsed 27.       Total CPU 0.00s/0.00u sec elapsed 0.00
sec.
VACUUM

No, they aren't shared.  (And why yes, some of those databases could
indeed use more regular vacuuming. :-))

Note, that was a 7.2.4 database, so pg_autovacuum won't work there
yet.  C'est la vie.

At any rate, template1.pg_class seems pretty distinct from
database1.pg_class which is distinct from database3.pg_class.  I think
there's a bit of an error there.
-- 
(reverse (concatenate 'string "ofni.smrytrebil" "@" "enworbbc"))
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 646 3304 x124 (land)