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
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
