I would like to register a "transaction callback" in the backend. Don't
think it's possible today and my question is, how hard would it be to
implement something that would enable this registration?

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".

A related issue is the ability to register a callback that is executed when
the connection is first established. Is that possible today? If so, how do I
register the function to be called?

Regards,

Thomas Hallgren



Sat, 31 Jan 2004 17:41:14 +0100
"Thomas Hallgren" <thhal@mailblocks.com>
Transaction callback
"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?

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.

			regards, tom lane

Sat, 31 Jan 2004 12:53:56 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Transaction callback 
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?
> 
> 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.

Why can't we call the callback before we commit so it can modify the
database?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Sat, 31 Jan 2004 14:08:01 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Transaction callback
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


Sat, 31 Jan 2004 20:23:43 +0100
David Helgason <david@uti.is>
Re: Transaction callback 
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Why can't we call the callback before we commit so it can modify the
> database?

He wanted that too...

A user callback before we start the commit sequence seems doable,
although there's some question in my mind of exactly when it should
happen.  The last point at which it'd really be safe to execute
arbitrary actions is just before DeferredTriggerEndXact() --- what
if you make a database change that fires deferred triggers?  Surely
those would have to be executed before we can commit.  As you move
further down in the sequence, successively larger chunks of
functionality become unsafe to invoke.  But depending on what you
want the callback for, you might not want all that stuff running
after you get called; all of it can potentially cause errors and thereby
prevent the commit from occurring.

I got the impression that Thomas wanted this in order to kluge up
some kind of two-phase-commit support, in which case he really needs
to get control at the point where we're just about to really truly
commit (ie, write the commit WAL record).  That's certainly not a
location where we want random users to be inserting code; as such
I don't think that exposing a callback hook is the right answer.
My advice to him is to go in and change the code.

			regards, tom lane

Sat, 31 Jan 2004 14:26:36 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Transaction callback 
Tom Lane wrote:
> I got the impression that Thomas wanted this in order to kluge up
> some kind of two-phase-commit support, in which case he really needs
> to get control at the point where we're just about to really truly
> commit (ie, write the commit WAL record).  That's certainly not a
> location where we want random users to be inserting code; as such
> I don't think that exposing a callback hook is the right answer.
> My advice to him is to go in and change the code.

Yes, it can't be used for more advanced stuff like 2-phase commit.  I
know some were asking for callbacks on (clean) session exit, and it
seemed like a natural extension.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Sat, 31 Jan 2004 14:44:32 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Transaction callback
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
>



Sat, 31 Jan 2004 23:51:31 +0100
"Thomas Hallgren" <thhal@mailblocks.com>
Re: Transaction callback
 I don't want to kludge up a 2-phase commit support. Any attempt to provide
that must be extremely well thought through :-)

But I would like to know more about PostgreSQL capabilities in the area.
What kind of XA-support is there? Can I read about this somewhere? (I'm
working on an Open Source high-end appserver bundling offer and would of
course like to promote PostgreSQL)

I'm not sure what Tom means when he says "go in and change the code". What
code?

- thomas

"Bruce Momjian" <pgman@candle.pha.pa.us> wrote in message
news:200401311944.i0VJiWj21658@candle.pha.pa.us...
> Tom Lane wrote:
> > I got the impression that Thomas wanted this in order to kluge up
> > some kind of two-phase-commit support, in which case he really needs
> > to get control at the point where we're just about to really truly
> > commit (ie, write the commit WAL record).  That's certainly not a
> > location where we want random users to be inserting code; as such
> > I don't think that exposing a callback hook is the right answer.
> > My advice to him is to go in and change the code.
>
> Yes, it can't be used for more advanced stuff like 2-phase commit.  I
> know some were asking for callbacks on (clean) session exit, and it
> seemed like a natural extension.
>
> -- 
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 359-1001
>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square, Pennsylvania
19073
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>



Sun, 1 Feb 2004 00:16:41 +0100
"Thomas Hallgren" <thhal@mailblocks.com>
Re: Transaction callback