Re: [GENERAL] Cascading Trigger Prevention
On Nov 29, 5:09 pm, [EMAIL PROTECTED] (Martijn van Oosterhout) wrote: > On Wed, Nov 28, 2007 at 02:00:58PM -0800, JonXP wrote: > > I have a table that contains a nested set (using paths), and I'm > > trying to create a trigger that updates the timestamps of a node and > > all of its parents on a modification. Unfortunately, this is causing > > infinitely recurring triggers. > > > The insert occurs, which fires the trigger, which then updates all of > > the parent rows, which then fires the trigger again for each update, > > which recurses yet again. > > You can disable triggers on a table but it's definitly not recommended > (deadlock prone) but it seems to me that if when the trigger is fired > it only updates its parent everything should work, right? As it > recurses up the tree eventually it reaches the end, surely? > > Have a nice day, > -- > Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > > > Those who make peaceful revolution impossible will make violent revolution > > inevitable. > > -- John F Kennedy > > > > signature.asc > 1KDownload Yes, that was one of two approaches I am considering taking. I was trying to update all of the parent nodes in one query (which is why I used this particular method of nested sets) but just recursing up the tree could also work. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Cascading Trigger Prevention
On Wed, Nov 28, 2007 at 02:00:58PM -0800, JonXP wrote: > I have a table that contains a nested set (using paths), and I'm > trying to create a trigger that updates the timestamps of a node and > all of its parents on a modification. Unfortunately, this is causing > infinitely recurring triggers. > > The insert occurs, which fires the trigger, which then updates all of > the parent rows, which then fires the trigger again for each update, > which recurses yet again. You can disable triggers on a table but it's definitly not recommended (deadlock prone) but it seems to me that if when the trigger is fired it only updates its parent everything should work, right? As it recurses up the tree eventually it reaches the end, surely? Have a nice day, -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > Those who make peaceful revolution impossible will make violent revolution > inevitable. > -- John F Kennedy signature.asc Description: Digital signature
[GENERAL] Cascading Trigger Prevention
Hello All, I have a table that contains a nested set (using paths), and I'm trying to create a trigger that updates the timestamps of a node and all of its parents on a modification. Unfortunately, this is causing infinitely recurring triggers. The insert occurs, which fires the trigger, which then updates all of the parent rows, which then fires the trigger again for each update, which recurses yet again. I have been looking with no success for a way to turn off triggers, and am surprised that there is no method of preventing a trigger from running when performing a query. Even more bothersome is the fact that a trigger has no way of knowing when it has been called by another trigger. I don't want to have to move my modified timestamps to a separate table just so that the triggers don't recurse themselves, but this is looking like it will be the case. Does anyone have any thoughts or alternate suggestions? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq