Re: Stored Procedure and Trigger they puzzle me - Mailing list pgsql-general
From | Lars Heidieker |
---|---|
Subject | Re: Stored Procedure and Trigger they puzzle me |
Date | |
Msg-id | 165A38CA-29DC-48B9-9170-F0DFE34DC261@heidieker.de Whole thread Raw |
In response to | Re: Stored Procedure and Trigger they puzzle me (Lars Heidieker <lars@heidieker.de>) |
List | pgsql-general |
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 19 Dec 2006, at 10:30, Lars Heidieker wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > > On 19 Dec 2006, at 08:56, Alban Hertroys wrote: > >>> >>> Not sure as I deleted them before, but currently I cant reproduce >>> it. >>> I just get the following now: >>> ERROR: insert or update on table "ltlocationpath" violates >>> foreign key >>> constraint "ltlocancester_fkey" >>> DETAIL: Key (ltlocancester_id)=(18999) is not present in table >>> "ltlocation". >>> CONTEXT: SQL statement "UPDATE ONLY "public"."ltlocationpath" SET >>> "ltlocation_id" = $1 WHERE "ltlocation_id" = $2" >> >> Looks like a locationpath being inserted with an invalid >> ltlocancestorid; probably the first or the last record inserted is >> wrong. >> >>> on: UPDATE ltlocation SET id = 45555 WHERE id = 18999; >> >> Not something that will actually happen in your application, I bet >> (what's the point of modifying an artificial key?); no reason it >> shouldn't work, though. >> >>> which I don't get if: >>> IF tg_op = 'UPDATE' THEN >>> DELETE FROM ltlocationpath WHERE ltlocation_id = old.id; >>> END IF; >>> is executed. >> >> I didn't suggest to remove that block, though it can probably be >> handled >> more elegantly (fe. only if a column referenced by ltlocationpath >> changed). I left it out because it wasn't part of what I tried to >> explain. >> >>> Probably I am running in some bad interaction between triggers and >>> foreign key constraints (cascading) >>> I'll just continue to play around to get a better understanding. >> > > Thanks, yes the ltlocancester_id is invalid as it was changed by > the foreign key constraint before, > in the end it turned out to be some bad interaction between the > trigger and the foreign key constraint, > as Albe Laurenz found out. > That's where I got confused. > > (Yes, updating the primary key doesn't happen, but I thought the > trigger should be able to handle that.) > Actually I just figured out, that is is not the trigger but the two cascade on update triggers collide. It happens also without the trigger: lt=# UPDATE ltlocation SET id = 45555 WHERE id = 18999; ERROR: insert or update on table "ltlocationpath" violates foreign key constraint "ltlocancester_fkey" DETAIL: Key (ltlocancester_id)=(18999) is not present in table "ltlocation". CONTEXT: SQL statement "UPDATE ONLY "public"."ltlocationpath" SET "ltlocation_id" = $1 WHERE "ltlocation_id" = $2" I think the two foreign key constraints together make it impossible to change the primary key (which isn't needed), as they would have to run "as one" which they can't... - -- Viele Grüße, Lars Heidieker lars@heidieker.de http://paradoxon.info - ------------------------------------ Mystische Erklärungen. Die mystischen Erklärungen gelten für tief; die Wahrheit ist, dass sie noch nicht einmal oberflächlich sind. -- Friedrich Nietzsche -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.5 (Darwin) iD8DBQFFh8shcxuYqjT7GRYRAtz+AJ42TizNIN13rOyGpKFjaXitxR3AdQCeI2RP oFbKgeuD4vCDDBQAxxz4L/8= =orBT -----END PGP SIGNATURE-----
pgsql-general by date: