Message-ID: <44100199.9060407@archonet.com>
Date: Thu, 09 Mar 2006 10:21:13 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 1.5 (X11/20051201)
MIME-Version: 1.0
To: josh@agliodbs.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Is there any way to stop triggers from cycling?
References: <200603081358.25185.josh@agliodbs.com>
In-Reply-To: <200603081358.25185.josh@agliodbs.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Josh Berkus wrote:
> 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:

One more addition to Rod/Chester's comments...

It strikes me that the root of this problem is that you're trying to 
maintain the condition that sortorder is unique while breaking that 
condition by setting A=>3 while C=>3. Hence Rod's delete/insert matches 
what you're doing (delete, shuffle up to fill gap, insert with A=>3).

If you counted sortorder in steps (e.g. 10,20,30) then you could set 
A=35 and it would be clear what order you wanted.

Since the shuffled row is "unusual" (it isn't divisible by 10) you then 
can do one of two things:
1. If NEW.sortorder % 10 <> 0 THEN ...
Compare OLD.sortorder, NEW.sortorder and shuffle rows between to fill 
the gap.
Then alter your NEW.sortorder to be a "rounded" number (30).

2. A post-update statement trigger could do the whole thing by looking 
at the table as a whole. Might be useful if you do multiple re-ordering 
on a small table.

-- 
   Richard Huxton
   Archonet Ltd