From: Richard Huxton <dev@archonet.com>
To: josh@agliodbs.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Is there any way to stop triggers from cycling?
Date: Thu, 09 Mar 2006 10:21:13 +0000
Message-ID: <44100199.9060407@archonet.com> (raw)
In-Reply-To: <200603081358.25185.josh@agliodbs.com>
References: <200603081358.25185.josh@agliodbs.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, josh@agliodbs.com
Subject: Re: Is there any way to stop triggers from cycling?
In-Reply-To: <44100199.9060407@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox