Re: should I worry? - Mailing list pgsql-hackers

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)
REFERENCESperso(perso_cod)
 
DETAIL:  Found referencing table's trigger.
NOTICE:  ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY perso_position(ppos_perso_cod)
REFERENCESperso(perso_cod)
 
DETAIL:  Found referencing table's trigger.
NOTICE:  ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY objet_position(pobj_obj_cod)
REFERENCESobjets(obj_cod)
 
DETAIL:  Found referencing table's trigger.
NOTICE:  ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY objet_position(pobj_obj_cod)
REFERENCESobjets(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)
REFERENCESperso(perso_cod)
 
DETAIL:  Found referencing table's trigger.
NOTICE:  ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY perso_objets(perobj_obj_cod)
REFERENCESobjets(obj_cod)
 
DETAIL:  Found referencing table's trigger.
NOTICE:  ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY perso_objets(perobj_obj_cod)
REFERENCESobjets(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)
REFERENCESmessages(msg_cod)
 
DETAIL:  Found referencing table's trigger.
NOTICE:  ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY messages_dest(dmsg_msg_cod)
REFERENCESmessages(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)
REFERENCESperso(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)
REFERENCESperso(perso_cod)
 
DETAIL:  Found referencing table's trigger.
NOTICE:  ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY perso_competences(pcomp_perso_cod)
REFERENCESperso(perso_cod)
 
DETAIL:  Found referencing table's trigger.
NOTICE:  ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY perso_competences(pcomp_perso_cod)
REFERENCESperso(perso_cod)
 
DETAIL:  Found referenced table's DELETE trigger.
NOTICE:  ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY perso_position(ppos_pos_cod)
REFERENCESpositions(pos_cod)
 
DETAIL:  Found referenced table's DELETE trigger.
NOTICE:  ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY perso_position(ppos_pos_cod)
REFERENCESpositions(pos_cod)
 
DETAIL:  Found referenced table's UPDATE trigger.
NOTICE:  ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY objet_position(pobj_pos_cod)
REFERENCESpositions(pos_cod)
 
DETAIL:  Found referenced table's DELETE trigger.
NOTICE:  ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY objet_position(pobj_pos_cod)
REFERENCESpositions(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)
REFERENCEStype_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)
REFERENCESobjets_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)
REFERENCESobjets_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)
REFERENCEScompetences(comp_cod)
 
DETAIL:  Found referenced table's UPDATE trigger.
NOTICE:  ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY perso_competences(pcomp_pcomp_cod)
REFERENCEScompetences(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)
REFERENCEScompetences(comp_cod)
 
DETAIL:  Found referenced table's DELETE trigger.
NOTICE:  ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY objet_generique(gobj_comp_cod)
REFERENCEScompetences(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)
REFERENCEScompetences(comp_cod)
 
DETAIL:  Found referenced table's DELETE trigger.
NOTICE:  ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY perso_competences(pcomp_pcomp_cod)
REFERENCEScompetences(comp_cod)
 
DETAIL:  Found referenced table's UPDATE trigger.
NOTICE:  ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY race_comp(racecomp_comp_cod)
REFERENCEScompetences(comp_cod)
 
DETAIL:  Found referencing table's trigger.
NOTICE:  ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY race_comp(racecomp_race_cod)
REFERENCESrace(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