From: Josh Berkus <josh@agliodbs.com>
To: pgsql-sql@postgresql.org
Subject: Is there any way to stop triggers from cycling?
Date: Wed, 8 Mar 2006 13:58:25 -0800
Message-ID: <200603081358.25185.josh@agliodbs.com> (raw)
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
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: josh@agliodbs.com
Subject: Re: Is there any way to stop triggers from cycling?
In-Reply-To: <200603081358.25185.josh@agliodbs.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