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

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

> Date: Sat, 03 Nov 2007 21:21:20 -0400
> From: Tom Lane <tgl@sss.pgh.pa.us>
> To: ohp@pyrenet.fr
> Cc: Heikki Linnakangas <heikki@enterprisedb.com>,
>      pgsql-hackers list <pgsql-hackers@postgresql.org>
> Subject: Re: [HACKERS] should I worry?
>
> ohp@pyrenet.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>"
ontable "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>"
ontable "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>"
ontable "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>"
ontable "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>"
ontable "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>"
ontable "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>"
ontable "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>"
ontable "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@profi-martin.de ->
catherine@pyrenet.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>"
ontable "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>"
ontable "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>"
ontable "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@pyrenet.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.