Re: FK disappeared in 8.3.3

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