[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-admin
Subject:    Re: [ADMIN] FK disappeared in 8.3.3
From:       Achilleas Mantzios <achill () matrix ! gatewaynet ! com>
Date:       2010-11-26 9:21:26
Message-ID: 201011261121.27414.achill () matrix ! gatewaynet ! com
[Download RAW message or body]

Στις 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

-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin


[prev in list] [next in list] [prev in thread] [next in thread] 

Configure | About | News | Add a list | Sponsored by KoreLogic