-----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@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)
iD8DBQFFh8shcxuYqjT7GRYRAtz+AJ42TizNIN13rOyGpKFjaXitxR3AdQCeI2RP
oFbKgeuD4vCDDBQAxxz4L/8=
=orBT
-----END PGP SIGNATURE-----
