-----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.)
- --
Viele Grüße,
Lars Heidieker
lars@xxxxxxxxxxxx
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)
iD8DBQFFh79UcxuYqjT7GRYRAu5WAKCXKY0GzAbSV5fxuBH6ANCddGVIWwCeJLcE
0vXHy8xr/Y54A9AYz95Aaqk=
=zRtO
-----END PGP SIGNATURE-----
