| 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 <tduffey@xxxxxxxxxxxxxxxx> Technology by Design :: http://techbydesign.com/ p: 414.431.0800 |
