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

I must add (which i should have included in the original post),=20
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)

=CE=A3=CF=84=CE=B9=CF=82 Monday 22 November 2010 18:11:12 =CE=B3=CF=81=CE=
=AC=CF=88=CE=B1=CF=84=CE=B5:
> 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=
=2E3.
At this time our problematic vessel was running 7.4.2. This vessel upgraded=
 to 8.3.3
some time in January 2009.=20

The procedure we used to migrate from 7.4.* to 8.3.3 (which was far from st=
raight forward) was a=20
heavily tested procedure which produced many good working DBs on many vesse=
ls.
(This FK and triggers are in place in some random DB's (with similar histor=
y) 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 hardwa=
re died.
I guess its motherboard's or CPU or memory's fault might have caused this l=
ost FK contsraint and the 4 lost triggers.
I can only attribute this to bad hardware, or hardware/os mistreatment by t=
he 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 sim=
ple pg_dump.
This did not help, since there is a lot of noise in the diff (correct diffe=
rences which does not
help to identify the bad ones). Anyway after some heuristic searches i thin=
k i have narrowed down
the missing (disappeared) DDL's.

Thanx.

> The other issues look=20
> 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).
>=20
> 			regards, tom lane
>=20


=2D-=20
Achilleas Mantzios