Re: Transaction callback - Mailing list pgsql-hackers

What I want accomplish has been very well defined already. I'm thinking of
javax.transaction.Transaction and javax.transaction.Synchronized, two
interfaces readily available in any J2EE application server. The actions
that can be performed by implementers of the latter interface (the one that
gets the suggested callbacks) are of course limited, especially during
post-commit. Nevertheless, they are powerful and can be used in a variety of
ways. Some examples.

While its true that a post-commit operation cannot modify a database, it
might still for instance propagate the outcome of the transaction to another
resource outside of the database, it might invalidate a transaction local
in-memory cache, or it might clear resources occupied in memory for a rule
system, etc. If status in a post-commit is preparing, an exceptional
condition could be interpreted as "vote rollback".

A pre-commit operation should be able to do lot's of interesting read-only
operations on the database. Deferred constraints and rule validation comes
to mind. It should be able to throw an exception and thus cause a rollback.

Some functionality must of course be present that prevents user-written code
form doing things that it's not supposed to (like changing things). That's
part of my original question really, "how hard is it to implement?".

Regards,

Thomas Hallgren

"David Helgason" <david@uti.is> wrote in message
news:FB829D41-5422-11D8-B0BC-000A9566DA8A@uti.is...
> On 31. jan 2004, at 18:53, Tom Lane wrote:
> > "Thomas Hallgren" <thhal@mailblocks.com> writes:
> >> Ideally, I'd like a "beforeCompletion" that is executed prior to the
> >> start
> >> of the commit process and a "afterCompletion" that is called when the
> >> transaction is commited. The latter would have a status flag
> >> indicating if
> >> status is "prepared" (to support 2-phase commits), "commited", or
> >> "rolled
> >> back".
> >
> > And what exactly would this callback do?
>
> I imagine this would be to enforce that constraints are kept. FOREIGN
> KEYs can be deferred, and simple CHECK constrains can be simulated with
> clever foreign keys to dummy tables. Possibly allowing CHECK
> constraints to be deferred alleviate the need for this?
>
> > The transaction commit sequence is sufficiently delicate that I'm not
> > interested in any proposals to call random user-written code in it.
> > The notion of a post-commit callback is even more problematic --- what
> > is it going to do at all?  It cannot modify the database, and it cannot
> > do anything that risks getting an error, which seems to leave mighty
> > little scope for useful activity.
>
> Surely this wouldn't effect the commit sequence. Post-commit actions
> could be just like cronjobs, but which are run as soon as there is a
> known need for them (and not otherwise).
>
> Ideally triggered triggers could install pre-commit actions during the
> transaction. The trigger knows:
> * after this particular insert/update some database logic that cannot
> be codified into a foreign key constraint is in an inconsistent state
> and must not be committed unless we are sure that some other action
> happened later
>
> I've had several cases of needing sth like this, but always could hack
> a solution using several triggers and dummy tables that I could put
> into an illegal state (with a deferred foreign key). Later another
> trigger took that table out of the illegal state if the right action
> was performed.
>
> David Helgason
> Over the Edge Entertainments
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>