Re: Stored Procedure and Trigger they puzzle me

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


On 18 Dec 2006, at 09:26, Albe Laurenz wrote:

>> The strange thing is:
>> insert is OK (materialized path gets populated)
>> update of parent column is OK old values get delete and new ones get
>> inserted
>> but if the exception handling of the unique_violation exception is
>> removed an update on the id column fails, with
>> an duplicate pkey violation an the self reference in the
>> materialized
>> path eg for the values (25, 25)
>>
>> It works OK with ignoring the exception but why is the exception
>> thrown in the first place.
>
> Could you provide a sequence of INSERT and UPDATE statements
> that produce the problem you describe?
>
>

Currently I can only reproduce the following error if the exception
handling is removed.
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"

The table ltlocation is filled with:
id |      name       |  description  | parent | type
- ----+-----------------+---------------+--------+------
   1 | <i>location</i> | root location |        |    0
   2 | Images          |               |      1 |    0

ltlocationpath:
ltlocation_id | ltlocancester_id
- ---------------+------------------
              1 |                1
              2 |                1
              2 |                2

INSERT INTO ltlocation (id, parent, name, description, type) VALUES
(18999, 2, 'test', '', 0);
UPDATE ltlocation SET id = 45555 WHERE id = 18999;

Should produce it.
I am just trying out a few thing Alban Hertroys suggested to get an
better understanding of what is going on.

- --

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)

iD8DBQFFhxhNcxuYqjT7GRYRAlp5AKCnlzAXOCIWbWn7uUd6AUxVb9VAugCg05Kd
kb8Z12MrU2c6q9AB3z9Fzh8=
=y4Av
-----END PGP SIGNATURE-----