From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Organization: Dynacom Tankers Mgmt
To: pgsql-admin@postgresql.org
Subject: Re: FK disappeared in 8.3.3
Date: Tue, 23 Nov 2010 18:54:21 +0300
User-Agent: KMail/1.9.7
Cc: Tom Lane <tgl@sss.pgh.pa.us>
References: <201011221213.32035.achill@matrix.gatewaynet.com>
	<201011231014.57430.achill@matrix.gatewaynet.com>
	<8602.1290525749@sss.pgh.pa.us>
In-Reply-To: <8602.1290525749@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <201011231754.22204.achill@matrix.gatewaynet.com>

=CE=A3=CF=84=CE=B9=CF=82 Tuesday 23 November 2010 17:22:29 =CE=BF/=CE=B7 To=
m Lane =CE=AD=CE=B3=CF=81=CE=B1=CF=88=CE=B5:
> Achilleas Mantzios <achill@matrix.gatewaynet.com> writes:
> > At this time our problematic vessel was running 7.4.2. This vessel upgr=
aded to 8.3.3
> > some time in January 2009.=20
>=20
> 7.4!?
>=20
> 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.s=
ql
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_WI=
THOUT_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 '{pr=
int "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 m=
ailcrew_message(msgno) ON UPDATE CASCADE ON DELETE CASCADE;

and i cannot think of any possible ways that human fatigue or other human e=
rror could possibly result in the failure to correctly=20
execute the above command (hmm... provided it was already there in 7.4... w=
hich 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 pla=
ying games
way off our written instructions, i cannot think of a way that it would fai=
l, unless there were
indeed records in 7.4 that didn't satisfy the FK for some reason, even if i=
t was there?

>=20
> 			regards, tom lane
> =20
>=20


=2D-=20
Achilleas Mantzios