From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: pgsql-sql@postgresql.org
Subject: Is there any way to stop triggers from cycling?
Date: Wed, 8 Mar 2006 13:58:25 -0800
User-Agent: KMail/1.8
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200603081358.25185.josh@agliodbs.com>

Folks,

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.

The idea is that I want to set up a set of triggers such that:
a) If the user moves item (1) to item (3), then the existing items (2) and 
(3) will be "bumped down" to (1) and (2), or
b) if the user moves item (3) to item (1) then the existing items (1) and 
(2) will be "bumped up".

(btw, the reason I want to use triggers and not data-push functions is that 
the triggers are *much* more reliable for maintaining the tree fields)

I have a set of triggers that are working except for a problem with 
cycling.  What I'm looking for is a reliable, elegant way to make sure 
that the trigger is executed for each row only once.

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:

Given:

A	1
B	2
C	3

If I move A --> 3 then:
B moves to 1
C moves to 2 ... but *so does A* ...
and I end up with:

A	2
B	1
C	2

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?

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco