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