Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group