From: | Rod Taylor <pg(at)rbt(dot)ca> |
---|---|
To: | josh(at)agliodbs(dot)com |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Is there any way to stop triggers from cycling? |
Date: | 2006-03-08 22:35:50 |
Message-ID: | 1141857350.51115.40.camel@home |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
--
From | Date | Subject | |
---|---|---|---|
Next Message | chester c young | 2006-03-08 23:04:18 | Re: Is there any way to stop triggers from cycling? |
Previous Message | Josh Berkus | 2006-03-08 21:58:25 | Is there any way to stop triggers from cycling? |