Re: should I worry?

Dear Tom,
On Sat, 3 Nov 2007, Tom Lane wrote:

> Date: Sat, 03 Nov 2007 21:21:20 -0400
> From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> To: ohp(at)pyrenet(dot)fr
> Cc: Heikki Linnakangas <heikki(at)enterprisedb(dot)com>,
> pgsql-hackers list <pgsql-hackers(at)postgresql(dot)org>
> Subject: Re: [HACKERS] should I worry?
>
> ohp(at)pyrenet(dot)fr writes:
> > Is there a query I can use to know all the unamed trigger, delete them and
> > recreate with the right sentence?
>
> I've applied a patch that should persuade the backend to convert the old
> CREATE CONSTRAINT TRIGGER commands into proper foreign-key constraints.
> I'd suggest applying the patch and re-loading the dump instead of trying
> to fix things manually.
>
> http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/commands/trigger.c.diff?r1=1.219;r2=1.220
>
> regards, tom lane
>
I've tried it and got those logs:

Nov 4 16:02:24 sun postgres[12505]: [189-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "RI_ConstraintTrigger_28974025" on table
Nov 4 16:02:24 sun postgres[12505]: [189-2] "perso_competences"
Nov 4 16:02:24 sun postgres[12505]: [190-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "<unnamed>" on table "perso_position"
Nov 4 16:02:24 sun postgres[12505]: [191-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "<unnamed>" on table "objet_position"
Nov 4 16:02:24 sun postgres[12505]: [192-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "<unnamed>" on table "objet_position"
Nov 4 16:02:24 sun postgres[12505]: [193-1] NOTICE: converting foreign-key trigger group into constraint "<unnamed>" on table "objet_position"
Nov 4 16:02:25 sun postgres[12505]: [194-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "<unnamed>" on table "perso_objets"
Nov 4 16:02:25 sun postgres[12505]: [195-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "<unnamed>" on table "perso_objets"
Nov 4 16:02:25 sun postgres[12505]: [196-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "<unnamed>" on table "perso_objets"
Nov 4 16:02:25 sun postgres[12505]: [197-1] NOTICE: converting foreign-key trigger group into constraint "<unnamed>" on table "perso_objets"
Nov 4 16:02:25 sun postgres[12505]: [198-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "<unnamed>" on table "messages_dest"
Nov 4 16:02:25 sun postgres[12505]: [199-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "<unnamed>" on table "messages_dest"
Nov 4 16:02:25 sun postgres[12505]: [200-1] NOTICE: converting foreign-key trigger group into constraint "<unnamed>" on table "messages_dest"
Nov 4 16:02:25 sun postfix/smtpd[12751]: connect from 82-32-100-168.cable.ubr01.hawk.blueyonder.co.uk[82.32.100.168]
Nov 4 16:02:26 sun postgres[12505]: [201-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "<unnamed>" on table "messages_dest"
Nov 4 16:02:26 sun postgres[12505]: [202-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "<unnamed>" on table "messages_exp"
Nov 4 16:02:26 sun postgres[12505]: [203-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "<unnamed>" on table "messages_exp"
Nov 4 16:02:26 sun postgres[12505]: [204-1] NOTICE: converting foreign-key trigger group into constraint "<unnamed>" on table "messages_exp"
Nov 4 16:02:26 sun postgres[12505]: [205-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "<unnamed>" on table "messages_exp"
Nov 4 16:02:26 sun postgres[12505]: [206-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "RI_ConstraintTrigger_28974598" on table
Nov 4 16:02:26 sun postgres[12505]: [206-2] "perso_competences"
Nov 4 16:02:26 sun postgres[12505]: [207-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "RI_ConstraintTrigger_28974610" on table
Nov 4 16:02:26 sun postgres[12505]: [207-2] "perso_competences"
Nov 4 16:02:26 sun postgres[12505]: [208-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "<unnamed>" on table "perso_position"
Nov 4 16:02:26 sun postgres[12505]: [209-1] NOTICE: converting foreign-key trigger group into constraint "<unnamed>" on table "perso_position"
Nov 4 16:02:26 sun postgres[12505]: [210-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "<unnamed>" on table "objet_position"
Nov 4 16:02:26 sun postgres[12505]: [211-1] NOTICE: converting foreign-key trigger group into constraint "<unnamed>" on table "objet_position"
Nov 4 16:02:26 sun postgres[12505]: [212-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "<unnamed>" on table "or_position"
Nov 4 16:02:26 sun postgres[12505]: [213-1] NOTICE: converting foreign-key trigger group into constraint "<unnamed>" on table "or_position"
Nov 4 16:02:26 sun postgres[12505]: [214-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "<unnamed>" on table "ligne_evt"
Nov 4 16:02:26 sun postgres[12505]: [215-1] NOTICE: converting foreign-key trigger group into constraint "<unnamed>" on table "ligne_evt"
Nov 4 16:02:26 sun postgres[12505]: [216-1] ERROR: insert or update on table "ligne_evt" violates foreign key constraint "ligne_evt_levt_tevt_cod_fkey"
Nov 4 16:02:26 sun postgres[12505]: [216-2] DETAIL: Key (levt_tevt_cod)=(99) is not present in table "type_evt".
Nov 4 16:02:26 sun postgres[12505]: [216-3] STATEMENT: CREATE CONSTRAINT TRIGGER "<unnamed>"
Nov 4 16:02:26 sun postgres[12505]: [216-4] AFTER UPDATE ON type_evt
Nov 4 16:02:26 sun postgres[12505]: [216-5] FROM ligne_evt
Nov 4 16:02:26 sun postgres[12505]: [216-6] NOT DEFERRABLE INITIALLY IMMEDIATE
Nov 4 16:02:26 sun postgres[12505]: [216-7] FOR EACH ROW
Nov 4 16:02:26 sun postgres[12505]: [216-8] EXECUTE PROCEDURE "RI_FKey_noaction_upd"('<unnamed>', 'ligne_evt', 'type_evt', 'UNSPECIFIED', 'levt_tevt_cod', 'tevt_cod');
Nov 4 16:02:26 sun postgres[12505]: [217-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "RI_ConstraintTrigger_28973942" on table "perso"
Nov 4 16:02:26 sun postgres[12505]: [218-1] NOTICE: converting foreign-key trigger group into constraint "RI_ConstraintTrigger_28973940" on table "perso"
Nov 4 16:02:26 sun postgres[12505]: [219-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "RI_ConstraintTrigger_28974582" on table "perso"
Nov 4 16:02:26 sun postgres[12505]: [220-1] NOTICE: converting foreign-key trigger group into constraint "RI_ConstraintTrigger_28974580" on table "perso"
Nov 4 16:02:26 localhost sqlgrey: grey: new: 82.32.100.168(82.32.100.168), kitchens(at)profi-martin(dot)de -> catherine(at)pyrenet(dot)fr
Nov 4 16:02:26 sun postgres[12505]: [221-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "<unnamed>" on table "objet_generique"
Nov 4 16:02:26 sun postgres[12505]: [222-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "<unnamed>" on table "objets"
Nov 4 16:02:26 sun postgres[12505]: [223-1] NOTICE: converting foreign-key trigger group into constraint "<unnamed>" on table "objets"
Nov 4 16:02:26 sun postgres[12505]: [224-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "<unnamed>" on table "objet_generique"
Nov 4 16:02:26 sun postgres[12505]: [225-1] NOTICE: converting foreign-key trigger group into constraint "<unnamed>" on table "objet_generique"
Nov 4 16:02:26 sun postgres[12505]: [226-1] ERROR: insert or update on table "objet_generique" violates foreign key constraint "objet_generique_gobj_obcar_cod_fkey"
Nov 4 16:02:26 sun postgres[12505]: [226-2] DETAIL: Key (gobj_obcar_cod)=(0) is not present in table "objets_caracs".
Nov 4 16:02:26 sun postgres[12505]: [226-3] STATEMENT: CREATE CONSTRAINT TRIGGER "<unnamed>"
Nov 4 16:02:26 sun postgres[12505]: [226-4] AFTER UPDATE ON objets_caracs
Nov 4 16:02:26 sun postgres[12505]: [226-5] FROM objet_generique
Nov 4 16:02:26 sun postgres[12505]: [226-6] NOT DEFERRABLE INITIALLY IMMEDIATE
Nov 4 16:02:26 sun postgres[12505]: [226-7] FOR EACH ROW
Nov 4 16:02:26 sun postgres[12505]: [226-8] EXECUTE PROCEDURE "RI_FKey_noaction_upd"('<unnamed>', 'objet_generique', 'objets_caracs', 'UNSPECIFIED', 'gobj_obcar_cod',
Nov 4 16:02:26 sun postgres[12505]: [226-9] 'obcar_cod');
Nov 4 16:02:26 sun postgres[12505]: [227-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "RI_ConstraintTrigger_28974035" on table
Nov 4 16:02:26 sun postgres[12505]: [227-2] "perso_competences"
Nov 4 16:02:26 sun postgres[12505]: [228-1] NOTICE: converting foreign-key trigger group into constraint "RI_ConstraintTrigger_28974033" on table "perso_competences"
Nov 4 16:02:27 sun postgres[12505]: [229-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "RI_ConstraintTrigger_28974011" on table "competences"
Nov 4 16:02:27 sun postgres[12505]: [230-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "<unnamed>" on table "objet_generique"
Nov 4 16:02:27 sun postgres[12505]: [231-1] NOTICE: converting foreign-key trigger group into constraint "<unnamed>" on table "objet_generique"
Nov 4 16:02:27 sun postgres[12505]: [232-1] ERROR: insert or update on table "objet_generique" violates foreign key constraint "objet_generique_gobj_comp_cod_fkey"
Nov 4 16:02:27 sun postgres[12505]: [232-2] DETAIL: Key (gobj_comp_cod)=(0) is not present in table "competences".
Nov 4 16:02:27 sun postgres[12505]: [232-3] STATEMENT: CREATE CONSTRAINT TRIGGER "<unnamed>"
Nov 4 16:02:27 sun postgres[12505]: [232-4] AFTER UPDATE ON competences
Nov 4 16:02:27 sun postgres[12505]: [232-5] FROM objet_generique
Nov 4 16:02:27 sun postgres[12505]: [232-6] NOT DEFERRABLE INITIALLY IMMEDIATE
Nov 4 16:02:27 sun postgres[12505]: [232-7] FOR EACH ROW
Nov 4 16:02:27 sun postgres[12505]: [232-8] EXECUTE PROCEDURE "RI_FKey_noaction_upd"('<unnamed>', 'objet_generique', 'competences', 'UNSPECIFIED', 'gobj_comp_cod',
Nov 4 16:02:27 sun postgres[12505]: [232-9] 'comp_cod');
Nov 4 16:02:27 sun postgres[12505]: [233-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "RI_ConstraintTrigger_28974594" on table "competences"
Nov 4 16:02:27 sun postgres[12505]: [234-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "RI_ConstraintTrigger_28974592" on table
Nov 4 16:02:27 sun postgres[12505]: [234-2] "perso_competences"
Nov 4 16:02:27 sun postgres[12505]: [235-1] NOTICE: converting foreign-key trigger group into constraint "RI_ConstraintTrigger_28974590" on table "perso_competences"

I've got two problems:

Looking at the errors, ISTM foreign statement is the over way round :
levt_tevt_cod is in ligne_evt NOT in type_evt

And, looking at the generated foreign keys, I don't see ON UPDATE/ON
DELETE clauses

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)

PS: For obvious reasons, I can't send the schema to the list, but I can
send it to you off list if it helps.