Re: ERROR: could not access status of transaction 575

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Brian Hurt <bhurt(at)janestcapital(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: ERROR: could not access status of transaction 575
Date: 2008-06-21 18:52:15
Message-ID: 22559.1214074335@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

[ sorry for not responding sooner ]

Brian Hurt <bhurt(at)janestcapital(dot)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

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Josh Tolley 2008-06-22 15:57:34 Re: Server Programming Interface - spi.exec() overheds issue
Previous Message Frank Bax 2008-06-21 03:23:19 Re: table oids and comments