From: | Lars Heidieker <lars(at)heidieker(dot)de> |
---|---|
To: | Lars Heidieker <lars(at)heidieker(dot)de> |
Cc: | Alban Hertroys <alban(at)magproductions(dot)nl>, pgsql-general(at)postgresql(dot)org, Albe Laurenz <all(at)adv(dot)magwien(dot)gv(dot)at> |
Subject: | Re: Stored Procedure and Trigger they puzzle me |
Date: | 2006-12-19 11:21:04 |
Message-ID: | 165A38CA-29DC-48B9-9170-F0DFE34DC261@heidieker.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)heidieker(dot)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-----
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2006-12-19 11:25:27 | Re: Second attempt, roll your own autovacuum |
Previous Message | Lars Heidieker | 2006-12-19 10:30:44 | Re: Stored Procedure and Trigger they puzzle me |