Dear Tom,
On Sat, 3 Nov 2007, Tom Lane wrote:
> Date: Sat, 03 Nov 2007 21:21:20 -0400
> From: Tom Lane <[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED]
> Cc: Heikki Linnakangas <[EMAIL PROTECTED]>,
> pgsql-hackers list <[email protected]>
> Subject: Re: [HACKERS] should I worry?
>
> [EMAIL PROTECTED] 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),
[EMAIL PROTECTED] -> [EMAIL PROTECTED]
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: [EMAIL PROTECTED]
------------------------------------------------------------------------------
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.
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
