Re: [NOVICE] 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

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice