Re: Another small bug (pg_autovacuum)

matthew(at)zeut(dot)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 elapsed 9.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 elapsed 2.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)