no cascade triggers?

From: Chris Mair <chris(at)1006(dot)org>
To: pgsql-hackers(at)postgresql(dot)org
Cc: postgresql-it(at)lists(dot)psql(dot)it
Subject: no cascade triggers?
Date: 2007-06-26 14:04:28
Message-ID: 46811CEC.7040704@1006.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello,

triggers in PostgreSQL are cascading by default. From section 34.1.
of the 8.2 manual:

"If a trigger function executes SQL commands then these commands
may fire triggers again. This is known as cascading triggers.
There is no direct limitation on the number of cascade levels.
It is possible for cascades to cause a recursive invocation of
the same trigger; for example, an INSERT trigger might execute
a command that inserts an additional row into the same table,
causing the INSERT trigger to be fired again. It is the trigger
programmer's responsibility to avoid infinite recursion in such
scenarios."

On the italian list we're discussing a case were a user reportedly
worked around this (i.e. got rid of unwanted cascading calls) by
writing an on insert trigger procedure something on the lines of:

ALTER TABLE tab DISABLE TRIGGER USER;
-- do more inserts into the same table
ALTER TABLE tab ENABLE TRIGGER USER;

While this reporetedly worked well in 8.2.1 it does not in 8.2.4
resulting in an error:

ERROR: relation "distinta_base1" is being used by active queries
in this session Stato SQL: 55006

Now -- while we agree that disabling a trigger from inside itself
is a somewhat strange thing to do, we cannot see a good and easy
solution to the problem (of avoiding cascading trigger calls).

General question: would a "no cascade" clause for triggers be a
todo item?

Special question: any recomendations for our user? He has a somewhat
large number of triggers that do the alter table trick - working
around it by means of some context-based logic would be a lot of
work...

Bye :)
Chris.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2007-06-26 14:12:16 Re: Frustrating issue with PGXS
Previous Message Gregory Stark 2007-06-26 13:49:51 Re: [COMMITTERS] pgsql: Arrange for quote_identifier() and pg_dump to not quote keywords