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

On Sat, 3 Nov 2007, Tom Lane wrote:

> Date: Sat, 03 Nov 2007 12:42:24 -0400
> From: Tom Lane <tgl@sss.pgh.pa.us>
> To: ohp@pyrenet.fr
> Cc: Heikki Linnakangas <heikki@enterprisedb.com>,
>      pgsql-hackers list <pgsql-hackers@postgresql.org>
> Subject: Re: [HACKERS] should I worry?
>
> ohp@pyrenet.fr writes:
> > 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@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)