From: Rod Taylor <pg@rbt.ca>
To: josh@agliodbs.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Is there any way to stop triggers from cycling?
Date: Wed, 08 Mar 2006 17:35:50 -0500
Message-ID: <1141857350.51115.40.camel@home> (raw)
In-Reply-To: <200603081358.25185.josh@agliodbs.com>
References: <200603081358.25185.josh@agliodbs.com>
> 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.
--
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: pg@rbt.ca, josh@agliodbs.com
Subject: Re: Is there any way to stop triggers from cycling?
In-Reply-To: <1141857350.51115.40.camel@home>
* 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