-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On 19 Dec 2006, at 08:45, Albe Laurenz wrote:
With your examples I can reproduce the error and explain what is going
on.
test=> select * from ltlocation;
id | name | description | parent | type
-------+-----------------+---------------+--------+------
1 | <i>location</i> | root location | | 0
2 | Images | | 1 | 0
18999 | test | | 2 | 0
test=> select * from ltlocationpath;
ltlocation_id | ltlocancester_id
---------------+------------------
1 | 1
2 | 2
2 | 1
18999 | 18999
18999 | 2
18999 | 1
(6 rows)
test=> UPDATE ltlocation SET id = 45555 WHERE id = 18999;
NOTICE: An exception! new.id = 45555, workid = 45555
NOTICE: An exception! new.id = 45555, workid = 2
NOTICE: An exception! new.id = 45555, workid = 1
UPDATE 1
Here is a chronological description of what takes place when you
do the update:
- 'id' in ltlocation is changed from 18999 to 45555.
- The foreign key ltlocancester_fkey, which is defined as
ON UPDATE CASCADE changes 'ltlocancester_id' in table
ltlocationpath from 18999 to 45555 in one record.
- The foreign key ltlocation_fkey, which is defined as
ON UPDATE CASCADE changes 'ltlocation_id' in table
ltlocationpath from 18999 to 45555 in three record.
- Trigger 'ltlocationpathtrigger' fires and does the following:
- DELETE FROM ltlocationpath WHERE ltlocation_id = 18999
This does not match any record in ltlocationpath, 0 records
are deleted.
- INSERT INTO ltlocationpath (ltlocation_id, ltlocancester_id)
VALUES (45555, 45555)
This violates the primary key on ltlocationpath since there
is also such a record.
- INSERT INTO ltlocationpath (ltlocation_id, ltlocancester_id)
VALUES (45555, 2)
This violates the primary key on ltlocationpath since there
is also such a record.
- INSERT INTO ltlocationpath (ltlocation_id, ltlocancester_id)
VALUES (45555, 1)
This violates the primary key on ltlocationpath since there
is also such a record.
Essentially, you're doing the same thing twice, once through the
foreign key constraint, and once in the trigger function.
Yours,
Laurenz Albe
---------------------------(end of
broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Thanks, this explains what is going on.
I had the thought that it might be some bad interaction between the
trigger and foreign key constraint.
So it makes sense to rewrite the trigger to take only care of those
cases that aren't handled by the cascading foreign keys.
- --
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)
iD8DBQFFh71ScxuYqjT7GRYRAit9AKCXIVHx28D1V1VURBuqCWdKzcXSQQCgy6yq
ne2AYpvhd6CPAfPfP8Ll1qw=
=/w5f
-----END PGP SIGNATURE-----
