[ sorry for not responding sooner ]
Brian Hurt <bhurt@janestcapital.com> writes:
> I'm getting an error on a Postgres database when I access a table:
>> template0=# select * from pg_language;
>> ERROR: could not access status of transaction 575
>> DETAIL: could not open file "pg_clog/0000": No such file or directory
>> template0=#
> Unfortunately, as you can tell, this is causing problems, especially
> with creating new databases. I'm wondering if anyone knows what the
> problem could be.
The fact that you're connected to template0, and that it seems to
contain some post-initdb changes, is raising a lot of alarm bells in
my head. Exactly what did you do to this database just after initdb?
The behavior you're seeing indicates a lost hint bit --- that is,
there's a row inserted (or perhaps deleted) by transaction 575,
for which the XMIN_COMMITTED/INVALID (or XMAX_COMMITTED/INVALID)
hint bit never got set before the section of pg_clog containing
XID 575's commit status was thrown away. There are a couple of ways
that could happen in 8.1, but I think the one that bit you is that
8.1 assumes that databases with datallowconn = false do not contain
any unhinted XIDs and so can be ignored while determining where to
truncate pg_clog. So I'm suspicious that you did this:
set template0's datallowconn to true
fool around in template0
set template0's datallowconn to false
without cleaning up after yourself by doing a VACUUM (or better
VACUUM FREEZE) on template0 after mucking with it.
FWIW, 8.2 and up use a different approach that is less vulnerable
to DBA cock-ups ...
regards, tom lane