Hi Tom On Sun, 4 Nov 2007, Tom Lane wrote: > Date: Sun, 04 Nov 2007 19:47:04 -0500 > From: Tom Lane <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] > 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) 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: [EMAIL PROTECTED] ------------------------------------------------------------------------------ Make your life a dream, make your dream a reality. (St Exupery) ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq