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

Hi Tom
On Sun, 4 Nov 2007, Tom Lane wrote:

> Date: Sun, 04 Nov 2007 19:47:04 -0500
> From: Tom Lane <tgl@sss.pgh.pa.us>
> To: ohp@pyrenet.fr
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] 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)
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)
REFERENCESmessages(msg_cod)
 
> DETAIL:  Found referencing table's trigger.
> NOTICE:  ignoring incomplete trigger group for constraint "<unnamed>" FOREIGN KEY messages_exp(emsg_msg_cod)
REFERENCESmessages(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)
REFERENCEStype_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)
REFERENCEStype_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.
Yes , weird
I guess that explain the bloating of the db I see
>
> 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?
>
PG is 8.2.5, dumps have been taken by both 8.2.5 and 8.3beta2 pg_dump
>             regards, tom lane
>
I just talk to my customer and he/we'll make a big batch deleting and
recreating all foreign keys on 8.2.5.
The question is, how do we get rid of those useless <unamed> triggers?
tell me what you think, Master :)

Thank you very much for your help on this one!

Best regards,
-- 
Olivier PRENANT                    Tel: +33-5-61-50-97-00 (Work)
15, Chemin des Monges                +33-5-61-50-97-01 (Fax)
31190 AUTERIVE                       +33-6-07-63-80-64 (GSM)
FRANCE                          Email: ohp@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)