Re: Stored Procedure and Trigger they puzzle me

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