Re: should I worry?

I wrote:
> Hmm, this is messier than I thought. What evidently has happened is
> that at one time or another, one of the two tables involved in an FK
> relationship has been dropped and re-created. If you'd had proper
> FK constraints the constraints would have gone away cleanly, but with
> these old trigger definitions there was no mechanism to make that
> happen, and so the triggers on the other table remained in place.

That seems to have happened several times, in fact. After tweaking
ConvertTriggerToFK() to be more verbose and to insist on finding all
three triggers, I get this:

NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY perso_competences(pcomp_perso_cod) REFERENCES perso(perso_cod)
DETAIL: Found referencing table's trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY perso_position(ppos_perso_cod) REFERENCES perso(perso_cod)
DETAIL: Found referencing table's trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY objet_position(pobj_obj_cod) REFERENCES objets(obj_cod)
DETAIL: Found referencing table's trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY objet_position(pobj_obj_cod) REFERENCES objets(obj_cod)
DETAIL: Found referenced table's DELETE trigger.
NOTICE: converting trigger group into constraint "<unnamed>" FOREIGN KEY objet_position(pobj_obj_cod) REFERENCES objets(obj_cod)
DETAIL: Found referenced table's UPDATE trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY perso_objets(perobj_perso_cod) REFERENCES perso(perso_cod)
DETAIL: Found referencing table's trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY perso_objets(perobj_obj_cod) REFERENCES objets(obj_cod)
DETAIL: Found referencing table's trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY perso_objets(perobj_obj_cod) REFERENCES objets(obj_cod)
DETAIL: Found referenced table's DELETE trigger.
NOTICE: converting trigger group into constraint "<unnamed>" FOREIGN KEY perso_objets(perobj_obj_cod) REFERENCES objets(obj_cod)
DETAIL: Found referenced table's UPDATE trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY messages_dest(dmsg_msg_cod) REFERENCES messages(msg_cod)
DETAIL: Found referencing table's trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY messages_dest(dmsg_msg_cod) REFERENCES messages(msg_cod)
DETAIL: Found referenced table's DELETE trigger.
NOTICE: converting trigger group into constraint "<unnamed>" FOREIGN KEY messages_dest(dmsg_msg_cod) REFERENCES messages(msg_cod)
DETAIL: Found referenced table's UPDATE trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY messages_dest(dmsg_perso_cod) REFERENCES perso(perso_cod)
DETAIL: Found referencing table's trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY messages_exp(emsg_msg_cod) REFERENCES messages(msg_cod)
DETAIL: Found referencing table's trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY messages_exp(emsg_msg_cod) REFERENCES messages(msg_cod)
DETAIL: Found referenced table's DELETE trigger.
NOTICE: converting trigger group into constraint "<unnamed>" FOREIGN KEY messages_exp(emsg_msg_cod) REFERENCES messages(msg_cod)
DETAIL: Found referenced table's UPDATE trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY messages_exp(emsg_perso_cod) REFERENCES perso(perso_cod)
DETAIL: Found referencing table's trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY perso_competences(pcomp_perso_cod) REFERENCES perso(perso_cod)
DETAIL: Found referencing table's trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY perso_competences(pcomp_perso_cod) REFERENCES perso(perso_cod)
DETAIL: Found referenced table's DELETE trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY perso_position(ppos_pos_cod) REFERENCES positions(pos_cod)
DETAIL: Found referenced table's DELETE trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY perso_position(ppos_pos_cod) REFERENCES positions(pos_cod)
DETAIL: Found referenced table's UPDATE trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY objet_position(pobj_pos_cod) REFERENCES positions(pos_cod)
DETAIL: Found referenced table's DELETE trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY objet_position(pobj_pos_cod) REFERENCES positions(pos_cod)
DETAIL: Found referenced table's UPDATE trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY or_position(por_pos_cod) REFERENCES positions(pos_cod)
DETAIL: Found referenced table's DELETE trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY or_position(por_pos_cod) REFERENCES positions(pos_cod)
DETAIL: Found referenced table's UPDATE trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY ligne_evt(levt_tevt_cod) REFERENCES type_evt(tevt_cod)
DETAIL: Found referenced table's DELETE trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY ligne_evt(levt_tevt_cod) REFERENCES type_evt(tevt_cod)
DETAIL: Found referenced table's UPDATE trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY perso(perso_race_cod) REFERENCES race(race_cod)
DETAIL: Found referenced table's UPDATE trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY perso(perso_race_cod) REFERENCES race(race_cod)
DETAIL: Found referenced table's DELETE trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY perso(perso_race_cod) REFERENCES race(race_cod)
DETAIL: Found referenced table's DELETE trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY perso(perso_race_cod) REFERENCES race(race_cod)
DETAIL: Found referenced table's UPDATE trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY objet_generique(gobj_tobj_cod) REFERENCES type_objet(tobj_cod)
DETAIL: Found referencing table's trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY objets(obj_gobj_cod) REFERENCES objet_generique(gobj_cod)
DETAIL: Found referenced table's DELETE trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY objets(obj_gobj_cod) REFERENCES objet_generique(gobj_cod)
DETAIL: Found referenced table's UPDATE trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY objet_generique(gobj_obcar_cod) REFERENCES objets_caracs(obcar_cod)
DETAIL: Found referenced table's DELETE trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY objet_generique(gobj_obcar_cod) REFERENCES objets_caracs(obcar_cod)
DETAIL: Found referenced table's UPDATE trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY perso_competences(pcomp_pcomp_cod) REFERENCES competences(comp_cod)
DETAIL: Found referenced table's UPDATE trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY perso_competences(pcomp_pcomp_cod) REFERENCES competences(comp_cod)
DETAIL: Found referenced table's DELETE trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY competences(comp_typc_cod) REFERENCES type_competences(typc_cod)
DETAIL: Found referencing table's trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY objet_generique(gobj_comp_cod) REFERENCES competences(comp_cod)
DETAIL: Found referenced table's DELETE trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY objet_generique(gobj_comp_cod) REFERENCES competences(comp_cod)
DETAIL: Found referenced table's UPDATE trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY competences(comp_typc_cod) REFERENCES type_competences(typc_cod)
DETAIL: Found referencing table's trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY perso_competences(pcomp_pcomp_cod) REFERENCES competences(comp_cod)
DETAIL: Found referenced table's DELETE trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY perso_competences(pcomp_pcomp_cod) REFERENCES competences(comp_cod)
DETAIL: Found referenced table's UPDATE trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY race_comp(racecomp_comp_cod) REFERENCES competences(comp_cod)
DETAIL: Found referencing table's trigger.
NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY race_comp(racecomp_race_cod) REFERENCES race(race_cod)
DETAIL: Found referencing table's trigger.

So you have a *bunch* of partially broken FK constraints in that source
database. Some of them fairly clearly come from a table drop/create
--- for instance perso was evidently recreated without fixing most
of the FKs pointing to it --- but why the heck does it have a DELETE
but no UPDATE trigger for the FK from perso_competences? Bizarre.

I'm still inclined to think that we shouldn't try to automatically
build FK constraints for these broken trigger groups, but this example
definitely makes me wish that there were a more visible/useful way to
complain about them. The only idea that comes to mind right now is
to bleat into the postmaster log about any groups that remain unresolved
at backend exit time ... but that's obviously hopeless in terms of how
many people will notice it.

BTW, what PG version is this --- both the server being dumped from,
and the pg_dump you're using, if different?

regards, tom lane