Hi Tom et all!
Thanks for your mails.
On Fri, 2 Nov 2007, Tom Lane wrote:
> Date: Fri, 02 Nov 2007 16:08:24 -0400
> From: Tom Lane <[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED]
> Cc: Heikki Linnakangas <[EMAIL PROTECTED]>,
> pgsql-hackers list <pgsql-hackers@postgresql.org>
> Subject: Re: [HACKERS] should I worry?
>
> [EMAIL PROTECTED] writes:
> > All triggers in the schema are named. So I assume they are triggers for
> > foreign keys.
>
> No, foreign-key triggers always have names too, and they don't look like
> that (they look like RI_ConstraintTrigger_nnn). I cannot find anyplace
> in PG that supplies "<unnamed>" as a default name for a trigger, either.
> So there's something weird about your schema, and we really need to
> see a test case ...
>
I'm confused, until I have clearence to send the schema, here are pg logs:
Nov 3 14:44:20 sun postgres[17963]: [189-1] ERROR: trigger "<unnamed>" for
relation "objets" already exists
Nov 3 14:44:20 sun postgres[17963]: [189-2] STATEMENT: CREATE CONSTRAINT
TRIGGER "<unnamed>"
Nov 3 14:44:20 sun postgres[17963]: [189-3] AFTER UPDATE ON objets
Nov 3 14:44:20 sun postgres[17963]: [189-4] FROM objet_position
Nov 3 14:44:20 sun postgres[17963]: [189-5] NOT DEFERRABLE INITIALLY
IMMEDIATE
Nov 3 14:44:20 sun postgres[17963]: [189-6] FOR EACH ROW
Nov 3 14:44:20 sun postgres[17963]: [189-7] EXECUTE PROCEDURE
"RI_FKey_noaction_upd"('<unnamed>', 'objet_position', 'objets', 'UNSPECIFIED',
'pobj_obj_cod',
Nov 3 14:44:20 sun postgres[17963]: [189-8] 'obj_cod');
Nov 3 14:44:20 sun postgres[17963]: [190-1] ERROR: trigger "<unnamed>" for
relation "perso_objets" already exists
Nov 3 14:44:20 sun postgres[17963]: [190-2] STATEMENT: CREATE CONSTRAINT
TRIGGER "<unnamed>"
Nov 3 14:44:20 sun postgres[17963]: [190-3] AFTER INSERT OR UPDATE ON
perso_objets
Nov 3 14:44:20 sun postgres[17963]: [190-4] FROM objets
Nov 3 14:44:20 sun postgres[17963]: [190-5] NOT DEFERRABLE INITIALLY
IMMEDIATE
Nov 3 14:44:20 sun postgres[17963]: [190-6] FOR EACH ROW
Nov 3 14:44:20 sun postgres[17963]: [190-7] EXECUTE PROCEDURE
"RI_FKey_check_ins"('<unnamed>', 'perso_objets', 'objets', 'UNSPECIFIED',
'perobj_obj_cod', 'obj_cod');
Nov 3 14:44:20 sun postgres[17963]: [191-1] ERROR: trigger "<unnamed>" for
relation "objets" already exists
Nov 3 14:44:20 sun postgres[17963]: [191-2] STATEMENT: CREATE CONSTRAINT
TRIGGER "<unnamed>"
Nov 3 14:44:20 sun postgres[17963]: [191-3] AFTER DELETE ON objets
Nov 3 14:44:20 sun postgres[17963]: [191-4] FROM perso_objets
Nov 3 14:44:20 sun postgres[17963]: [191-5] NOT DEFERRABLE INITIALLY
IMMEDIATE
Nov 3 14:44:20 sun postgres[17963]: [191-6] FOR EACH ROW
Nov 3 14:44:20 sun postgres[17963]: [191-7] EXECUTE PROCEDURE
"RI_FKey_noaction_del"('<unnamed>', 'perso_objets', 'objets', 'UNSPECIFIED',
'perobj_obj_cod',
Nov 3 14:44:20 sun postgres[17963]: [191-8] 'obj_cod');
Nov 3 14:44:20 sun postgres[17963]: [192-1] ERROR: trigger "<unnamed>" for
relation "objets" already exists
Nov 3 14:44:20 sun postgres[17963]: [192-2] STATEMENT: CREATE CONSTRAINT
TRIGGER "<unnamed>"
Nov 3 14:44:20 sun postgres[17963]: [192-3] AFTER UPDATE ON objets
Nov 3 14:44:20 sun postgres[17963]: [192-4] FROM perso_objets
Nov 3 14:44:20 sun postgres[17963]: [192-5] NOT DEFERRABLE INITIALLY
IMMEDIATE
Nov 3 14:44:20 sun postgres[17963]: [192-6] FOR EACH ROW
Nov 3 14:44:20 sun postgres[17963]: [192-7] EXECUTE PROCEDURE
"RI_FKey_noaction_upd"('<unnamed>', 'perso_objets', 'objets', 'UNSPECIFIED',
'perobj_obj_cod',
Nov 3 14:44:20 sun postgres[17963]: [192-8] 'obj_cod');
Nov 3 14:44:20 sun postgres[17963]: [193-1] ERROR: trigger "<unnamed>" for
relation "messages" already exists
Nov 3 14:44:20 sun postgres[17963]: [193-2] STATEMENT: CREATE CONSTRAINT
TRIGGER "<unnamed>"
Nov 3 14:44:20 sun postgres[17963]: [193-3] AFTER UPDATE ON messages
Nov 3 14:44:20 sun postgres[17963]: [193-4] FROM messages_dest
Nov 3 14:44:20 sun postgres[17963]: [193-5] NOT DEFERRABLE INITIALLY
IMMEDIATE
Nov 3 14:44:20 sun postgres[17963]: [193-6] FOR EACH ROW
Nov 3 14:44:20 sun postgres[17963]: [193-7] EXECUTE PROCEDURE
"RI_FKey_noaction_upd"('<unnamed>', 'messages_dest', 'messages', 'UNSPECIFIED',
'dmsg_msg_cod',
Nov 3 14:44:20 sun postgres[17963]: [193-8] 'msg_cod');
Nov 3 14:44:20 sun postgres[17963]: [194-1] ERROR: trigger "<unnamed>" for
relation "messages_dest" already exists
Nov 3 14:44:20 sun postgres[17963]: [194-2] STATEMENT: CREATE CONSTRAINT
TRIGGER "<unnamed>"
Nov 3 14:44:20 sun postgres[17963]: [194-3] AFTER INSERT OR UPDATE ON
messages_dest
Nov 3 14:44:20 sun postgres[17963]: [194-4] FROM perso
Nov 3 14:44:20 sun postgres[17963]: [194-5] NOT DEFERRABLE INITIALLY
IMMEDIATE
Nov 3 14:44:20 sun postgres[17963]: [194-6] FOR EACH ROW
Nov 3 14:44:20 sun postgres[17963]: [194-7] EXECUTE PROCEDURE
"RI_FKey_check_ins"('<unnamed>', 'messages_dest', 'perso', 'UNSPECIFIED',
'dmsg_perso_cod', 'perso_cod');
Nov 3 14:44:20 sun postgres[17963]: [195-1] ERROR: trigger "<unnamed>" for
relation "messages" already exists
Nov 3 14:44:20 sun postgres[17963]: [195-2] STATEMENT: CREATE CONSTRAINT
TRIGGER "<unnamed>"
Nov 3 14:44:20 sun postgres[17963]: [195-3] AFTER DELETE ON messages
Nov 3 14:44:20 sun postgres[17963]: [195-4] FROM messages_exp
Nov 3 14:44:20 sun postgres[17963]: [195-5] NOT DEFERRABLE INITIALLY
IMMEDIATE
Nov 3 14:44:20 sun postgres[17963]: [195-6] FOR EACH ROW
Nov 3 14:44:20 sun postgres[17963]: [195-7] EXECUTE PROCEDURE
"RI_FKey_noaction_del"('<unnamed>', 'messages_exp', 'messages', 'UNSPECIFIED',
'emsg_msg_cod',
Nov 3 14:44:20 sun postgres[17963]: [199-3] AFTER DELETE ON positions
Nov 3 14:44:20 sun postgres[17963]: [199-4] FROM objet_position
Nov 3 14:44:20 sun postgres[17963]: [199-5] NOT DEFERRABLE INITIALLY
IMMEDIATE
Nov 3 14:44:20 sun postgres[17963]: [199-6] FOR EACH ROW
Nov 3 14:44:20 sun postgres[17963]: [199-7] EXECUTE PROCEDURE
"RI_FKey_noaction_del"('<unnamed>', 'objet_position', 'positions',
'UNSPECIFIED', 'pobj_pos_cod',
Nov 3 14:44:20 sun postgres[17963]: [199-8] 'pos_cod');
Nov 3 14:44:20 sun postgres[17963]: [200-1] ERROR: trigger "<unnamed>" for
relation "positions" already exists
Nov 3 14:44:20 sun postgres[17963]: [200-2] STATEMENT: CREATE CONSTRAINT
TRIGGER "<unnamed>"
Nov 3 14:44:20 sun postgres[17963]: [200-3] AFTER UPDATE ON positions
Nov 3 14:44:20 sun postgres[17963]: [200-4] FROM objet_position
Nov 3 14:44:20 sun postgres[17963]: [200-5] NOT DEFERRABLE INITIALLY
IMMEDIATE
Nov 3 14:44:20 sun postgres[17963]: [200-6] FOR EACH ROW
Nov 3 14:44:20 sun postgres[17963]: [200-7] EXECUTE PROCEDURE
"RI_FKey_noaction_upd"('<unnamed>', 'objet_position', 'positions',
'UNSPECIFIED', 'pobj_pos_cod',
Nov 3 14:44:20 sun postgres[17963]: [200-8] 'pos_cod');
Nov 3 14:44:20 sun postgres[17963]: [201-1] ERROR: trigger "<unnamed>" for
relation "positions" already exists
Nov 3 14:44:20 sun postgres[17963]: [201-2] STATEMENT: CREATE CONSTRAINT
TRIGGER "<unnamed>"
Nov 3 14:44:20 sun postgres[17963]: [201-3] AFTER DELETE ON positions
Nov 3 14:44:20 sun postgres[17963]: [201-4] FROM or_position
Nov 3 14:44:20 sun postgres[17963]: [201-5] NOT DEFERRABLE INITIALLY
IMMEDIATE
Nov 3 14:44:20 sun postgres[17963]: [201-6] FOR EACH ROW
Nov 3 14:44:20 sun postgres[17963]: [201-7] EXECUTE PROCEDURE
"RI_FKey_noaction_del"('<unnamed>', 'or_position', 'positions', 'UNSPECIFIED',
'por_pos_cod', 'pos_cod');
Nov 3 14:44:20 sun postgres[17963]: [202-1] ERROR: trigger "<unnamed>" for
relation "positions" already exists
Nov 3 14:44:20 sun postgres[17963]: [202-2] STATEMENT: CREATE CONSTRAINT
TRIGGER "<unnamed>"
Nov 3 14:44:20 sun postgres[17963]: [202-3] AFTER UPDATE ON positions
Nov 3 14:44:20 sun postgres[17963]: [202-4] FROM or_position
Nov 3 14:44:20 sun postgres[17963]: [202-5] NOT DEFERRABLE INITIALLY
IMMEDIATE
Nov 3 14:44:20 sun postgres[17963]: [202-6] FOR EACH ROW
Nov 3 14:44:20 sun postgres[17963]: [202-7] EXECUTE PROCEDURE
"RI_FKey_noaction_upd"('<unnamed>', 'or_position', 'positions', 'UNSPECIFIED',
'por_pos_cod', 'pos_cod');
Nov 3 14:44:20 sun postgres[17963]: [203-1] ERROR: trigger "<unnamed>" for
relation "type_evt" already exists
Nov 3 14:44:20 sun postgres[17963]: [203-2] STATEMENT: CREATE CONSTRAINT
TRIGGER "<unnamed>"
Nov 3 14:44:20 sun postgres[17963]: [203-3] AFTER UPDATE ON type_evt
Nov 3 14:44:20 sun postgres[17963]: [203-4] FROM ligne_evt
Nov 3 14:44:20 sun postgres[17963]: [203-5] NOT DEFERRABLE INITIALLY
IMMEDIATE
Nov 3 14:44:20 sun postgres[17963]: [203-6] FOR EACH ROW
Nov 3 14:44:20 sun postgres[17963]: [203-7] EXECUTE PROCEDURE
"RI_FKey_noaction_upd"('<unnamed>', 'ligne_evt', 'type_evt', 'UNSPECIFIED',
'levt_tevt_cod', 'tevt_cod');
Nov 3 14:44:20 sun postgres[17963]: [204-1] ERROR: trigger "<unnamed>" for
relation "objet_generique" already exists
Nov 3 14:44:20 sun postgres[17963]: [204-2] STATEMENT: CREATE CONSTRAINT
TRIGGER "<unnamed>"
Nov 3 14:44:20 sun postgres[17963]: [204-3] AFTER DELETE ON
objet_generique
Nov 3 14:44:20 sun postgres[17963]: [204-4] FROM objets
Nov 3 14:44:20 sun postgres[17963]: [204-5] NOT DEFERRABLE INITIALLY
IMMEDIATE
Nov 3 14:44:20 sun postgres[17963]: [204-6] FOR EACH ROW
Nov 3 14:44:20 sun postgres[17963]: [204-7] EXECUTE PROCEDURE
"RI_FKey_noaction_del"('<unnamed>', 'objets', 'objet_generique', 'UNSPECIFIED',
'obj_gobj_cod',
Nov 3 14:44:20 sun postgres[17963]: [204-8] 'gobj_cod');
Nov 3 14:44:20 sun postgres[17963]: [205-1] ERROR: trigger "<unnamed>" for
relation "objet_generique" already exists
Nov 3 14:44:20 sun postgres[17963]: [205-2] STATEMENT: CREATE CONSTRAINT
TRIGGER "<unnamed>"
Nov 3 14:44:20 sun postgres[17963]: [205-3] AFTER UPDATE ON
objet_generique
Nov 3 14:44:20 sun postgres[17963]: [205-4] FROM objets
Nov 3 14:44:20 sun postgres[17963]: [205-5] NOT DEFERRABLE INITIALLY
IMMEDIATE
Nov 3 14:44:20 sun postgres[17963]: [205-6] FOR EACH ROW
Nov 3 14:44:20 sun postgres[17963]: [205-7] EXECUTE PROCEDURE
"RI_FKey_noaction_upd"('<unnamed>', 'objets', 'objet_generique', 'UNSPECIFIED',
'obj_gobj_cod',
Nov 3 14:44:20 sun postgres[17963]: [205-8] 'gobj_cod');
Nov 3 14:44:20 sun postgres[17963]: [206-1] ERROR: trigger "<unnamed>" for
relation "objets_caracs" already exists
Nov 3 14:44:20 sun postgres[17963]: [206-2] STATEMENT: CREATE CONSTRAINT
TRIGGER "<unnamed>"
Nov 3 14:44:20 sun postgres[17963]: [206-3] AFTER UPDATE ON
objets_caracs
Nov 3 14:44:20 sun postgres[17963]: [206-4] FROM objet_generique
Nov 3 14:44:20 sun postgres[17963]: [206-5] NOT DEFERRABLE INITIALLY
IMMEDIATE
Nov 3 14:44:20 sun postgres[17963]: [206-6] FOR EACH ROW
Nov 3 14:44:20 sun postgres[17963]: [206-7] EXECUTE PROCEDURE
"RI_FKey_noaction_upd"('<unnamed>', 'objet_generique', 'objets_caracs',
'UNSPECIFIED', 'gobj_obcar_cod',
Nov 3 14:44:20 sun postgres[17963]: [206-8] 'obcar_cod');
Nov 3 14:44:20 sun postgres[17963]: [207-1] ERROR: trigger "<unnamed>" for
relation "competences" already exists
Nov 3 14:44:20 sun postgres[17963]: [207-2] STATEMENT: CREATE CONSTRAINT
TRIGGER "<unnamed>"
Nov 3 14:44:20 sun postgres[17963]: [207-3] AFTER UPDATE ON competences
Nov 3 14:44:20 sun postgres[17963]: [207-4] FROM objet_generique
Nov 3 14:44:20 sun postgres[17963]: [207-5] NOT DEFERRABLE INITIALLY
IMMEDIATE
Nov 3 14:44:20 sun postgres[17963]: [207-6] FOR EACH ROW
Nov 3 14:44:20 sun postgres[17963]: [207-7] EXECUTE PROCEDURE
"RI_FKey_noaction_upd"('<unnamed>', 'objet_generique', 'competences',
'UNSPECIFIED', 'gobj_comp_cod',
Nov 3 14:44:20 sun postgres[17963]: [207-8] 'comp_cod');
Nov 3 14:44:20 sun postgres[17963]: [208-1] ERROR: trigger "<unnamed>" for
relation "race_comp" already exists
Nov 3 14:44:20 sun postgres[17963]: [208-2] STATEMENT: CREATE CONSTRAINT
TRIGGER "<unnamed>"
Nov 3 14:44:20 sun postgres[17963]: [208-3] AFTER INSERT OR UPDATE ON
race_comp
Nov 3 14:44:20 sun postgres[17963]: [208-4] FROM race
Nov 3 14:44:20 sun postgres[17963]: [208-5] NOT DEFERRABLE INITIALLY
IMMEDIATE
Nov 3 14:44:20 sun postgres[17963]: [208-6] FOR EACH ROW
Nov 3 14:44:20 sun postgres[17963]: [208-7] EXECUTE PROCEDURE
"RI_FKey_check_ins"('<unnamed>', 'race_comp', 'race', 'UNSPECIFIED',
'racecomp_race_cod', 'race_cod');
Maybe this would ring a bell that refuses to ring here :)
> regards, tom lane
>
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 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
