Postgres Pro
Downloads
Home   >   mailing lists

Thread: avoiding endless loop in an UPDATE trigger

avoiding endless loop in an UPDATE trigger

From
"Norman J. Clarke"
Date:
Hello,

I am attempting to write a trigger function or rule in pl/pgsql that runs
on UPDATE to a table named "nodes". From inside this trigger, I would like
to UPDATE the same "nodes" table. How can I do this without entering into
an endless loop?

At the moment, I have a trigger which does the following:

<snip>
UPDATE pg_class SET reltriggers = 0 WHERE relname = ''nodes'';
</snip>

At the end of the function, I again update pg_class.reltriggers to its
previous value, thus reenabling triggers.

The problem with this is that it disables all of my FOREIGN KEY
constraints during function execution, which is undesirable.

I am sure there must be a more elegant way to accomplish this, but I have
not found it. Could anyone offer some advice?

Thanks!

Regards,

Norm

--------------------------------------
Norman Clarke
Combimatrix Corp Software Development
Harbour Pointe Tech Center
6500 Harbour Heights Pkwy, Suite 301
Mukilteo, WA 98275

tel: 425.493.2240
fax: 425.493.2010
--------------------------------------

Re: avoiding endless loop in an UPDATE trigger

From
"Dominic J. Eidson"
Date:
On Mon, 26 Feb 2001, Norman J. Clarke wrote:

> The problem with this is that it disables all of my FOREIGN KEY
> constraints during function execution, which is undesirable.

Foreign Key constraints are implemented as triggers, IIRC. I'll let you
figure out the implication(s) yourself.


--
Dominic J. Eidson
                                        "Baruk Khazad! Khazad ai-menu!" - Gimli
-------------------------------------------------------------------------------
http://www.the-infinite.org/              http://www.the-infinite.org/~dominic/

Re: avoiding endless loop in an UPDATE trigger

From
"Norman J. Clarke"
Date:
Right. I already figured this out, which is why in my email I said "the
problem with this is..."

If you have any suggestions on how to avoid getting into this kind of loop
without disabling triggers (so that my foreign keys still work) I'd very
much appreciate your suggestions.

Thanks!

Norman


On Mon, 26 Feb 2001, Dominic J. Eidson wrote:

> On Mon, 26 Feb 2001, Norman J. Clarke wrote:
>
> > The problem with this is that it disables all of my FOREIGN KEY
> > constraints during function execution, which is undesirable.
>
> Foreign Key constraints are implemented as triggers, IIRC. I'll let you
> figure out the implication(s) yourself.
>
>
>

Re: avoiding endless loop in an UPDATE trigger

From
Louis-David Mitterrand
Date:
On Mon, Feb 26, 2001 at 11:53:51AM -0800, Norman J. Clarke wrote:
> Hello,
>
> I am attempting to write a trigger function or rule in pl/pgsql that runs
> on UPDATE to a table named "nodes". From inside this trigger, I would like
> to UPDATE the same "nodes" table. How can I do this without entering into
> an endless loop?

What I did is add a no_update bool to the table and test:

if new.no_update = true then
    new.no_update = false;
else
    <do your update here>
    new.no_update = true;
endif

--
Jesus is coming! Everyone look busy!

Re: avoiding endless loop in an UPDATE trigger

From
Louis-David Mitterrand
Date:
On Mon, Feb 26, 2001 at 11:53:51AM -0800, Norman J. Clarke wrote:
> Hello,
>
> I am attempting to write a trigger function or rule in pl/pgsql that runs
> on UPDATE to a table named "nodes". From inside this trigger, I would like
> to UPDATE the same "nodes" table. How can I do this without entering into
> an endless loop?

What I did is add a no_update bool to the table and test:

if new.no_update = true then
    new.no_update = false;
else
    <do your update here>
    new.no_update = true;
endif

--
Observe, reason, and experiment.
 (if you're too dumb, just pray)