Thread: Feature proposal: generalizing deferred trigger events
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
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
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
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
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
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