Re: FK disappeared in 8.3.3

Στις Tuesday 23 November 2010 17:22:29 ο/η Tom Lane έγραψε:
> I don't have enough information to show the exact chain of events, but I
> think that something like this is a lot more probable than a random
> hardware failure that just happened to produce these particular results.
>

I just got replies from 59 of our postgresql sites, from which more than 30 had gone through the same
migration procedure, and *all* (but the problematic one) had the correct definitions for this table.
Since the FK creation was inside the dump and not some human given extra command,
i cannot think of anything else than the error (FK contraint been gone) was there before the initial
migration to 8.3 (some time in 2009). So the error must have occured while in 7.4.
And i can only think of hardware error that cause the initial "ON DELETE CASCADE" to not work
in the live database 7.4, leaving child rows with no corresponding parent rows, and thus
causing the restore during the initial upgrade to 8.3 to fail.
i think of a possible scenario such as:
1) the ON DELETE CASCADE mechanism stops to work in 7.4
2) during the initial migration to 8.3 the statements in the 7.4 are like:

CREATE TABLE mailcrew_entity ...
COPY mailcrew_entity ....
....
ALTER TABLE ONLY mailcrew_entity
ADD CONSTRAINT mailcrew_entity_message FOREIGN KEY (msgno) REFERENCES mailcrew_message(msgno) ON UPDATE CASCADE ON DELETE CASCADE;
^^^^^
this stmt fails since the FK constraint cannot be met.

3) database works in this manner...
4) newer hardware fails as well
5) migration to new hardware (this july 2010), (i was onboard but i didn't witness any error cause there was nothing to complaint about in the log.)

I think that could explain how the FK constraint was lost.
So, i guess there is only 7.4/old hardware or a combination of 7.4 and the old hardware to blame.

> regards, tom lane
>
>

--
Achilleas Mantzios