Re: FK disappeared in 8.3.3 - Mailing list pgsql-admin

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