Hi All,
One of our databases suffered a problem yesterday during a normal
update, something we have been doing for years. Near the end of the
process a foreign key constraint is rebuilt on a table containing
several hundred million rows. Rebuilding the constraint failed with
the following message:
ERROR: could not access status of transaction 4294918145
DETAIL: Could not open file "pg_clog/0FFF": No such file or directory.
Here's the table and constraint definitions:
CREATE TABLE point_history (
point_id integer NOT NULL,
value real NOT NULL,
status integer NOT NULL,
"timestamp" timestamp without time zone NOT NULL
);
ALTER TABLE point_history ADD CONSTRAINT point_history_pkey PRIMARY
KEY (point_id, "timestamp");
ALTER TABLE point_history ADD CONSTRAINT "$1" FOREIGN KEY (point_id)
REFERENCES point(id);
I read about this and and created the pg_clog/0FFF file, filling it
with 256K of zeroes and then vacuumed the database. Then I tried
rebuilding the constraint and received a foreign key violation:
DETAIL: Key (point_id)=(2) is not present in table "point".
The crappy thing about this is that there was no record in the
point_history table with point_id = 2:
db=> select * from point_history where point_id = 2;
point_id | value | status | timestamp
----------+-------+--------+-----------
(0 rows)
I scratched my head for a while and decided to reload the database
from a backup, which I'm still working on now. I'm wondering if
anyone has any thoughts or ideas about this? I found references to
similar problems but they were all for older versions of PostgreSQL.
When the problem occurred we were running 8.3.6 and are now running
8.3.7.
Tom
--
Tom Duffey <tduf...@techbydesign.com>
Technology by Design :: http://techbydesign.com/
p: 414.431.0800
