From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Organization: Dynacom Tankers Mgmt
To: pgsql-admin@postgresql.org
Subject: Re: FK disappeared in 8.3.3
Date: Fri, 26 Nov 2010 12:21:26 +0300
User-Agent: KMail/1.9.7
References: <201011221213.32035.achill@matrix.gatewaynet.com>
	<201011231014.57430.achill@matrix.gatewaynet.com>
	<8602.1290525749@sss.pgh.pa.us>
In-Reply-To: <8602.1290525749@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <201011261121.27414.achill@matrix.gatewaynet.com>

=CE=A3=CF=84=CE=B9=CF=82 Tuesday 23 November 2010 17:22:29 =CE=BF/=CE=B7 To=
m Lane =CE=AD=CE=B3=CF=81=CE=B1=CF=88=CE=B5:
> 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.
>=20

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 de=
finitions for this table.
Since the FK creation was inside the dump and not some human given extra co=
mmand,
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 CA=
SCADE" to not work
in the live database 7.4, leaving child rows with no corresponding parent r=
ows, 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 ....
=2E...
ALTER TABLE ONLY mailcrew_entity
    ADD CONSTRAINT mailcrew_entity_message FOREIGN KEY (msgno) REFERENCES m=
ailcrew_message(msgno) ON UPDATE CASCADE ON DELETE CASCADE;
^^^^^=20
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
> =20
>=20


=2D-=20
Achilleas Mantzios