Thread: Autonomous subtransactions
Dear Hackers, I have written some notes about autonomous subtransactions, which have already been touched (at least) in two separate threads; please find them at http://wiki.postgresql.org/wiki/Autonomous_subtransactions It is meant to be an ongoing project, requesting comments and contributions, rather than a conclusive document. Thanks, Dr. Gianni Ciolli - 2ndQuadrant Italia PostgreSQL Training, Services and Support gianni.ciolli@2ndquadrant.it | www.2ndquadrant.it p.s. The reason for the variation on the name is that there is always a parent transaction.
On Dec 18, 2011, at 2:28 AM, Gianni Ciolli wrote: > I have written some notes about autonomous subtransactions, which have > already been touched (at least) in two separate threads; please find > them at > > http://wiki.postgresql.org/wiki/Autonomous_subtransactions The document seems to mix the terms subtransaction and autonomous transaction. That's going to generate a ton of confusion,because both terms already have meaning associated with them: - Autonomous transaction means you can execute something outside of your current transaction and it is in no way effectedby the current transaction (doesn't matter if T0 commits or not). - Subtransactions are an alternative to savepoints. They allow you to break a large transaction into smaller chunks, butif T0 doesn't commit then none of the subtransactions do either. -- Jim C. Nasby, Database Architect jim@nasby.net 512.569.9461 (cell) http://jim.nasby.net
On Sun, Dec 18, 2011 at 10:28, Gianni Ciolli <gianni.ciolli@2ndquadrant.it> wrote: > http://wiki.postgresql.org/wiki/Autonomous_subtransactions > > It is meant to be an ongoing project, requesting comments and > contributions, rather than a conclusive document. In addition to what Jim Nasby said, this proposal seems a bit inflexible. In particular: 1. It limits us to exactly 2 autonomous transactions at any time (the main one and the "subtransaction"). 2. There's no reason why two autonomous transactions should have a "main / sub" relationship. They are autonomous -- they should not depend on the state of the "outer" transaction. Now, the initial implementation may well have such limits, but I think you should design your proposal to accommodate the above two features in the future without having to redesign the syntax. Maybe look at SAVEPOINTs for inspiration. There can be multiple savepoints in a single transaction, and they can be released/rolled back to, at any time. Regards, Marti
On Sun, Dec 18, 2011 at 4:22 PM, Jim Nasby <jim@nasby.net> wrote: > On Dec 18, 2011, at 2:28 AM, Gianni Ciolli wrote: >> I have written some notes about autonomous subtransactions, which have >> already been touched (at least) in two separate threads; please find >> them at >> >> http://wiki.postgresql.org/wiki/Autonomous_subtransactions > > The document seems to mix the terms subtransaction and autonomous transaction. That's going to generate a ton of confusion,because both terms already have meaning associated with them: > > - Autonomous transaction means you can execute something outside of your current transaction and it is in no way effectedby the current transaction (doesn't matter if T0 commits or not). > - Subtransactions are an alternative to savepoints. They allow you to break a large transaction into smaller chunks, butif T0 doesn't commit then none of the subtransactions do either. OK, perhaps we should just stick to the term Autonomous Transaction. That term is in common use, even if the usage is otherwise exactly the same as a subtransaction i.e. main transaction stops until the subtransaction is complete. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Mon, Dec 19, 2011 at 3:52 PM, Marti Raudsepp <marti@juffo.org> wrote: > On Sun, Dec 18, 2011 at 10:28, Gianni Ciolli > <gianni.ciolli@2ndquadrant.it> wrote: >> http://wiki.postgresql.org/wiki/Autonomous_subtransactions >> >> It is meant to be an ongoing project, requesting comments and >> contributions, rather than a conclusive document. > > In addition to what Jim Nasby said, this proposal seems a bit > inflexible. In particular: > 1. It limits us to exactly 2 autonomous transactions at any time (the > main one and the "subtransaction"). It's not clear to me why you think there would be a limitation to exactly 2 autonomous transactions. The intention would be to have a theoretically unlimited number, subject to resource limits, just as normal subtransactions already do. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Mon, Dec 19, 2011 at 20:34, Simon Riggs <simon@2ndquadrant.com> wrote: > It's not clear to me why you think there would be a limitation to > exactly 2 autonomous transactions. Sorry my bad, I didn't read the proposal carefully. Nesting does indeed allow multiple autonomous subtransactions. Maybe that's just my paranoia, but the fact that subtransactions aren't named means it's pretty easy to accidentally get "out of step" and commit the wrong subtransaction. I see app developers often messing up BEGIN/COMMIT/ROLLBACK already. This is why I like the SAVEPOINT style; it's obvious when there's a bug. (I do realize that allowing subtransactions to commit out of order also makes it failure prone) Regards, Marti
Excerpts from Marti Raudsepp's message of lun dic 19 16:32:06 -0300 2011: > (I do realize that allowing subtransactions to commit out of order > also makes it failure prone) Uhm? You can't "commit" savepoints out of order. You can "release" an older one, but then all the ones following it disappear and can't be released separately. -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Mon, Dec 19, 2011 at 21:43, Alvaro Herrera <alvherre@commandprompt.com> wrote: >> (I do realize that allowing subtransactions to commit out of order >> also makes it failure prone) > > Uhm? You can't "commit" savepoints out of order. You can "release" an > older one, but then all the ones following it disappear and can't be > released separately. We're confused about the terminology already :) I was talking about "autonomous subtransactions" as in COMMIT SUBTRANSACTION from the proposal. Earlier I commented that it would be nice if the syntax didn't require autonomous transactions to be strictly nested. Regards, Marti
Excerpts from Marti Raudsepp's message of lun dic 19 16:50:22 -0300 2011: > > On Mon, Dec 19, 2011 at 21:43, Alvaro Herrera > <alvherre@commandprompt.com> wrote: > >> (I do realize that allowing subtransactions to commit out of order > >> also makes it failure prone) > > > > Uhm? You can't "commit" savepoints out of order. You can "release" an > > older one, but then all the ones following it disappear and can't be > > released separately. > > We're confused about the terminology already :) Yeah. The code talks about savepoints as "subtransactions" (because that's the name they had at first), so if you guys are going to talk about autonomous transactions as "subtransactions" too, then the code is going to end up pretty schizo. > I was talking about "autonomous subtransactions" as in COMMIT > SUBTRANSACTION from the proposal. Earlier I commented that it would be > nice if the syntax didn't require autonomous transactions to be > strictly nested. Oh ... Probably. -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Mon, Dec 19, 2011 at 05:52:40PM +0200, Marti Raudsepp wrote: > On Sun, Dec 18, 2011 at 10:28, Gianni Ciolli > <gianni.ciolli@2ndquadrant.it> wrote: > > http://wiki.postgresql.org/wiki/Autonomous_subtransactions > > > > It is meant to be an ongoing project, requesting comments and > > contributions, rather than a conclusive document. > > In addition to what Jim Nasby said, this proposal seems a bit > inflexible. In particular: > 1. It limits us to exactly 2 autonomous transactions at any time (the > main one and the "subtransaction"). Thanks for pointing this out; it shows me that the only example I provided was too basic, so that I have added a second one, plus one related remark. The spec doesn't actually impose a limit of only 2 total transactions: if we have the capability to pause the current transaction (T0) to start a new one (T1), and then resume T0 after the conclusion of T1, then we can apply that capability more than once in the same transaction, resulting in a sequence of transactions T1, T2, ..., Tn all subordinate to T0, possibly interspersed by statements from T0. Also, if T0 is a function then it is possible peek at the (very) near future and eliminate any empty interlude of T0 between T_k and T_(k+1). > 2. There's no reason why two autonomous transactions should have a > "main / sub" relationship. They are autonomous -- they should not > depend on the state of the "outer" transaction. COMMIT/ROLLBACK of any of T1,T2,... does not depend on COMMIT/ROLLBACK of T0; the parent transaction exists only to simplify the logical model. It can happen that T0 contains zero statements, so that it is very similar to a sequence of independent transactions. The advantage to me is that we allow autonomous transactions, but there is always one "master" transaction, so that it looks more like a generalisation of the existing model as opposed to something radically different (and therefore less likely to happen). The behaviour is indeed similar to the one that can be obtained with dblink: it allows for instance to have an "umbrella" transaction which doesn't modify the database, and has the only purpose of executing other transactions, which commit or rollback independently of each other and of the umbrella transaction. With respect to dblink, this spec brings a simpler syntax, which doesn't require managing a second connection to the same db, and one significant difference: you reuse the current session instead of opening a new one (with some caveats, as noted in the Wiki). Regards, Dr. Gianni Ciolli - 2ndQuadrant Italia PostgreSQL Training, Services and Support gianni.ciolli@2ndquadrant.it | www.2ndquadrant.it
On Mon, Dec 19, 2011 at 09:32:06PM +0200, Marti Raudsepp wrote: > Maybe that's just my paranoia, but the fact that subtransactions > aren't named means it's pretty easy to accidentally get "out of step" > and commit the wrong subtransaction. I see app developers often > messing up BEGIN/COMMIT/ROLLBACK already. This is why I like the > SAVEPOINT style; it's obvious when there's a bug. For each COMMIT/ROLLBACK there is only one possible BEGIN SUBTRANSACTION, therefore naming is not strictly necessary, unlike in the SAVEPOINT case where you can have more than one option. However, ISTM that allowing to optionally name each transaction could provide a nice safety measure that the careful developer can use to reduce the chances of human error. If subtransactions are not named, no check is made; but if they are named, then the Xs on BEGIN SUBTRANSACTION X and COMMIT/ROLLBACK SUBTRANSACTION X must match, otherwise an exception is raised (and a bug is detected). Regards, Dr. Gianni Ciolli - 2ndQuadrant Italia PostgreSQL Training, Services and Support gianni.ciolli@2ndquadrant.it | www.2ndquadrant.it
On Dec 19, 2011, at 12:31 PM, Simon Riggs wrote: > On Sun, Dec 18, 2011 at 4:22 PM, Jim Nasby <jim@nasby.net> wrote: >> On Dec 18, 2011, at 2:28 AM, Gianni Ciolli wrote: >>> I have written some notes about autonomous subtransactions, which have >>> already been touched (at least) in two separate threads; please find >>> them at >>> >>> http://wiki.postgresql.org/wiki/Autonomous_subtransactions >> >> The document seems to mix the terms subtransaction and autonomous transaction. That's going to generate a ton of confusion,because both terms already have meaning associated with them: >> >> - Autonomous transaction means you can execute something outside of your current transaction and it is in no way effectedby the current transaction (doesn't matter if T0 commits or not). >> - Subtransactions are an alternative to savepoints. They allow you to break a large transaction into smaller chunks, butif T0 doesn't commit then none of the subtransactions do either. > > OK, perhaps we should just stick to the term Autonomous Transaction. > That term is in common use, even if the usage is otherwise exactly the > same as a subtransaction i.e. main transaction stops until the > subtransaction is complete. Except AFAIR Oracle uses the term to indicate something that is happening *outside* of your current transaction, which isdefinitely not what the proposal is talking about. I'm not wed to "subtransaction" (though I think it's a perfectly goodname for this), but I definitely think calling this an "autonomous transaction" would be bad. -- Jim C. Nasby, Database Architect jim@nasby.net 512.569.9461 (cell) http://jim.nasby.net
On Wed, Jan 04, 2012 at 04:58:08PM -0600, Jim Nasby wrote: > Except AFAIR Oracle uses the term to indicate something that is > happening *outside* of your current transaction, which is definitely > not what the proposal is talking about. That feature is commonly translated in PostgreSQL to a dblink-based solution, which itself is not distant from the current proposal, at least in terms of inside/outside (the biggest difference I can see is on sharing temporary tables). But I am not sure I understand your remark; it would be clearer to me if you could provide an example explaining the difference. Dr. Gianni Ciolli - 2ndQuadrant Italia PostgreSQL Training, Services and Support gianni.ciolli@2ndquadrant.it | www.2ndquadrant.it
On Jan 4, 2012, at 5:59 PM, Gianni Ciolli wrote: > On Wed, Jan 04, 2012 at 04:58:08PM -0600, Jim Nasby wrote: >> Except AFAIR Oracle uses the term to indicate something that is >> happening *outside* of your current transaction, which is definitely >> not what the proposal is talking about. > > That feature is commonly translated in PostgreSQL to a dblink-based > solution, which itself is not distant from the current proposal, at > least in terms of inside/outside (the biggest difference I can see is > on sharing temporary tables). > > But I am not sure I understand your remark; it would be clearer to me > if you could provide an example explaining the difference. As I understand your proposal, you are doing everything in a single backend and a single transaction... you're just providinga means to split one transaction into smaller pieces. Is that not the case? -- Jim C. Nasby, Database Architect jim@nasby.net 512.569.9461 (cell) http://jim.nasby.net