Hi, I'm new to this list.

Having got some insight into the PostgreSQL sources by following
server action using GDB and after trying to implement some features I
became able to formulate a proposal which IMHO could be useful for
others, too.

The proposal consists in generalizing the PostgreSQL deferred trigger
mechanism to more general types of events. Such an event could be,
e.g., a function call deferred up to transaction end.

One use case would be a PostgreSQL procedure `mark_tx_rollback()' to
mark a transaction for rollback. The rollback will be performed at
transaction commit time. (Actually `mark_tx_rollback()' can be
implemented by faking a `ResultRelInfo' and calling
`ExecARInsertTriggers' or similar functions with the faked
`ResultRelInfo' but this is not the cleanest way and will not work for
other use cases.)

The implementation would be straightforward, changes have to be made
only to `trigger.c' and `trigger.h'. A data structure
`DeferredEventData' must be added, from which as well the existing
`DeferredTriggerEventData' as also `DeferredFunctionCallEvent' will be
derived. The implementation of `deferredTriggerInvokeEvents(bool)'
must be changed to separate DeferredTriggerEvent's from
DeferredFunctionCallEvent's.  A function `DeferredFunctionCallExecute'
called from `deferredTriggerInvokeEvents(bool)' in the case of a
`DeferredFunctionCallEvent' and functions to add
DeferredFunctionCallEvent's to the queue have to be added.

I would be able to make the necessary changes and post the patch (to
whom ?). Is it OK ? Are there others working on similar or
contradicting features ? Does my proposal conform to the further plans
of PostgreSQL development ?

-- 
Holger Krug
hkrug@rationalizer.com

Wed, 2 Jan 2002 10:26:52 +0100
Holger Krug <hkrug@rationalizer.com>
Feature proposal: generalizing deferred trigger events
Holger Krug <hkrug@rationalizer.com> writes:
> The proposal consists in generalizing the PostgreSQL deferred trigger
> mechanism to more general types of events.

While I have no inherent objection to that, I'm not seeing what it's
good for either.  What SQL feature would be associated with this?

> One use case would be a PostgreSQL procedure `mark_tx_rollback()' to
> mark a transaction for rollback.

This is unconvincing, since Postgres has no need for rollback procedures
(and I personally will disagree with any attempt to introduce 'em; if
you need one then you have a problem doing crash recovery).

			regards, tom lane

Wed, 02 Jan 2002 11:31:16 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Feature proposal: generalizing deferred trigger events 
On Wed, Jan 02, 2002 at 11:31:16AM -0500, Tom Lane wrote:
> Holger Krug <hkrug@rationalizer.com> writes:
> > The proposal consists in generalizing the PostgreSQL deferred trigger
> > mechanism to more general types of events.
> 
> While I have no inherent objection to that, I'm not seeing what it's
> good for either.  What SQL feature would be associated with this?

The use I want to make of this feature is to collect errors during a
transaction, report them back to the user (in my case an application
server) and rollback the transaction when the user tries to commit.
It's not a SQL feature, but nevertheless useful in some cases, because
you can collect several errors within one transaction and are not
fobbed off with only one.

> > One use case would be a PostgreSQL procedure `mark_tx_rollback()' to
> > mark a transaction for rollback.
> 
> This is unconvincing, since Postgres has no need for rollback procedures
> (and I personally will disagree with any attempt to introduce 'em; if
> you need one then you have a problem doing crash recovery).

I think you misunderstand me. `mark_tx_rollback()', when called, would
add an entry to the deferred trigger queue, which is called at commit
time *before* the transaction closes and causes transaction rollback
(and not more). All is done *within one transaction*, not after the
transaction has finished.

Are there any other ways to add transaction end hooks
(i.e. functions to be called immediately *before* the transaction
closes) ? I would use them.

-- 
Holger Krug
hkrug@rationalizer.com

Wed, 2 Jan 2002 18:03:41 +0100
Holger Krug <hkrug@rationalizer.com>
Re: Feature proposal: generalizing deferred trigger events
Holger Krug <hkrug@rationalizer.com> writes:
> On Wed, Jan 02, 2002 at 11:31:16AM -0500, Tom Lane wrote:
>> While I have no inherent objection to that, I'm not seeing what it's
>> good for either.  What SQL feature would be associated with this?

> The use I want to make of this feature is to collect errors during a
> transaction, report them back to the user (in my case an application
> server) and rollback the transaction when the user tries to commit.

Hmm.  So basically your code is saying "I want to abort the current
transaction, but not right now --- I can wait till the end to force
abort."  Okay.  Of course, if some other code causes a transaction
abort, your deferred trigger will never get run, but maybe that's
all right in your situation.

> Are there any other ways to add transaction end hooks
> (i.e. functions to be called immediately *before* the transaction
> closes) ? I would use them.

I don't think so.  I've occasionally thought that xact.c should have
a modifiable data structure instead of a hard-wired list of action
subroutines to call.  But in practice, there are usually a bunch of
constraints on the order that things must be done in, so just "add
another commit-time function call" would not be an adequate API for
adding to the list.  You'd need to be able to specify "before these
guys, after those guys" in some way.

In your example, it would seem that you'd want the force-an-abort
subroutine to be called at the last possible instant before we'd
otherwise commit, so as to allow as many potential errors as possible
to be detected and reported.  In particular, if you just throw it into
the deferred trigger list at a random time, then only the other deferred
triggers that are queued before it will have an opportunity to detect
errors.  Seems like that is not really the behavior you want.

			regards, tom lane

Wed, 02 Jan 2002 12:16:14 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Feature proposal: generalizing deferred trigger events 
On Wed, Jan 02, 2002 at 12:16:14PM -0500, Tom Lane wrote:

> Of course, if some other code causes a transaction
> abort, your deferred trigger will never get run, but maybe that's
> all right in your situation.

It is because I use special triggers for referential integrity checks etc..

> In your example, it would seem that you'd want the force-an-abort
> subroutine to be called at the last possible instant before we'd
> otherwise commit, so as to allow as many potential errors as possible
> to be detected and reported.  In particular, if you just throw it into
> the deferred trigger list at a random time, then only the other deferred
> triggers that are queued before it will have an opportunity to detect
> errors.  Seems like that is not really the behavior you want.

You're right, I oversimplified. I planned some ugly hacks within my
triggers to "solve" this problem. It would be better to introduce
priority levels for triggers. But this involves some deeper changes of
the implementation in `trigger.c' (not necessarily of the interface),
which I feared not to get the OK for.

If I would do so the following questions should be agreed on:

1) One trigger list for all priority levels or several lists for different
   levels ?
2) A small number of priority levels or something like int16 ?

There is even a second problem with my approach: If many errors occur,
the rollback trigger is added many times to the deferred trigger
list. This is really nonsense. I can circumvent this, by holding the
xid of the last call as a static variable within the procedure adding
the rollback trigger.

-- 
Holger Krug
hkrug@rationalizer.com

Wed, 2 Jan 2002 18:58:31 +0100
Holger Krug <hkrug@rationalizer.com>
Re: Feature proposal: generalizing deferred trigger events
On Wed, Jan 02, 2002 at 12:16:14PM -0500, Tom Lane wrote:

Hey, I got an SQL feature for you: One could use those generalized
deferred triggers to implement:

CREATE TEMP TABLE <table> ( ... ) ON COMMIT DELETE ROWS

Simply add a not deferred trigger on insertion to <table>, which
checks if it was already called in the same transaction (using a
hard-coded transaction id as static variable) and if not, adds a
generalized clean-up trigger which truncates <table>. (Shall triggers
ON DELETE for <table> be called in this case or not ?)

-- 
Holger Krug
hkrug@rationalizer.com

Wed, 2 Jan 2002 19:18:06 +0100
Holger Krug <hkrug@rationalizer.com>
Re: Feature proposal: generalizing deferred trigger events