pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedIs there any way to stop triggers from cycling?
5+ messages / 4 participants
[nested] [flat]
* Is there any way to stop triggers from cycling?
@ 2006-03-08 21:58 Josh Berkus <josh@agliodbs.com>
0 siblings, 3 replies; 5+ messages in thread
From: Josh Berkus @ 2006-03-08 21:58 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Is there any way to stop triggers from cycling?
@ 2006-03-08 22:35 Rod Taylor <pg@rbt.ca>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 0 replies; 5+ messages in thread
From: Rod Taylor @ 2006-03-08 22:35 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-sql
> 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.
--
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Is there any way to stop triggers from cycling?
@ 2006-03-08 23:04 chester c young <chestercyoung@yahoo.com>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 1 reply; 5+ messages in thread
From: chester c young @ 2006-03-08 23:04 UTC (permalink / raw)
To: josh@agliodbs.com; pgsql-sql
trying to do this exlusively in triggers is a forray into folly.
take advantage of "instead of" or "do also" rules to create a compound
statement before your triggers do their work. (in terms of maintenance
and sanity, it's best if a trigger touches only its own record.)
as a handsweep example:
create view tree_v as select * from tree;
grant select, insert, update on tree_v to public;
create or replace rule 'tree_update' as
on update
to tree_v
do instead(
--
update tree set seq = seq+1
where old.pnt=new.pnt and old.seq<new.seq-1
and pnt = old.pnt and seq between old.seq and new.neq;
--
update tree set set = new.seq
where old.pnt=new.pnt and old.seq != new.seq
and pnt = old.pnt and seq = new.seq;
);
note two conditions on the where clause: first is rule when to do it,
and second is what record to do it on.
you might not need the intermediate view, but I always use a view
between my app and the table - for reasons like this and many, many
others.
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Is there any way to stop triggers from cycling?
@ 2006-03-08 23:29 Josh Berkus <josh@agliodbs.com>
parent: chester c young <chestercyoung@yahoo.com>
0 siblings, 0 replies; 5+ messages in thread
From: Josh Berkus @ 2006-03-08 23:29 UTC (permalink / raw)
To: chester c young <chestercyoung@yahoo.com>; +Cc: pgsql-sql
Chester,
> take advantage of "instead of" or "do also" rules to create a compound
> statement before your triggers do their work. (in terms of maintenance
> and sanity, it's best if a trigger touches only its own record.)
Ah, I see ... so:
1) create a view on the table
2) put a rule on the view to re-order, which re-orders the *table* so
there's no cascade
3) use the triggers to do the other tree-maintenance stuff, only for their
own rows/children (cascading triggers work *very* well for tree
maintenance).
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Is there any way to stop triggers from cycling?
@ 2006-03-09 10:21 Richard Huxton <dev@archonet.com>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 0 replies; 5+ messages in thread
From: Richard Huxton @ 2006-03-09 10:21 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-03-09 10:21 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-08 21:58 Is there any way to stop triggers from cycling? Josh Berkus <josh@agliodbs.com>
2006-03-08 22:35 ` Rod Taylor <pg@rbt.ca>
2006-03-08 23:04 ` chester c young <chestercyoung@yahoo.com>
2006-03-08 23:29 ` Josh Berkus <josh@agliodbs.com>
2006-03-09 10:21 ` Richard Huxton <dev@archonet.com>
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