From: Rod Taylor <pg@rbt.ca>
To: josh@agliodbs.com
Cc: pgsql-sql@postgresql.org
In-Reply-To: <200603081358.25185.josh@agliodbs.com>
References: <200603081358.25185.josh@agliodbs.com>
Date: Wed, 08 Mar 2006 17:35:50 -0500
Message-Id: <1141857350.51115.40.camel@home>
Mime-Version: 1.0
Subject: Re: Is there any way to stop triggers from cycling?
Content-Type: text/plain
Content-Transfer-Encoding: 7bit


> I'm experimenting with a set of triggers to automagically maintain 
> ltrees-organized tables.  I almost have it working, except for a pesky 
> problem with re-ordering groups.

> Currently I'm doing this by only cascade-updating the row adjacent to
the 
> one I'm moving.  However, this is resulting in a cycle, and I don't
see 
> how to break it.  Namely:

> So I'm trying to come up with a way to ensure that each row is visited only 
> once, but it doesn't seem to be possible.  Ideas?

I've played this game. Not elegant, but workable. Don't use an update
trigger.

Have an Insert trigger. From the client do a DELETE and INSERT to move A
to 3 instead of an update.

Within that trigger use updates -- thus no cascade.


Option #2 is equally un-elegant and works best for a 'session' flag. Use
a sequences state as a boolean value.

Have trigger #1 grab a value from the sequence and fix all of the data.

Have the cascaded triggers use a PG_TRY {} to determine if it can
successfully call currval() or not. If it can, then the trigger has
already run. If not, then it should do the work.


Option #3, probably better than #2 but I've not used it before: declare
a standard named cursor.

If the cursor exists then your cascaded triggers can read it for the
work that they should do (nothing in this case) (test with PG_TRY{}).

If the cursor does not exist then the trigger should make a cursor with
instructions, do the work (cascades to sub-triggers), and remove the
cursor.

Named cursors are better than temporary tables because they don't cause
system table bloat.
--