Re: Stored Procedure and Trigger they puzzle me

Lars Heidieker 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)
>>>
>>> I think that is because your workid will be back at 25 in the next
>>> iteration when that happens, because of the self-reference.
>>>
>
> 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.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

--
Alban Hertroys
alban(at)magproductions(dot)nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //