Re: ERROR: could not access status of transaction 575

[ 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