FAQ
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

Search Discussions

  • Brian Hurt at Jun 17, 2008 at 4:47 pm

    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
  • Tom Lane at Jun 21, 2008 at 6:52 pm
    [ 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJun 17, '08 at 4:11p
activeJun 21, '08 at 6:52p
posts3
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Brian Hurt: 2 posts Tom Lane: 1 post

People

Translate

site design / logo © 2022 Grokbase