Re: FK disappeared in 8.3.3

Hello,
in one (out of our 60+) PostgreSQL systems which are deployed on tanker vessels at open sea, we had the following problem:
One (ON DELETE CASCADE) FK constraint which was supposed to be there was found to be (mysteriously) absent.
That caused am erroneous behaviour which manifested the problem so we thougth of doing a proper investigation on the whole
of the schema in this DB to see if everything is in place.
The schema itself is of the order of 25,000 lines long.
So we pg_dumped the schema on a known good 8.3.3 identical test database we have at the office (on shore),
and compared it against the suspicious schema on the problematic vessel.
The diff (without options) alone produced ~ 7500 lines of output. (quite an unpleasant result).
So i am now currently in the process of examining one by one the different portions of the two schema dumps.
Some of the strange things i notice are (diff -u)

1) (many cases like:)
CREATE TABLE machclasses (
- id integer DEFAULT nextval(('public.machclasses_id_seq'::text)::regclass) NOT NULL,
+ id integer NOT NULL,
name character varying(100) NOT NULL,
vslsecid integer NOT NULL
);
@@ -545,7 +537,7 @@
--
(later in the "bad" dump i get the "ALTER TABLE machclasses ALTER COLUMN id SET DEFAULT nextval('machclasses_id_seq'::regclass);" stmt)

2)
CREATE VIEW big_machclasses AS
- SELECT machclasses.id, machclasses.name, machclasses.vslsecid FROM machclasses WHERE (machclasses.id = ANY (ARR
AY[1, 2, 16, 74, 647, 3, 15, 76, 137, 267, 31, 32, 412, 33, 95, 10, 312, 11, 118, 70, 106, 22, 8, 21, 571, 80, 81, 1
20]));
+ SELECT machclasses.id, machclasses.name, machclasses.vslsecid FROM machclasses WHERE ((((((((((((((((((((((((((
((machclasses.id = 1) OR (machclasses.id = 2)) OR (machclasses.id = 16)) OR (machclasses.id = 74)) OR (machclasses.i
d = 647)) OR (machclasses.id = 3)) OR (machclasses.id = 15)) OR (machclasses.id = 76)) OR (machclasses.id = 137)) OR
(machclasses.id = 267)) OR (machclasses.id = 31)) OR (machclasses.id = 32)) OR (machclasses.id = 412)) OR (machclas
ses.id = 33)) OR (machclasses.id = 95)) OR (machclasses.id = 10)) OR (machclasses.id = 312)) OR (machclasses.id = 11
)) OR (machclasses.id = 118)) OR (machclasses.id = 70)) OR (machclasses.id = 106)) OR (machclasses.id = 22)) OR (mac
hclasses.id = 8)) OR (machclasses.id = 21)) OR (machclasses.id = 571)) OR (machclasses.id = 80)) OR (machclasses.id
= 81)) OR (machclasses.id = 120));

In the "good" dump the view definition uses the minimalistic ANY expression, while in the "bad" dump it is expressed verbosely.

Both databases were created with the same procedure using dumps from 7.4.2.

I must mention that the HW of the problematic vessel died some time around summer, and i had myself
personally onboard, pg_dump the old DB, and restore it to the new box.

I am puzzled about the differences in the schema, if any one has any ideas of why this might be happening, would be great.
That would help throw out of the equation those recurring cases and concentrate on any real and fatal differences in the schema.

One additional issue that is worsening our situation is that we dont have TCP/IP access to the vessel.
We only have term emulator (minicom) which dials up a remote mgetty
(which works only if the weather conditions are ok, and nothing else is broken: read if we are lucky)
--
Achilleas Mantzios

Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> wrote:

> One (ON DELETE CASCADE) FK constraint which was supposed to be
> there was found to be (mysteriously) absent.

Do you have old backups to see how long it has been gone?

> So we pg_dumped the schema on a known good 8.3.3 identical test
> database

That's an oxymoron. 8.3.3 has known bugs and security vulnerability
which have been fixed in maintenance releases which can be applied
without a dump and reload.

http://www.postgresql.org/support/versioning

The current 8.3 release is 8.3.12. For details of what's been
fixed, see this:

http://www.postgresql.org/docs/8.3/static/release.html

> and compared it against the suspicious schema on the problematic
> vessel. The diff (without options) alone produced ~ 7500 lines of
> output.

Were both databases at the same version number? Was the same
version of pg_dump used for both dumps? (Note: you can always dump
an older database with a newer version of pg_dump, but not vice
versa.)

> Both databases were created with the same procedure using dumps
> from 7.4.2.

The current version of 7.4 is 7.4.30!:

http://www.postgresql.org/docs/7.4/static/release.html

I'm not clear what you mean, though. Both databases are on 8.3.3?

> I must mention that the HW of the problematic vessel died some
> time around summer, and i had myself personally onboard, pg_dump
> the old DB, and restore it to the new box.

Did you get any errors when the dump was loaded? A damaged database
might have left orphaned rows which would have prevented creation of
the foreign key. Do you still have a dump file from that point?

> I am puzzled about the differences in the schema, if any one has
> any ideas of why this might be happening, would be great.

My first guess is that they were dumped by pg_dump executables from
different versions.

-Kevin

Kevin thanx,
I explicitly wrote that:
"One additional issue that is worsening our situation is that we dont have TCP/IP access to the vessel.
We only have term emulator (minicom) which dials up a remote mgetty
(which works only if the weather conditions are ok, and nothing else is broken: read if we are lucky)"
(which means that any upgrade ... aspirations are ... optimistic in the best case scenario.)
Now if what you asked is why didnt i upgrade the DB to whatever newest 8.3.* version existed
at the mid of July, the answer is that we have not done an upgrade plan yet, so yes we prefer to keep
the same version on all vessels, until we are capable (find resources/time) to design a proper upgrade
plan (i repeat we do not have TCP/IP access to the DBs)
(imagine having to upgrade 61 installations for which you dont have ssh/scp ;) sounds fun right? )

To your question about versions, yes both are 8.3.3, and the pg_dump used to diff the schemas was from 8.3.3

Στις Monday 22 November 2010 16:58:17 ο/η Kevin Grittner έγραψε:
> Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> wrote:
>
> > One (ON DELETE CASCADE) FK constraint which was supposed to be
> > there was found to be (mysteriously) absent.
>
> Do you have old backups to see how long it has been gone?
>
> > So we pg_dumped the schema on a known good 8.3.3 identical test
> > database
>
> That's an oxymoron. 8.3.3 has known bugs and security vulnerability
> which have been fixed in maintenance releases which can be applied
> without a dump and reload.
>
> http://www.postgresql.org/support/versioning
>
> The current 8.3 release is 8.3.12. For details of what's been
> fixed, see this:
>
> http://www.postgresql.org/docs/8.3/static/release.html
>
> > and compared it against the suspicious schema on the problematic
> > vessel. The diff (without options) alone produced ~ 7500 lines of
> > output.
>
> Were both databases at the same version number? Was the same
> version of pg_dump used for both dumps? (Note: you can always dump
> an older database with a newer version of pg_dump, but not vice
> versa.)
>
> > Both databases were created with the same procedure using dumps
> > from 7.4.2.
>
> The current version of 7.4 is 7.4.30!:
>
> http://www.postgresql.org/docs/7.4/static/release.html
>
> I'm not clear what you mean, though. Both databases are on 8.3.3?
>
> > I must mention that the HW of the problematic vessel died some
> > time around summer, and i had myself personally onboard, pg_dump
> > the old DB, and restore it to the new box.
>
> Did you get any errors when the dump was loaded? A damaged database
> might have left orphaned rows which would have prevented creation of
> the foreign key. Do you still have a dump file from that point?
>
> > I am puzzled about the differences in the schema, if any one has
> > any ideas of why this might be happening, would be great.
>
> My first guess is that they were dumped by pg_dump executables from
> different versions.
>
> -Kevin
>

--
Achilleas Mantzios

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> wrote:
>> I am puzzled about the differences in the schema, if any one has
>> any ideas of why this might be happening, would be great.

> My first guess is that they were dumped by pg_dump executables from
> different versions.

The differences do look suspiciously related to past cross-version
changes. I do not think they can be explained entirely by pg_dump
differences, though. In particular, there's no way that pg_dump
could have caused the reported change in a view definition from
ANY(ARRAY[...]) to a list of ORs. What seems most likely to me is
that that view was originally entered as
WHERE machclasses.id IN (1, 2, 16, ...)
Backends since 8.2 will translate that into ANY(ARRAY), which is how
it'll get reverse-listed by \d or pg_dump. Older versions produced
the list-of-ORs translation. I think the "bad" dump somehow traces
its lineage to an 8.1 or older installation. The other issues look
like they could possibly be explained by either forcing pg_dump to
dump from a newer server major version (which until recently you
could force with -i, but the resulting dump would likely have issues)
or trying to load a dump script into an older major server version
than it was designed for (and ignoring the ensuing error messages).

regards, tom lane

Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> wrote:

> (imagine having to upgrade 61 installations for which you dont
> have ssh/scp ;) sounds fun right? )

I don't have to imagine -- been there, done that. (Not with ships
but large geographical distances and needing to trudge through
secure facilities with big steel doors every so many feet.) It can
be time-consuming and tedious. But then, so can recovering a
corrupted database.

If I were in your shoes, I'd consider arranging to have a CD-ROM
mailed to someone on each ship at its next port, with a script which
would automatically perform the update. (Heavily tested in advance,
of course.) I strongly recommend that you read through the release
notes of 8.3.4 to 8.3.12 and consider whether any of the fixes
incorporated in those releases, or the aggregate of all of them,
might be worth the effort involved in testing such a script and
burning such a CD-ROM.

Of course, none of that helps with your current issue; I would just
hate to see you pop up here in a few months with bigger problems
than this if it can be avoided.

-Kevin

I must add (which i should have included in the original post),
that apart from the FK also 2 triggers on the same table were lost.
And in a 2nd table also two triggers were lost.
(all triggers for use with tsearch2)

Στις Monday 22 November 2010 18:11:12 γράψατε:
> The differences do look suspiciously related to past cross-version
> changes. I do not think they can be explained entirely by pg_dump
> differences, though. In particular, there's no way that pg_dump
> could have caused the reported change in a view definition from
> ANY(ARRAY[...]) to a list of ORs. What seems most likely to me is
> that that view was originally entered as
> WHERE machclasses.id IN (1, 2, 16, ...)
> Backends since 8.2 will translate that into ANY(ARRAY), which is how
> it'll get reverse-listed by \d or pg_dump. Older versions produced
> the list-of-ORs translation. I think the "bad" dump somehow traces
> its lineage to an 8.1 or older installation.

That's entirely correct. The view was entered using the IN notation.
This view was written and sent to the vessels at 2008-12-04.
Our test-vessel DB at shore in this date 2008-12-04 was already running 8.3.3.
At this time our problematic vessel was running 7.4.2. This vessel upgraded to 8.3.3
some time in January 2009.

The procedure we used to migrate from 7.4.* to 8.3.3 (which was far from straight forward) was a
heavily tested procedure which produced many good working DBs on many vessels.
(This FK and triggers are in place in some random DB's (with similar history) that we doublechecked)
This vessel has a bad history regarding its server's hardware.
In at least two occasions (the latest being around this summer), its hardware died.
I guess its motherboard's or CPU or memory's fault might have caused this lost FK contsraint and the 4 lost triggers.
I can only attribute this to bad hardware, or hardware/os mistreatment by the crew.

I have already created the FK constraint, the crew confirmed the app works as specified,
and i will recreate the 4 triggers.

My intention was to find any missing schema DDL's in the bad DB using a simple pg_dump.
This did not help, since there is a lot of noise in the diff (correct differences which does not
help to identify the bad ones). Anyway after some heuristic searches i think i have narrowed down
the missing (disappeared) DDL's.

Thanx.

> The other issues look
> like they could possibly be explained by either forcing pg_dump to
> dump from a newer server major version (which until recently you
> could force with -i, but the resulting dump would likely have issues)
> or trying to load a dump script into an older major server version
> than it was designed for (and ignoring the ensuing error messages).
>
> regards, tom lane
>

--
Achilleas Mantzios

Στις Monday 22 November 2010 18:45:28 γράψατε:
> Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> wrote:
>
> > (imagine having to upgrade 61 installations for which you dont
> > have ssh/scp ;) sounds fun right? )
>
> I don't have to imagine -- been there, done that. (Not with ships
> but large geographical distances and needing to trudge through
> secure facilities with big steel doors every so many feet.) It can
> be time-consuming and tedious. But then, so can recovering a
> corrupted database.

What you describe is an entirely different situation.

>
> If I were in your shoes, I'd consider arranging to have a CD-ROM
> mailed to someone on each ship at its next port, with a script which
> would automatically perform the update. (Heavily tested in advance,
> of course.)

This in unrealistic. Many things can go wrong, and in this case the vessel
stays offline, which is a very very bad thing.
(imagine to be hard to make a telephone call, and after hours of effort to hear a nice Indian
guy with whom you cant communicate due to different accents of English
since both we are non native english speakers)
btw, we use tsearch2, intarray, which require an upgrade plan by their own,
if you have ever done this.
In addition, i must say that we have about 290 tables in our schema.
Its not the kind of DB you would simply upgrade with a CDROM.
The huge improvements between 7.4.* and 8.3.* were about performance
and not stability. Lack of stability is not smth PostgreSQL is famous for.
So we perform the upgrade to 8.3 only when we send one of our people
onboard.

>
> Of course, none of that helps with your current issue; I would just
> hate to see you pop up here in a few months with bigger problems
> than this if it can be avoided.
>

We had bigger problems than this and still survived, dont worry ;)
PostgreSQL (be it 7.4.2, 7.4.19 or 8.3.3) in our 60+ vessels, is the last thing we have worried about
over the last 10 years. Generally PostgreSQL lasts even after hardware/OS have given up.

Dont get me wrong, i would love to see all our systems running the latest 9.0.1, and all the rest of software
(jboss, java, linux) to be on their latest releases.
However this is such a huge project to accomplish and our human resources are very limited.
(i am the DBA, linux, freebsd sysadm, java j2ee programmer, architect, etc.....)

Also, if there is no explicit upgrade plan (first in the office, second on vessels), then i prefer sticking
to 8.3.3 on the vessels, until we commit to do a serious upgrade.
I cannot start an initiative by myself, only to find later, that i just increased my own time spend on managing
who has what in the whole fleet.

But the real pain is the lack of TCP/IP and remote shell whenever we want it.

Anyways thanx for helping by sharing your thoughts.

> -Kevin
>

--
Achilleas Mantzios

Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> writes:
> At this time our problematic vessel was running 7.4.2. This vessel upgraded to 8.3.3
> some time in January 2009.

7.4!?

I can't shake the suspicion that some of your issues arose from trying
to load a later-version pg_dump output into an older-version server.
The discrepancies you're seeing are closely related to places where
we've changed pg_dump's output over the years, and not always in
backwards-compatible ways. The older server would throw syntax errors
on the newer commands, and if you didn't notice or follow up on that
during the reload, the outcome would be that the objects those commands
tried to create would just not be there. Which is what you've got.
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.

regards, tom lane

Στις Tuesday 23 November 2010 17:22:29 ο/η Tom Lane έγραψε:
> Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> writes:
> > At this time our problematic vessel was running 7.4.2. This vessel upgraded to 8.3.3
> > some time in January 2009.
>
> 7.4!?
>
> I can't shake the suspicion that some of your issues arose from trying
> to load a later-version pg_dump output into an older-version server.

The (same) migration procedure run fine for 30+ postgresql installations,
and it didn't involve nothing like loading anything later to anything older.
The migration procedure was like:

on the 7.4 machine:
Take a dump, copy it to the 8.3 machine as dynacom_DUMP_OUR_DUMP_FROM_7_4.sql
on the 8.3 machine:
as root:
dropdb dynacom
createdb dynacom
cd /usr/local/src/postgresql-8.3.3
cd contrib/tsearch2/
make uninstall
cd ../intarray/
make uninstall
as postgres:
create domain public.tsvector as pg_catalog.tsvector;
create domain public.gtsvector as pg_catalog.gtsvector;
create domain public.tsquery as pg_catalog.tsquery;
bash
echo "SET default_with_oids TO on;" > foo
mv dynacom_DUMP_OUR_DUMP_FROM_7_4.sql dynacom_DUMP_OUR_DUMP_FROM_7_4.sql_WITHOUT_OIDS
cat foo dynacom_DUMP_OUR_DUMP_FROM_7_4.sql_WITHOUT_OIDS > dynacom_DUMP_OUR_DUMP_FROM_7_4.sql
psql dynacom -f dynacom_DUMP_OUR_DUMP_FROM_7_4.sql >2see 2>&1
psql
DROP TABLE public.pg_ts_cfg;
DROP TABLE public.pg_ts_cfgmap;
DROP TABLE public.pg_ts_dict ;
DROP TABLE public.pg_ts_parser ;
DROP TYPE public.query_int ;
DROP TYPE public.statinfo ;
DROP TYPE public.tokenout ;
DROP TYPE public.tokentype ;
DROP FUNCTION public.ts_debug(text) ;
DROP TYPE public.tsdebug ;
ALTER TABLE vesselhelp ALTER idxfti TYPE pg_catalog.tsvector;
ALTER TABLE vmanews ALTER idxfti TYPE pg_catalog.tsvector;
ALTER TABLE mail_entity ALTER subjectidxfti TYPE pg_catalog.tsvector;
ALTER TABLE mail_entity ALTER textidxfti TYPE pg_catalog.tsvector;
ALTER TABLE mail_fromfield ALTER idxfti TYPE pg_catalog.tsvector;
ALTER TABLE mail_tofield ALTER idxfti TYPE pg_catalog.tsvector;
ALTER TABLE mailcrew_entity ALTER subjectidxfti TYPE pg_catalog.tsvector;
ALTER TABLE mailcrew_entity ALTER textidxfti TYPE pg_catalog.tsvector;
ALTER TABLE mailcrew_fromfield ALTER idxfti TYPE pg_catalog.tsvector;
ALTER TABLE mailcrew_tofield ALTER idxfti TYPE pg_catalog.tsvector;
DROP DOMAIN public.gtsvector ;
DROP DOMAIN public.tsquery ;
DROP DOMAIN public.tsvector ;
as root:
cd /usr/local/src/postgresql-8.3.3/contrib/intarray
make install
cd /usr/local/src/postgresql-8.3.3/contrib/tsearch2
make install
as postgres
psql
load 'tsearch2';
\i /usr/local/src/postgresql-8.3.3/contrib/tsearch2/tsearch2.sql
load '_int';
\i /usr/local/src/postgresql-8.3.3/contrib/intarray/_int.sql

CREATE TRIGGER mail_entity_subjtsvectorupdate
BEFORE INSERT OR UPDATE ON mail_entity
FOR EACH ROW
EXECUTE PROCEDURE tsearch2('subjectidxfti', 'entsubject');

CREATE TRIGGER mail_entity_texttsvectorupdate
BEFORE INSERT OR UPDATE ON mail_entity
FOR EACH ROW
EXECUTE PROCEDURE tsearch2('textidxfti', 'enttext');

CREATE TRIGGER mail_fromfield_tsvectorupdate
BEFORE INSERT OR UPDATE ON mail_fromfield
FOR EACH ROW
EXECUTE PROCEDURE tsearch2('idxfti', 'fromname', 'fromaddr');

CREATE TRIGGER mail_tofield_tsvectorupdate
BEFORE INSERT OR UPDATE ON mail_tofield
FOR EACH ROW
EXECUTE PROCEDURE tsearch2('idxfti', 'toname', 'toaddr');

CREATE TRIGGER mailcrew_entity_subjtsvectorupdate
BEFORE INSERT OR UPDATE ON mailcrew_entity
FOR EACH ROW
EXECUTE PROCEDURE tsearch2('subjectidxfti', 'entsubject');

CREATE TRIGGER mailcrew_entity_texttsvectorupdate
BEFORE INSERT OR UPDATE ON mailcrew_entity
FOR EACH ROW
EXECUTE PROCEDURE tsearch2('textidxfti', 'enttext');

CREATE TRIGGER mailcrew_fromfield_tsvectorupdate
BEFORE INSERT OR UPDATE ON mailcrew_fromfield
FOR EACH ROW
EXECUTE PROCEDURE tsearch2('idxfti', 'fromname', 'fromaddr');

CREATE TRIGGER mailcrew_tofield_tsvectorupdate
BEFORE INSERT OR UPDATE ON mailcrew_tofield
FOR EACH ROW
EXECUTE PROCEDURE tsearch2('idxfti', 'toname', 'toaddr');

CREATE TRIGGER vesselhelp_tsvectorupdate
BEFORE INSERT OR UPDATE ON vesselhelp
FOR EACH ROW
EXECUTE PROCEDURE tsvector_update_trigger('idxfti', 'pg_catalog.english', 'content');

CREATE TRIGGER vmanews_tsvectorupdate
BEFORE INSERT OR UPDATE ON vmanews
FOR EACH ROW
EXECUTE PROCEDURE tsvector_update_trigger('idxfti', 'pg_catalog.english', 'header', 'content');

CREATE INDEX paintgentypes_parents ON paintgentypes USING gin (parents gin__int_ops);
CREATE INDEX machdefs_parents_gistsmall ON machdefs USING gin (parents gin__int_ops);

as root:
grep COPY dynacom_DUMP_OUR_DUMP_FROM_7_4.sql | grep "FROM stdin" | awk '{print "select count(*) from "$2";"}' > checkcounts.sql
psql -a -f checkcounts.sql dynacom > checkcounts.out.8.3
psql -h old-7.4-machine -a -f checkcounts.sql dynacom > checkcounts.out.7.4
diff -u checkcounts.out.7.4 checkcounts.out.8.3

> The discrepancies you're seeing are closely related to places where
> we've changed pg_dump's output over the years, and not always in
> backwards-compatible ways. The older server would throw syntax errors
> on the newer commands, and if you didn't notice or follow up on that
> during the reload, the outcome would be that the objects those commands
> tried to create would just not be there. Which is what you've got.

As i wrote this scenario should not happen, what was supposed to happen was load an old dump to the new server,
but on the other hand i was not personally present when the migration took place.
(i wasn't present in any migration to be more precise)
Maybe the person who did this, screwed up with the commands, or was in a bad
mental condition when he did this, i cannot know that.

OTOH, on a handy 7.4 pg_dump --schema-only that i just created, i see the "lost" FK given as:
ALTER TABLE ONLY mailcrew_entity
ADD CONSTRAINT mailcrew_entity_message FOREIGN KEY (msgno) REFERENCES mailcrew_message(msgno) ON UPDATE CASCADE ON DELETE CASCADE;

and i cannot think of any possible ways that human fatigue or other human error could possibly result in the failure to correctly
execute the above command (hmm... provided it was already there in 7.4... which takes us to another dimension of guessing ;)

> 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.

e.g. in the FK above the commands are the same in dumps created in 7.4 and in 8.3,
even in the extremely silly situation that the person doing the job was playing games
way off our written instructions, i cannot think of a way that it would fail, unless there were
indeed records in 7.4 that didn't satisfy the FK for some reason, even if it was there?

>
> regards, tom lane
>
>

--
Achilleas Mantzios

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