Thread: ERROR: could not access status of transaction 575

Hello all-

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.  Doing some googling turns up a bug with 7.4 (this is 8.11), and this:
http://archives.postgresql.org/pgsql-bugs/2007-07/msg00152.php

I did find this:
http://archives.postgresql.org/pgsql-bugs/2007-07/msg00124.php

Which looks like the same problem.  However, reindexing doesn't seem to help me.

Please, oh please, someone tell me that it's a hardware problem- this particular database is a development database running on dodgy second-hand hardware with no important data on it.  Losing the whole database would rank no higher than annoying, and this would mean that production, running on shiny new hardware, isn't likely to be affected.

More information:

<>template0=# select version();
                                                  version
------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.1.11 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070626 (Red Hat 4.1.2-14)
(1 row)

template0=#

-bash-3.1$ cat /proc/version
Linux version 2.6.18-53.1.4.el5 (mockbuild@builder6.centos.org) (gcc version 4.1.2 20070626 (Red Hat 4.1.2-14)) #1 SMP Fri Nov 30 00:45:55 EST 2007
-bash-3.1$

Any and all help would be appreciated.  Thanks.

Brian

Brian Hurt wrote:

Hello all-

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=#

More information: I did (as postgres):

dd bs=49152 count=1 if=/dev/zero of=/database/postgres/pg_clog/0000

to create the clog file (49152 is the size of /database/postgres/pg_clog/0001), then fixed the permissions (chmod 0600 /database/postgres/pg_clog/0000).  This seems to have fixed the problem.

The partition is on a Areca raid control, and some playing with the areca cli didn't turn up any HD errors.  We're running bonnie++ on the partition to see if it detects any errors.

Brian

[ 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