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@xxxxxxxxxxxxxxxxx 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 //
