Re: should I worry?

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)