Making AFTER triggers act properly in PL functions - Mailing list pgsql-hackers

Here's another thing that I think it would be good to fix in 8.0.
We've had numerous complaints before about RI constraints not firing
inside PL functions; a recent one is
http://archives.postgresql.org/pgsql-bugs/2004-09/msg00020.php

I think also that the current behavior violates the SQL spec.
SQL99 4.17 says:
        The checking of a constraint depends on its constraint mode within        the current SQL-transaction. If the
constraintmode is immediate,        then the constraint is effectively checked at the end of each SQL-
statement.
        NOTE 13 - This includes SQL-statements that are executed as a        direct result or an indirect result of
executinga different SQL-        statement.
 

The NOTE is a bit opaque but I think it has to be taken as meaning that
if a PL function contains DML commands then the results of those DML
commands are checked when they complete, not when the outermost statement
completes.

Therefore, I think the behavior we want is that immediate-mode AFTER
triggers fire at completion of the statement that queued them, no matter
whether this was an interactive statement or one inside a function.
Deferred triggers, as now, fire only at end of top-level transaction
(or when changed to immediate mode by SET CONSTRAINTS).  Changing this
obviously risks breaking existing applications, but we'll have to do it
eventually.  8.0 seems like the appropriate time.

Implementation proposal:

I was originally thinking we could just invoke DeferredTriggerEndQuery
after each query is finished, but that doesn't work.  Imagine the
situation where a PL function is being called repeatedly by an UPDATE
query (which is generating trigger events), and the PL function is doing
queries that themselves fire triggers.  We want completion of a query
within the PL function to fire only those events queued by that query,
not events already queued by the outer UPDATE.  The outer UPDATE's events
should be processed only when it finishes.

To handle this, I think we need to introduce a DeferredTriggerBeginQuery
function to match DeferredTriggerEndQuery, and make SPI and other callers
of the executor essentially do
DeferredTriggerBeginQuery()ExecutorStart()ExecutorRun()ExecutorEnd()DeferredTriggerEndQuery()

trigger.c will have to be prepared to cope with nested begin/end query
calls.  I envision it working like this:

* BeginQuery establishes a new, empty list of AFTER triggers for the
new query.  This is pushed onto a stack of active queries for the current
(sub)transaction.

* In the executor, any request to queue an AFTER trigger event queues it
onto the topmost query's list.  (Error if no active query, so you MUST
have done DeferredTriggerBeginQuery.)

* EndQuery processes and discards immediate-mode AFTER trigger events for the
current query.  Any remaining events (ie, DEFERRED triggers) are appended
to the current (sub)transaction's list of pending deferred triggers.
Note that even inside a subtransaction, we can discard immediate-mode
events.

* EndSubXact, in the commit case, expects the stack of open queries for
the current subxact to be empty (error if not); in the abort case, pop
and discard any open queries, along with any deferred triggers of the
subxact.

* EndXact and DeferredTriggerSetState continue to act the same as before.
In particular, DeferredTriggerSetState need pay no attention to trigger
events that are still in lists belonging to open queries; those events
aren't ready to fire yet.

Comments?
        regards, tom lane