Fwd: vacuuming template0 gave ERROR

I have the discussion below in which it is advised that template0 should
not be touched, however, my production v8.1.4 template0 and 1 database XID
"age", determined with the following query, changes constantly:-

select datname, age(datfrozenxid), datfrozenxid, current_date, current_time
FROM pg_database

If template0 shouldn't be touched (and I don't know of deliberate access to
it), why is this changing please? template0 currently returns -1797761636 (a
minus number), and template1 returns 1436634067 and is growing constantly.
Should I be doing a vacuum full on them both, and is this activity normal
please? I'm concerned the XID / wraparound age will expire eventually.

Adrian
---------- Forwarded message ----------
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Date: Dec 27, 2005 5:02 AM
Subject: Re: [ADMIN] vacuuming template0 gave ERROR
To: Gourish Singbal <gourish(at)gmail(dot)com>
Cc: "pgsql-admin(at)postgresql(dot)org " <pgsql-admin(at)postgresql(dot)org>

Gourish Singbal <gourish(at)gmail(dot)com> writes:
> Got the following ERROR when i was vacuuming the template0 database.

Why were you doing that in the first place? template0 shouldn't ever
be touched.

> postgresql server version is 7.4.5

The underlying cause is likely related to this 7.4.6 bug fix:

2004-10-13 18:22 tgl

* contrib/pgstattuple/pgstattuple.c,
src/backend/access/heap/heapam.c,
src/backend/utils/adt/ri_triggers.c (REL7_4_STABLE): Repair
possible failure to update hint bits back to disk, per
http://archives.postgresql.org/pgsql-hackers/2004-10/msg00464.php.
I plan a more permanent fix in HEAD, but for the back branches it
seems best to just touch the places that actually have a problem.

> INFO: vacuuming "pg_catalog.pg_statistic"
> ERROR: could not access status of transaction 1107341112
> DETAIL: could not open file "/home/postgres/data/pg_clog/0420": No such
> file or directory

Fortunately for you, pg_statistic doesn't contain any irreplaceable
data. So you could get out of this via

TRUNCATE pg_statistic;
VACUUM ANALYZE; -- rebuild contents of pg_statistic
VACUUM FREEZE; -- make sure template0 needs no further vacuuming

Then reset template0's datallowconn to false, and get rid of that code
to override it. And then update to a more recent release ;-)

(I don't recall exactly what rules 7.4 uses, but likely you'll find that
you need to run a standalone backend with -O switch to perform
TRUNCATE on a system catalog.)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

------------------------------
At the Datamail Group we value team commitment, respect, achievement,
customer focus, and courage.

This email with any attachments is confidential and may be subject to legal
privilege. If it is not intended for you please advise by reply immediately,
destroy it and do not copy, disclose or use it in any way.
------------------------------

------------------------------
This email has been scanned by the DMZGlobal Business Quality Electronic
Messaging Suite.
Please see http://www.dmzglobal.com/services/bqem.htm for details.
------------------------------