Re: should I worry?

Hi hackers,

I'm now testing 8.3beta2 on a relatively big (10G) database.
I've tried with pg_dymp -Fc/pg_restore and pg_dump/pgsql and get those
errors:

ERROR: trigger "<unnamed>" for relation "objets" already exists
ERROR: trigger "<unnamed>" for relation "perso_objets" already exists
ERROR: trigger "<unnamed>" for relation "objets" already exists
ERROR: trigger "<unnamed>" for relation "objets" already exists
ERROR: trigger "<unnamed>" for relation "messages" already exists
ERROR: trigger "<unnamed>" for relation "messages_dest" already exists
ERROR: trigger "<unnamed>" for relation "messages" already exists
ERROR: trigger "<unnamed>" for relation "messages" already exists
ERROR: trigger "<unnamed>" for relation "messages_exp" already exists
ERROR: trigger "<unnamed>" for relation "positions" already exists
ERROR: trigger "<unnamed>" for relation "positions" already exists
ERROR: trigger "<unnamed>" for relation "positions" already exists
ERROR: trigger "<unnamed>" for relation "positions" already exists
ERROR: trigger "<unnamed>" for relation "positions" already exists
ERROR: trigger "<unnamed>" for relation "type_evt" already exists
ERROR: trigger "<unnamed>" for relation "objet_generique" already exists
ERROR: trigger "<unnamed>" for relation "objet_generique" already exists
ERROR: trigger "<unnamed>" for relation "objets_caracs" already exists
ERROR: trigger "<unnamed>" for relation "competences" already exists
ERROR: trigger "<unnamed>" for relation "race_comp" already exists

I don't remember having those with beta1 and don't know what to look at.

I've tried both 8.2.5 and 8.3beta2 pg_dump.

I'm also playing with checkpoint logging. What should I see? few buffers
dump, little write time, little sync time?

Unrelated, who should I contact to get my password of jaguar in
build_farm, this animal has been set up to test with
-DCLOBBER_CACHE_ALWAYS and I never received the password.

Maybe pgfbuildfarm.org web should include a button to resend account to
the owner.

Just my 0.01$...

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: ohp(at)pyrenet(dot)fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)

ohp(at)pyrenet(dot)fr wrote:
> I'm now testing 8.3beta2 on a relatively big (10G) database.
> I've tried with pg_dymp -Fc/pg_restore and pg_dump/pgsql and get those
> errors:

Could you be a bit more specific? The database you tried to restore to
was empty, right? Can you post the dump file (schema-only)?

> I'm also playing with checkpoint logging. What should I see? few buffers
> dump, little write time, little sync time?

Depends on how much activity there is. If there's a lot of dirty
buffers, a lot of written buffers and long write time is perfectly normal.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Fri, 2 Nov 2007, Heikki Linnakangas wrote:

> Date: Fri, 02 Nov 2007 18:11:14 +0000
> From: Heikki Linnakangas <heikki(at)enterprisedb(dot)com>
> To: ohp(at)pyrenet(dot)fr
> Cc: pgsql-hackers list <pgsql-hackers(at)postgresql(dot)org>
> Subject: Re: [HACKERS] should I worry?
>
> ohp(at)pyrenet(dot)fr wrote:
> > I'm now testing 8.3beta2 on a relatively big (10G) database.
> > I've tried with pg_dymp -Fc/pg_restore and pg_dump/pgsql and get those
> > errors:
>
> Could you be a bit more specific? The database you tried to restore to
> was empty, right? Can you post the dump file (schema-only)?
Yes it was empty, Sorry, I can't post the schema, it belongs to one of my
customer...

All triggers in the schema are named. So I assume they are triggers for
foreign keys.

It's hard to tell if all foreign keys have been created after restore is
complete...
>
> > I'm also playing with checkpoint logging. What should I see? few buffers
> > dump, little write time, little sync time?
>
> Depends on how much activity there is. If there's a lot of dirty
> buffers, a lot of written buffers and long write time is perfectly normal.
>
>
Thanks for the explanation.

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)

ohp(at)pyrenet(dot)fr wrote:
> On Fri, 2 Nov 2007, Heikki Linnakangas wrote:
>
>> Date: Fri, 02 Nov 2007 18:11:14 +0000
>> From: Heikki Linnakangas <heikki(at)enterprisedb(dot)com>
>> To: ohp(at)pyrenet(dot)fr
>> Cc: pgsql-hackers list <pgsql-hackers(at)postgresql(dot)org>
>> Subject: Re: [HACKERS] should I worry?
>>
>> ohp(at)pyrenet(dot)fr wrote:
>>> I'm now testing 8.3beta2 on a relatively big (10G) database.
>>> I've tried with pg_dymp -Fc/pg_restore and pg_dump/pgsql and get those
>>> errors:
>> Could you be a bit more specific? The database you tried to restore to
>> was empty, right? Can you post the dump file (schema-only)?
> Yes it was empty, Sorry, I can't post the schema, it belongs to one of my
> customer...

Maybe you could reduce it to a small self-contained test case, with
table names changed to protect the innocent?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

ohp(at)pyrenet(dot)fr 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 ...

regards, tom lane

Hi,

Le Friday 02 November 2007 21:08:24 Tom Lane, vous avez écrit :
> 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've had some restore problems with "<unnamed>" triggers on a 8.1 database. It
contained some de-activated triggers dating from pre-7.3 era, and I finally
managed to clean out the schema with the adddepend contrib utility.
http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/adddepend/Attic/

After running this, I had no more problems related to unnamed triggers, but I
can't remember the specifics of the errors I had. For adddepend to run, some
data were to be removed, too (disabled constraint triggers made possible to
insert them at some point in the past).

Sorry for the imprecision of the post, hope this helps,
--
dim

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 <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:
> > 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: ohp(at)pyrenet(dot)fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)

ohp(at)pyrenet(dot)fr writes:
> 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');

These must be hangovers from some truly ancient version of Postgres :-(

I'd suggest dropping all these triggers and setting up real foreign key
constraint declarations instead. If there seem to be too many to do it
manually, you might try contrib/adddepend which used to be included
with Postgres (between 7.3 and 8.1).

Looking into it, I think the reason you're getting bit now is that
CREATE CONSTRAINT TRIGGER didn't use to insist on a unique trigger name.
Now it does. But it's way past time for you to get rid of these
old-style foreign keys anyway.

regards, tom lane

Sorry to post on my own post,
I found this (as a little exemple I've got plenty of these)
could that be the culprit and how to correct that?
--
-- Name: RI_ConstraintTrigger_609094000; Type: TRIGGER; Schema: public; Owner: delain
--

CREATE CONSTRAINT TRIGGER "<unnamed>"
AFTER DELETE ON objets
FROM objet_position
NOT DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_noaction_del"('<unnamed>', 'objet_position', 'objets', 'UNSPECIFIED', 'pobj_obj_cod', 'obj_cod');

--
-- Name: RI_ConstraintTrigger_609094001; Type: TRIGGER; Schema: public; Owner: delain
--

CREATE CONSTRAINT TRIGGER "<unnamed>"
AFTER UPDATE ON objets
FROM objet_position
NOT DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_noaction_upd"('<unnamed>', 'objet_position', 'objets', 'UNSPECIFIED', 'pobj_obj_cod', 'obj_cod');

This dump was generated by pg_dump 8.3beta1 against a 8.2.5 db

Best regardsb
On Fri, 2 Nov 2007, Heikki Linnakangas wrote:

> Date: Fri, 02 Nov 2007 19:59:03 +0000
> From: Heikki Linnakangas <heikki(at)enterprisedb(dot)com>
> To: ohp(at)pyrenet(dot)fr
> Cc: pgsql-hackers list <pgsql-hackers(at)postgresql(dot)org>
> Subject: Re: [HACKERS] should I worry?
>
> ohp(at)pyrenet(dot)fr wrote:
> > On Fri, 2 Nov 2007, Heikki Linnakangas wrote:
> >
> >> Date: Fri, 02 Nov 2007 18:11:14 +0000
> >> From: Heikki Linnakangas <heikki(at)enterprisedb(dot)com>
> >> To: ohp(at)pyrenet(dot)fr
> >> Cc: pgsql-hackers list <pgsql-hackers(at)postgresql(dot)org>
> >> Subject: Re: [HACKERS] should I worry?
> >>
> >> ohp(at)pyrenet(dot)fr wrote:
> >>> I'm now testing 8.3beta2 on a relatively big (10G) database.
> >>> I've tried with pg_dymp -Fc/pg_restore and pg_dump/pgsql and get those
> >>> errors:
> >> Could you be a bit more specific? The database you tried to restore to
> >> was empty, right? Can you post the dump file (schema-only)?
> > Yes it was empty, Sorry, I can't post the schema, it belongs to one of my
> > customer...
>
> Maybe you could reduce it to a small self-contained test case, with
> table names changed to protect the innocent?
>
>

--
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)

On Sat, 3 Nov 2007, Tom Lane wrote:

> Date: Sat, 03 Nov 2007 12:42:24 -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:
> > 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');
>
> These must be hangovers from some truly ancient version of Postgres :-(
Yes, this db is restored on every new version for nearly 5 years now :)
>
> I'd suggest dropping all these triggers and setting up real foreign key
> constraint declarations instead. If there seem to be too many to do it
> manually, you might try contrib/adddepend which used to be included
> with Postgres (between 7.3 and 8.1).
Done see below
>
Actually, I can't even edit the dump 'cause it's 3.5G uncompress, xemacs
gives up at 2G :-(

> Looking into it, I think the reason you're getting bit now is that
> CREATE CONSTRAINT TRIGGER didn't use to insist on a unique trigger name.
> Now it does. But it's way past time for you to get rid of these
> old-style foreign keys anyway.
I've reload the dump on a 8.2.5 then ran adddepend.pl, took a dump and
reloaded it on a 8.3beta2, and have less but still errors.

What do I loose if I leave it as is, I guess I'll miss a few foreign keys,
is there an easy way to know which?

Is there a query I can use to know all the unamed trigger, delete them and
recreate with the right sentence?
>
> regards, tom lane
>
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)

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

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.

ohp(at)pyrenet(dot)fr writes:
> 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

No, that's just how we've worded FK violation errors for some time.
The real question is how did FK violations get into your dump?

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

The ones we can see here are default (NO ACTION) cases, so I'm not
convinced you're describing a real problem.

regards, tom lane

ohp(at)pyrenet(dot)fr writes:
> I've tried it and got those logs:

BTW, is that a complete list of the NOTICEs you got? I'd expect to see
exactly two "ignoring" messages for each "converting" message, and it's
a bit worrisome that that's not what you seem to have.

Another thing that's strange is that some of the messages reference
auto-generated trigger names instead of constraint names:

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"

That's fairly unhelpful since it makes it harder to match up the
messages, but I didn't see any such cases when I was testing the patch
here.

Would it be possible for you to send me (off-list) all of the CREATE
CONSTRAINT TRIGGER commands appearing in the dump? I don't need to
see anything else, but I'm curious to look at those.

regards, tom lane

> On Sun, 4 Nov 2007, Tom Lane wrote:
>> Would it be possible for you to send me (off-list) all of the CREATE
>> CONSTRAINT TRIGGER commands appearing in the dump?

> [done]

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.
In some cases it looks like the FK constraint was re-established
with a fresh ALTER TABLE ADD CONSTRAINT command, leading to redundant
sets of triggers on one side of the relationship, while in other cases
it wasn't, leading to a partially functional FK constraint :-(
This explains why your dump contains some invalid data: the constraint
wasn't being enforced against the FK table.

So the question is what to do when we see a situation like this.

As the code stands, it will try to re-create an FK constraint after
seeing the two triggers on the PK table, regardless of whether a
trigger is present on the FK table. That has a couple of disadvantages:
* it may try to create an FK constraint that was only partially enforced
before, leading to errors like we saw in Olivier's report.
* in the situation where the user had re-established the constraint,
we may create redundant FK constraints.

The only thing I can think to do differently is to insist on seeing
all three matching triggers before we create the FK constraint.
This could be programmed so that we make only one constraint not two
when there's redundant triggers in the input. The downside is that
we'd fail to translate a constraint that was only partially enforced
in the source database. Maybe that's the best thing; it's symmetric
with what will happen when we see only the trigger on the FK table
and none for the PK table. (We can't do anything else in that case,
for lack of sufficient information.)

It would be nice if we could throw warnings for incomplete trigger
sets, but I see no very helpful way to do that --- we'd only be
sure we could emit the warning upon client disconnect, and then
it's too late to be sure the user would see the warning.

Comments, better ideas?

regards, tom lane

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.

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?

regards, tom lane

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.

I looked back in our CVS history and found that the above statement
is incorrect, or at least an oversimplification. Ever since PG 7.0
(the first release with FOREIGN KEY support) there has been a mechanism
to auto-drop the FK triggers on the other side of the relationship.
Since 7.3 the pg_depend mechanism has handled it, but before that DROP
TABLE did a scan of pg_trigger for entries having tgconstrrelid pointing
to the doomed table. So how come it broke? Further study provided a
pathway that could cause this: 7.0 pg_dump failed to include
tgconstrrelid (the "FROM table" clause) in its CREATE CONSTRAINT TRIGGER
commands. This was fixed in 7.1 and all later releases, but was never
back-patched to 7.0.x. In 7.3 and later, the backend has a hack to
regenerate the missing tgconstrrelid value when loading an RI constraint
trigger definition, but 7.0-7.2 will just load the definition and set
tgconstrrelid = 0.

So the scenario must have gone like this:

1. Create some FOREIGN KEY constraints in 7.0.
2. Dump the database using 7.0's pg_dump.
3. Load into 7.0, 7.1, or 7.2.
4. Drop and recreate the table on one side of the FK relationship,
but don't re-create the FK constraint.
5. Continue to use the database up to the present day without ever
noticing that the FK constraint was only partially enforced.
(Dumps and reloads would not have changed its status.)

It's still not clear to me how perso managed to have only a DELETE
trigger and no UPDATE trigger for the FK reference from
perso_competences, but all the other inconsistencies seem to be
explainable by this mechanism.

Also, I was wondering why some of the trigger definitions had
names like "RI_ConstraintTrigger_28974011" rather than the underlying
constraint name, which is always "<unnamed>" in this set of triggers.
I now notice that these entries are also associated with duplicated
sets of triggers, which makes me think they are the result of manual
attempts at patching broken RI trigger sets.

I don't suppose there is enough history of schema changes in this DB
to confirm or deny these theories?

Anyway, the conclusion that the breakage must have gone undetected
since 7.2 or before makes me feel that maybe this isn't quite as
critical as I thought before. There can't be a huge number of people
in such situations, and the FKs aren't working per spec for them
anyway.

regards, tom lane

Hi Tom
On Sun, 4 Nov 2007, Tom Lane wrote:

> Date: Sun, 04 Nov 2007 19:47:04 -0500
> From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> To: ohp(at)pyrenet(dot)fr
> Cc: pgsql-hackers(at)postgresql(dot)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: ohp(at)pyrenet(dot)fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)

ohp(at)pyrenet(dot)fr writes:
> On Sun, 4 Nov 2007, Tom Lane wrote:
>> So you have a *bunch* of partially broken FK constraints in that source
>> database.

> 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?

DROP TRIGGER should be fine.

regards, tom lane

Doesn't DROP TRIGGER require the name of the trigger? He says they
are unnamed. How then does he drop them?

On Nov 5, 2007, at 6:31 AM, Tom Lane wrote:

> ohp(at)pyrenet(dot)fr writes:
>> On Sun, 4 Nov 2007, Tom Lane wrote:
>>> So you have a *bunch* of partially broken FK constraints in that
>>> source
>>> database.
>
>> 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?
>
> DROP TRIGGER should be fine.
>
> regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that
> your
> message can get through to the mailing list cleanly
>

Rick Gigger wrote:
> Doesn't DROP TRIGGER require the name of the trigger? He says they are
> unnamed. How then does he drop them?

They're not really unnamed. pg_dump just replaces the real name with
"<unnamed>".

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Heikki Linnakangas <heikki(at)enterprisedb(dot)com> writes:
> Rick Gigger wrote:
>> Doesn't DROP TRIGGER require the name of the trigger? He says they are
>> unnamed. How then does he drop them?

> They're not really unnamed. pg_dump just replaces the real name with
> "<unnamed>".

And \d will show the real names of the triggers, so it's not really
that hard to drop them:

u=# \d t1
Table "public.t1"
Column | Type | Modifiers
--------+---------+-----------
f1 | integer | not null
Indexes:
"t1_pkey" PRIMARY KEY, btree (f1)
Triggers:
"RI_ConstraintTrigger_229629" AFTER DELETE ON t1 FROM t2 NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del"('<unnamed>', 't2', 't1', 'UNSPECIFIED', 'f2', 'f1')
"RI_ConstraintTrigger_229630" AFTER UPDATE ON t1 FROM t2 NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd"('<unnamed>', 't2', 't1', 'UNSPECIFIED', 'f2', 'f1')

u=# drop trigger "RI_ConstraintTrigger_229629" on t1;
DROP TRIGGER

I do recall newbies forgetting to double-quote the mixed-case trigger
names when this came up in times past, though.

regards, tom lane

Ah, yes it was the quotes. I guess that makes me a newbie. :)

On Nov 5, 2007, at 1:53 PM, Tom Lane wrote:

> Heikki Linnakangas <heikki(at)enterprisedb(dot)com> writes:
>> Rick Gigger wrote:
>>> Doesn't DROP TRIGGER require the name of the trigger? He says
>>> they are
>>> unnamed. How then does he drop them?
>
>> They're not really unnamed. pg_dump just replaces the real name with
>> "<unnamed>".
>
> And \d will show the real names of the triggers, so it's not really
> that hard to drop them:
>
> u=# \d t1
> Table "public.t1"
> Column | Type | Modifiers
> --------+---------+-----------
> f1 | integer | not null
> Indexes:
> "t1_pkey" PRIMARY KEY, btree (f1)
> Triggers:
> "RI_ConstraintTrigger_229629" AFTER DELETE ON t1 FROM t2 NOT
> DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
> "RI_FKey_noaction_del"('<unnamed>', 't2', 't1', 'UNSPECIFIED', 'f2',
> 'f1')
> "RI_ConstraintTrigger_229630" AFTER UPDATE ON t1 FROM t2 NOT
> DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
> "RI_FKey_noaction_upd"('<unnamed>', 't2', 't1', 'UNSPECIFIED', 'f2',
> 'f1')
>
> u=# drop trigger "RI_ConstraintTrigger_229629" on t1;
> DROP TRIGGER
>
> I do recall newbies forgetting to double-quote the mixed-case trigger
> names when this came up in times past, though.
>
> regards, tom lane
>

On Mon, 5 Nov 2007, Tom Lane wrote:

> Date: Mon, 05 Nov 2007 15:53:10 -0500
> From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> To: Heikki Linnakangas <heikki(at)enterprisedb(dot)com>
> Cc: Rick Gigger <rick(at)alpinenetworking(dot)com>, ohp(at)pyrenet(dot)fr,
> pgsql-hackers(at)postgresql(dot)org
> Subject: Re: [HACKERS] should I worry?
>
> Heikki Linnakangas <heikki(at)enterprisedb(dot)com> writes:
> > Rick Gigger wrote:
> >> Doesn't DROP TRIGGER require the name of the trigger? He says they are
> >> unnamed. How then does he drop them?
>
> > They're not really unnamed. pg_dump just replaces the real name with
> > "<unnamed>".
>
> And \d will show the real names of the triggers, so it's not really
> that hard to drop them:
>
> u=# \d t1
> Table "public.t1"
> Column | Type | Modifiers
> --------+---------+-----------
> f1 | integer | not null
> Indexes:
> "t1_pkey" PRIMARY KEY, btree (f1)
> Triggers:
> "RI_ConstraintTrigger_229629" AFTER DELETE ON t1 FROM t2 NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del"('<unnamed>', 't2', 't1', 'UNSPECIFIED', 'f2', 'f1')
> "RI_ConstraintTrigger_229630" AFTER UPDATE ON t1 FROM t2 NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd"('<unnamed>', 't2', 't1', 'UNSPECIFIED', 'f2', 'f1')
>
> u=# drop trigger "RI_ConstraintTrigger_229629" on t1;
> DROP TRIGGER
>
> I do recall newbies forgetting to double-quote the mixed-case trigger
> names when this came up in times past, though.
>
> regards, tom lane
>
I'd love to find a query against pg_triggers giving the table name for
each RI_ConstraintTrigger_xxx.
That would be quick to delete them all instead of going through all
tables.
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)

ohp(at)pyrenet(dot)fr wrote:
> I'd love to find a query against pg_triggers giving the table name for
> each RI_ConstraintTrigger_xxx.

SELECT t.tgname, c.relname, tgconstrname FROM pg_trigger t, pg_class c
WHERE t.tgrelid = c.oid AND tgname like 'RI_ConstraintTrigger_%';

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Tue, 6 Nov 2007, Heikki Linnakangas wrote:

> Date: Tue, 06 Nov 2007 13:07:23 +0000
> From: Heikki Linnakangas <heikki(at)enterprisedb(dot)com>
> To: ohp(at)pyrenet(dot)fr
> Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Rick Gigger <rick(at)alpinenetworking(dot)com>,
> pgsql-hackers(at)postgresql(dot)org
> Subject: Re: [HACKERS] should I worry?
>
> ohp(at)pyrenet(dot)fr wrote:
> > I'd love to find a query against pg_triggers giving the table name for
> > each RI_ConstraintTrigger_xxx.
>
> SELECT t.tgname, c.relname, tgconstrname FROM pg_trigger t, pg_class c
> WHERE t.tgrelid = c.oid AND tgname like 'RI_ConstraintTrigger_%';
>
>
Thanks! GREAT!
IIUC, I have drop every trigger like this :

SELECT t.tgname, c.relname, tgconstrname FROM pg_trigger t, pg_class c
WHERE t.tgrelid = c.oid AND tgname like 'RI_ConstraintTrigger_%'AND
tgconstrname = '<unnamed>';

and I delete all those ancient foreign key WITHOUT disturbing any others
Is that right?

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)

Heikki Linnakangas <heikki(at)enterprisedb(dot)com> writes:
> ohp(at)pyrenet(dot)fr wrote:
>> I'd love to find a query against pg_triggers giving the table name for
>> each RI_ConstraintTrigger_xxx.

> SELECT t.tgname, c.relname, tgconstrname FROM pg_trigger t, pg_class c
> WHERE t.tgrelid = c.oid AND tgname like 'RI_ConstraintTrigger_%';

Um ... that will find all triggers named like that, but I think Olivier
only wants to find the ones that are not attached to a constraint.
In HEAD it would do to add "... AND tgconstraint = 0" but in 8.2
I'm afraid he's got to dig through pg_depend ...

regards, tom lane

ohp(at)pyrenet(dot)fr writes:
> IIUC, I have drop every trigger like this :

> SELECT t.tgname, c.relname, tgconstrname FROM pg_trigger t, pg_class c
> WHERE t.tgrelid = c.oid AND tgname like 'RI_ConstraintTrigger_%'AND
> tgconstrname = '<unnamed>';

> and I delete all those ancient foreign key WITHOUT disturbing any others
> Is that right?

Not necessarily --- are you sure you don't have any real constraints
named "<unnamed>"?

However, if you do, the DROP TRIGGER command will just fail, so maybe
you don't need to bother with looking into pg_depend for yourself.

regards, tom lane

Dear Tom,
On Tue, 6 Nov 2007, Tom Lane wrote:

> Date: Tue, 06 Nov 2007 10:05:58 -0500
> 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>,
> Rick Gigger <rick(at)alpinenetworking(dot)com>,
> pgsql-hackers list <pgsql-hackers(at)postgresql(dot)org>
> Subject: Re: [HACKERS] should I worry?
>
> ohp(at)pyrenet(dot)fr writes:
> > IIUC, I have drop every trigger like this :
>
> > SELECT t.tgname, c.relname, tgconstrname FROM pg_trigger t, pg_class c
> > WHERE t.tgrelid = c.oid AND tgname like 'RI_ConstraintTrigger_%'AND
> > tgconstrname = '<unnamed>';
>
> > and I delete all those ancient foreign key WITHOUT disturbing any others
> > Is that right?
>
> Not necessarily --- are you sure you don't have any real constraints
> named "<unnamed>"?
>
yes
> However, if you do, the DROP TRIGGER command will just fail, so maybe
> you don't need to bother with looking into pg_depend for yourself.
>
> regards, tom lane
>
Thanks to your last sentence, I ended up with the rather crude script
below that works for me (expect a lot of errors)

DBNAME=xxx
for s in `psql -t ${DBNAME} << EOD
SELECT 'DROP TRIGGER "' || t.tgname || '" ON ' || c.relname || ';'
FROM pg_trigger t, pg_class c
WHERE t.tgrelid = c.oid AND tgname like 'RI_ConstraintTrigger_%'
EOD`
do
echo $s
done|psql ${DBNAME}

Could maybe help others like me....

What's your opinion?
--
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)