Thread: 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@cshl.org GMOD Coordinator (http://www.gmod.org/) 216-392-3087 Cold Spring Harbor Laboratory
Scott Cain <cain@cshl.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@cshl.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@cshl.org GMOD Coordinator (http://www.gmod.org/) 216-392-3087 Cold Spring Harbor Laboratory