Re: ERROR: could not access status of transaction 210

Hello,

After dropping a database, recreating it and trying to add a table to
it, I am getting this message:

NOTICE: CREATE TABLE will create implicit sequence "tableinfo_tableinfo_id_seq" for "serial" column "tableinfo.tableinfo_id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tableinfo_pkey" for table "tableinfo"
NOTICE: CREATE TABLE / UNIQUE will create implicit index "tableinfo_c1" for table "tableinfo"
ERROR: could not access status of transaction 210
DETAIL: could not open file "/opt/postgres/data/pg_clog/0000": No such file or directory
ERROR: relation "tableinfo" does not exist

There is a file call 0001 in pg_clog, but not 0000. This is a
relatively new install of PostgreSQL 7.4.6 built from source on a
similarly new install of Fedora Core 3. It is entirely possible that
the database was not shut down properly a few times as I have been
working out the kinks in this system.

So, what can I do to get rid of this? I already dropped the database
(which I can do successfully) and created an empty database in its
place, but I can't add any tables to it. Also, when I do a '\dt' in
that empty database, I get a similar message:

chado=# \dt
ERROR: could not access status of transaction 45
DETAIL: could not open file "/opt/postgres/data/pg_clog/0000": No such
file or directory

Thanks much,
Scott

--
------------------------------------------------------------------------
Scott Cain, Ph. D. cain(at)cshl(dot)org
GMOD Coordinator (http://www.gmod.org/) 216-392-3087
Cold Spring Harbor Laboratory

Scott Cain <cain(at)cshl(dot)org> writes:
> After dropping a database, recreating it and trying to add a table to
> it, I am getting this message:

> ERROR: could not access status of transaction 210
> DETAIL: could not open file "/opt/postgres/data/pg_clog/0000": No such file or directory

> There is a file call 0001 in pg_clog, but not 0000. This is a
> relatively new install of PostgreSQL 7.4.6 built from source on a
> similarly new install of Fedora Core 3.

Hmm, are you sure it's 7.4.6 and has been 7.4.6 for the whole existence
of the data directory? This looks like a hint-bit-drop problem, which
I thought we had fixed the last of as of 7.4.6.

> So, what can I do to get rid of this? I already dropped the database
> (which I can do successfully) and created an empty database in its
> place, but I can't add any tables to it. Also, when I do a '\dt' in
> that empty database, I get a similar message:

Apparently you've got hint-bit corruption in template1. I'd recommend a
fresh initdb. If you're desperate to avoid that because you have other
databases that aren't broken, you could reconstruct template1 from
template0 (see techdocs.postgresql.org for the procedural details).

regards, tom lane

On Mon, 2004-11-29 at 12:57 -0500, Tom Lane wrote:
> Scott Cain <cain(at)cshl(dot)org> writes:
> > After dropping a database, recreating it and trying to add a table to
> > it, I am getting this message:
>
> > ERROR: could not access status of transaction 210
> > DETAIL: could not open file "/opt/postgres/data/pg_clog/0000": No such file or directory
>
> > There is a file call 0001 in pg_clog, but not 0000. This is a
> > relatively new install of PostgreSQL 7.4.6 built from source on a
> > similarly new install of Fedora Core 3.
>
> Hmm, are you sure it's 7.4.6 and has been 7.4.6 for the whole existence
> of the data directory? This looks like a hint-bit-drop problem, which
> I thought we had fixed the last of as of 7.4.6.

Yes--when I installed 7.4.6, I did a fresh install of Pg
at /opt/postgres, whereas the previous install was at /home/postgres.
>
> > So, what can I do to get rid of this? I already dropped the database
> > (which I can do successfully) and created an empty database in its
> > place, but I can't add any tables to it. Also, when I do a '\dt' in
> > that empty database, I get a similar message:
>
> Apparently you've got hint-bit corruption in template1. I'd recommend a
> fresh initdb. If you're desperate to avoid that because you have other
> databases that aren't broken, you could reconstruct template1 from
> template0 (see techdocs.postgresql.org for the procedural details).

I'll do a fresh initdb--this is a development machine (thankfully), so
the databases on it are just for testing. If you don't hear back--all
went well.

Thanks,
Scott

>
> regards, tom lane
>
--
------------------------------------------------------------------------
Scott Cain, Ph. D. cain(at)cshl(dot)org
GMOD Coordinator (http://www.gmod.org/) 216-392-3087
Cold Spring Harbor Laboratory