Re: should I worry?

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

> Date: Sun, 04 Nov 2007 19:47:04 -0500
> From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> To: ohp(at)pyrenet(dot)fr
> Cc: pgsql-hackers(at)postgresql(dot)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) 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.
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(at)pyrenet(dot)fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)