Thread: Transaction callback
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
"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
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, Pennsylvania19073
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
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
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, Pennsylvania19073
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 >
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) >