From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-admin@postgresql.org
Subject: Re: FK disappeared in 8.3.3
Date: Fri, 26 Nov 2010 12:21:26 +0300
Message-ID: <201011261121.27414.achill@matrix.gatewaynet.com> (raw)
In-Reply-To: <8602.1290525749@sss.pgh.pa.us>
References: <201011221213.32035.achill@matrix.gatewaynet.com>
<201011231014.57430.achill@matrix.gatewaynet.com>
<8602.1290525749@sss.pgh.pa.us>
Στις 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: achill@matrix.gatewaynet.com
Subject: Re: FK disappeared in 8.3.3
In-Reply-To: <201011261121.27414.achill@matrix.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox