Thread: Nested Transactions, Abort All
Is there going to be an option to abort the complete transaction without knowing how deep you are? Perhaps something like "ABORT ALL". The reason I suggest this, is that I can foresee an application or user leaving nested transactions open inadvertently, or not knowing how deeply nested they are when they are called. It's just a thought, and I didn't recall any mention of something like it on the list. Thomas
On Thu, Jul 01, 2004 at 02:01:37PM -0500, Thomas Swan wrote: > Is there going to be an option to abort the complete transaction without > knowing how deep you are? Perhaps something like "ABORT ALL". > > The reason I suggest this, is that I can foresee an application or user > leaving nested transactions open inadvertently, or not knowing how > deeply nested they are when they are called. It's just a thought, and I > didn't recall any mention of something like it on the list. If we change the syntax, say by using SUBCOMMIT/SUBABORT for subtransactions, then using a simple ABORT would abort the whole transaction tree. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) Oh, oh, las chicas galacianas, lo harán por las perlas, ¡Y las de Arrakis por el agua! Pero si buscas damas Que se consuman como llamas, ¡Prueba una hija de Caladan! (Gurney Halleck)
On Thu, 2004-07-01 at 18:38 -0400, Alvaro Herrera wrote: > On Thu, Jul 01, 2004 at 02:01:37PM -0500, Thomas Swan wrote: > > Is there going to be an option to abort the complete transaction without > > knowing how deep you are? Perhaps something like "ABORT ALL". > > > > The reason I suggest this, is that I can foresee an application or user > > leaving nested transactions open inadvertently, or not knowing how > > deeply nested they are when they are called. It's just a thought, and I > > didn't recall any mention of something like it on the list. > > If we change the syntax, say by using SUBCOMMIT/SUBABORT for > subtransactions, then using a simple ABORT would abort the whole > transaction tree. > But then we're back to the application having to know if its in a regular transaction or a sub-transaction aren't we? To me that sounds just as bad. "ABORT ALL" sure would be nice. -- Mike Benoit <ipso@snappymail.ca>
On Thu, Jul 01, 2004 at 04:47:09PM -0700, Mike Benoit wrote: > On Thu, 2004-07-01 at 18:38 -0400, Alvaro Herrera wrote: > > On Thu, Jul 01, 2004 at 02:01:37PM -0500, Thomas Swan wrote: > > If we change the syntax, say by using SUBCOMMIT/SUBABORT for > > subtransactions, then using a simple ABORT would abort the whole > > transaction tree. > > But then we're back to the application having to know if its in a > regular transaction or a sub-transaction aren't we? To me that sounds > just as bad. I don't get it. You want to argue that the application should be ignorant of whether it was in a transaction or not? What I am saying is that independent of what the current nesting level is, issuing ABORT would close all open subtransactions, close (roll back) the main transaction too, and return to the default not-in-a-transaction state. Of course, issuing a single COMMIT would also commit all open subtransactions and the main transaction too. In contrast, issuing SUBCOMMIT or SUBABORT would commit/abort only the current subtransaction. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "Ellos andaban todos desnudos como su madre los parió, y también las mujeres, aunque no vi más que una, harto moza, y todos los que yo vi eran todos mancebos, que ninguno vi de edad de más de XXX años" (Cristóbal Colón)
Mike Benoit <ipso@snappymail.ca> writes:
> On Thu, 2004-07-01 at 18:38 -0400, Alvaro Herrera wrote:
>> If we change the syntax, say by using SUBCOMMIT/SUBABORT for
>> subtransactions, then using a simple ABORT would abort the whole
>> transaction tree.
> But then we're back to the application having to know if its in a
> regular transaction or a sub-transaction aren't we? To me that sounds
> just as bad.
Someone (I forget who at this late hour) gave several cogent arguments
that that's *exactly* what we want. Please see the prior discussion...
Right at the moment I think we have a consensus that we should use
SUBBEGIN/SUBEND or some such keywords for subtransactions. (I do not
say we've agreed to exactly those keywords, only that it's a good idea
to make them different from the outer-level BEGIN/END keywords.)
There was also some talk of offering commands based around the notion of
savepoints, but I'm not sure that we have a consensus on that yet.
regards, tom lane
On Thu, 2004-07-01 at 22:14, Tom Lane wrote: > Mike Benoit <ipso@snappymail.ca> writes: > > On Thu, 2004-07-01 at 18:38 -0400, Alvaro Herrera wrote: > >> If we change the syntax, say by using SUBCOMMIT/SUBABORT for > >> subtransactions, then using a simple ABORT would abort the whole > >> transaction tree. > > > But then we're back to the application having to know if its in a > > regular transaction or a sub-transaction aren't we? To me that sounds > > just as bad. > > Someone (I forget who at this late hour) gave several cogent arguments > that that's *exactly* what we want. Please see the prior discussion... > > Right at the moment I think we have a consensus that we should use > SUBBEGIN/SUBEND or some such keywords for subtransactions. (I do not > say we've agreed to exactly those keywords, only that it's a good idea > to make them different from the outer-level BEGIN/END keywords.) > > There was also some talk of offering commands based around the notion of > savepoints, but I'm not sure that we have a consensus on that yet. Aren't subtransactions and their syntax defined by the SQL spec somewhere?
Tom Lane wrote: >Mike Benoit <ipso@snappymail.ca> writes: > > >>On Thu, 2004-07-01 at 18:38 -0400, Alvaro Herrera wrote: >> >> >>>If we change the syntax, say by using SUBCOMMIT/SUBABORT for >>>subtransactions, then using a simple ABORT would abort the whole >>>transaction tree. >>> >>> > > > >>But then we're back to the application having to know if its in a >>regular transaction or a sub-transaction aren't we? To me that sounds >>just as bad. >> >> > >Someone (I forget who at this late hour) gave several cogent arguments >that that's *exactly* what we want. Please see the prior discussion... > >Right at the moment I think we have a consensus that we should use >SUBBEGIN/SUBEND or some such keywords for subtransactions. (I do not >say we've agreed to exactly those keywords, only that it's a good idea >to make them different from the outer-level BEGIN/END keywords.) > > > Either approach still needs some mechanism to clear the current stack of transactions and subtransactions. That's why I was thinking ABORT ALL and ROLLBACK ALL would be sufficient to cover that and be clear enough to the user/programmer. >There was also some talk of offering commands based around the notion of >savepoints, but I'm not sure that we have a consensus on that yet. > > regards, tom lane > >
> If we change the syntax, say by using SUBCOMMIT/SUBABORT for > subtransactions, then using a simple ABORT would abort the whole > transaction tree. Question: with the new syntax, would issuing a BEGIN inside a already started transaction result in an error? My concern is about say, a pl/pgsql function that opened and closed a transation. This could result in different behaviors depending if called from within a transaction, which is not true of the old syntax. Then again, since a statement is always transactionally wrapped, would it be required to always issue SUBBEGIN if issued from within a function? This would address my concern. Merlin
On Fri, Jul 02, 2004 at 01:14:25PM -0400, Merlin Moncure wrote: > > If we change the syntax, say by using SUBCOMMIT/SUBABORT for > > subtransactions, then using a simple ABORT would abort the whole > > transaction tree. > > Question: with the new syntax, would issuing a BEGIN inside a already > started transaction result in an error? Yes. > My concern is about say, a pl/pgsql function that opened and closed a > transation. This could result in different behaviors depending if > called from within a transaction, which is not true of the old syntax. > > Then again, since a statement is always transactionally wrapped, would > it be required to always issue SUBBEGIN if issued from within a > function? This would address my concern. Yes, I was thinking about this because the current code behaves wrong if a BEGIN is issued and not inside a transaction block. So we'd need to do something special in SPI -- not sure exactly what, but the effect would be that the function can't issue BEGIN at all and can only issue SUBBEGIN. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) A male gynecologist is like an auto mechanic who never owned a car. (Carrie Snow)
Alvaro Herrera wrote: >On Fri, Jul 02, 2004 at 01:14:25PM -0400, Merlin Moncure wrote: > > >>>If we change the syntax, say by using SUBCOMMIT/SUBABORT for >>>subtransactions, then using a simple ABORT would abort the whole >>>transaction tree. >>> >>> >>Question: with the new syntax, would issuing a BEGIN inside a already >>started transaction result in an error? >> >> > >Yes. > > > >>My concern is about say, a pl/pgsql function that opened and closed a >>transation. This could result in different behaviors depending if >>called from within a transaction, which is not true of the old syntax. >> >>Then again, since a statement is always transactionally wrapped, would >>it be required to always issue SUBBEGIN if issued from within a >>function? This would address my concern. >> >> > >Yes, I was thinking about this because the current code behaves wrong if >a BEGIN is issued and not inside a transaction block. So we'd need to >do something special in SPI -- not sure exactly what, but the effect >would be that the function can't issue BEGIN at all and can only issue >SUBBEGIN. > > > Isn't this counterintuitive. It seems that BEGIN and COMMIT/ABORT should be sufficient regardless of the level. If you are inside a current transaction those commands start a new transaction inside of the current transaction level, just like pushing on and popping off elements on a stack. I'm not trying to be argumentative, but the notation seems orthogonal to the issue. Some functions and procedures may not be called inside of transactions or subtransactions. Having to start with a SUBBEGIN and SUBCOMMIT/SUBABORT is equally problematic if you don't know where you begin. Taking the extreme everything should be a SUBBEGIN and a SUBCOMMIT/SUBABORT so why have BEGIN and END? Unless you have some way to tell (by query) the state you are in is a subtransaction and how many levels you are deep into the nested transaction, deciding whether to use SUBBEGIN and SUBCOMMIT/SUBABORT vs the traditional BEGIN COMMIT/ABORT becomes nondeterministic.
Thomas Swan wrote: > Alvaro Herrera wrote: > >> Yes, I was thinking about this because the current code behaves wrong if >> a BEGIN is issued and not inside a transaction block. So we'd need to >> do something special in SPI -- not sure exactly what, but the effect >> would be that the function can't issue BEGIN at all and can only issue >> SUBBEGIN. >> > Isn't this counterintuitive. It seems that BEGIN and COMMIT/ABORT > should be sufficient regardless of the level. If you are inside a > current transaction those commands start a new transaction inside of the > current transaction level, just like pushing on and popping off elements > on a stack. How about this radical idea: Use SAVEPOINT to begin a subtransaction and ROLLBACK TO SAVEPOINT to abort that subtransaction. Normally, in Oracle, I would write code like: SAVEPOINT foo; <do work> IF (error) THEN ROLLBACK TO SAVEPOINT foo; END IF; Could we not treat a subtransaction as an "anonymous" savepoint until savepoints are added? So the above in PostgreSQL would read: SAVEPOINT; <do work> IF (error) THEN ROLLBACK TO SAVEPOINT; END IF; My old SQL3 draft EBNF reads: <savepoint statement> ::= SAVEPOINT <savepoint specifier> <savepoint specifier> ::= <savepoint name> | <simple target specification> <savepoint name> ::= <identifier> and <rollback statement> ::= ROLLBACK [ WORK ] [ AND[ NO ] CHAIN ] [ <savepoint clause> ] <savepoint clause> ::= TO SAVEPOINT <savepoint specifier> Mike Mascari
On Fri, Jul 02, 2004 at 01:37:46PM -0500, Thomas Swan wrote: > Alvaro Herrera wrote: > >>Then again, since a statement is always transactionally wrapped, would > >>it be required to always issue SUBBEGIN if issued from within a > >>function? This would address my concern. > > Isn't this counterintuitive. It seems that BEGIN and COMMIT/ABORT > should be sufficient regardless of the level. If you are inside a > current transaction those commands start a new transaction inside of the > current transaction level, just like pushing on and popping off elements > on a stack. No, the first level is quite different from any other, and that's why it should use a different syntax. Really any level above level 1 is not a transaction at all; it's a unit that you can rollback independently but nothing more; you can't commit it independently. I think a better term than "subtransaction" or "nested transaction" is "rollback unit" or some such. > Some functions and procedures may not be called inside of transactions > or subtransactions. No. Some functions cannot be called inside a transaction block. Whether you are or not inside a subtransaction within the transaction block is not important. In fact, the application doesn't care what nesting level it is in; the only thing that it cares about is if it is in a transaction block or not. Please note that I'm using the term "transaction block" and not "transaction." The distinction is important because everything is always inside a transaction, though it may be an implicit one. A transaction block, on the other hand, is always an explicit thing. And a subtransaction is also an explicit thing. > Unless you have some way to tell (by query) the state you are in is a > subtransaction and how many levels you are deep into the nested > transaction, deciding whether to use SUBBEGIN and SUBCOMMIT/SUBABORT vs > the traditional BEGIN COMMIT/ABORT becomes nondeterministic. The application always has to keep track if it is inside a transaction block or not. This has always been true and it continues to be so. Whether you are inside a subtransaction or not is not really important. If you want to commit the whole transaction block just issue COMMIT, and all levels will be committed. Similarly if you want to abort. But if you want to retry a subtransaction which has just failed you better know whether you are on a subtransaction or not ... I mean if the app doesn't know that then it isn't using subtransactions, is it? Knowing just the nesting level will not help you -- the app has to know _what_ to retry. And if it isn't going to retry anything then there's no point in using subtransactions at all. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "Cuando mañana llegue pelearemos segun lo que mañana exija" (Mowgli)
Alvaro Herrera wrote: >On Fri, Jul 02, 2004 at 01:37:46PM -0500, Thomas Swan wrote: > > >>Alvaro Herrera wrote: >> >> > > > >>>>Then again, since a statement is always transactionally wrapped, would >>>>it be required to always issue SUBBEGIN if issued from within a >>>>function? This would address my concern. >>>> >>>> >>Isn't this counterintuitive. It seems that BEGIN and COMMIT/ABORT >>should be sufficient regardless of the level. If you are inside a >>current transaction those commands start a new transaction inside of the >>current transaction level, just like pushing on and popping off elements >>on a stack. >> >> > >No, the first level is quite different from any other, and that's why it >should use a different syntax. Really any level above level 1 is not a >transaction at all; it's a unit that you can rollback independently but >nothing more; you can't commit it independently. I think a better term >than "subtransaction" or "nested transaction" is "rollback unit" or some >such. > > > >>Some functions and procedures may not be called inside of transactions >>or subtransactions. >> >> > >No. Some functions cannot be called inside a transaction block. >Whether you are or not inside a subtransaction within the transaction >block is not important. In fact, the application doesn't care what >nesting level it is in; the only thing that it cares about is if it is >in a transaction block or not. > >Please note that I'm using the term "transaction block" and not >"transaction." The distinction is important because everything is >always inside a transaction, though it may be an implicit one. A >transaction block, on the other hand, is always an explicit thing. >And a subtransaction is also an explicit thing. > > This is the reason the outermost block is irrelevant to the point. Inner transactions (including the implicit ones mentioned) commit only if their parent transactions commit. If there is an implicit begin/commit, then everything underneath should be subbegin/subcommit. If it is sometimes implicit then the subbegin/begin state is non-deterministic. Without the underlying or stack depth, it is difficult to predict. In psql, autocommit (on/off) behavoir becomes a little muddy if you go with the SUBBEGIN and SUBCOMMIT construct. Below should BEGIN (1) be a SUBBEGIN or a BEGIN? Both examples would give equivalent results. -- BEGIN (implicit) BEGIN (1) BEGIN SOMETHING BEGIN SOMETHING ROLLBACK ROLLBACK COMMIT (1) COMMIT (implicit) -- BEGIN (1) BEGIN SOMETHING BEGIN SOMETHING ROLLBACK ROLLBACK COMMIT (1) -- > > >>Unless you have some way to tell (by query) the state you are in is a >>subtransaction and how many levels you are deep into the nested >>transaction, deciding whether to use SUBBEGIN and SUBCOMMIT/SUBABORT vs >>the traditional BEGIN COMMIT/ABORT becomes nondeterministic. >> >> > >The application always has to keep track if it is inside a transaction >block or not. This has always been true and it continues to be so. >Whether you are inside a subtransaction or not is not really important. >If you want to commit the whole transaction block just issue COMMIT, and >all levels will be committed. > psql will tell me how deep I am in transactions? >Similarly if you want to abort. But if >you want to retry a subtransaction which has just failed you better know >whether you are on a subtransaction or not ... I mean if the app >doesn't know that then it isn't using subtransactions, is it? > > > That's an rather big assumption? It may not be the app, it may include stored procedures and functions as well. Imagine a little function called dance( ). Dance begins a transaction, does a little work, and then aborts. If I am not in a transaction and I write dance as a subtransaction then I have the problem and call it then I have a problem. If I am in a transaction and I write dance as a transaction, then I have a problem. There's no universal way to write the function, without having to refer to an external state unless I make the scope universal. Hence, SUBBEGIN and SUBCOMMIT are bad ideas. >Knowing just the nesting level will not help you -- the app has to know >_what_ to retry. And if it isn't going to retry anything then there's >no point in using subtransactions at all. > > If you have the nesting level, then you know how many commits/rollbacks to perform to get to an entrance state.
On Fri, Jul 02, 2004 at 03:32:12PM -0500, Thomas Swan wrote: > Alvaro Herrera wrote: > > >Please note that I'm using the term "transaction block" and not > >"transaction." The distinction is important because everything is > >always inside a transaction, though it may be an implicit one. A > >transaction block, on the other hand, is always an explicit thing. > >And a subtransaction is also an explicit thing. > > This is the reason the outermost block is irrelevant to the point. > Inner transactions (including the implicit ones mentioned) commit only > if their parent transactions commit. If there is an implicit > begin/commit, then everything underneath should be subbegin/subcommit. > If it is sometimes implicit then the subbegin/begin state is > non-deterministic. Without the underlying or stack depth, it is > difficult to predict. You can't have subtransactions inside an implicit transaction block, so this answers all your concerns here I think. It just doesn't make sense. How would you call a subtransaction in a implicit transaction? Don't tell me to use BEGIN because that'd start an explicit transaction block ... > In psql, autocommit (on/off) behavoir becomes a little muddy if you go > with the SUBBEGIN and SUBCOMMIT construct. Au contraire ... autocommit is easier to support with separate syntax AFAICT. > psql will tell me how deep I am in transactions? Yes, there should be a way to know this, if only for showing it in the prompt. It's not there at present. > >Similarly if you want to abort. But if you want to retry a > >subtransaction which has just failed you better know whether you are > >on a subtransaction or not ... I mean if the app doesn't know that > >then it isn't using subtransactions, is it? > > That's an rather big assumption? It may not be the app, it may include > stored procedures and functions as well. I said in some other thread that a function can not call BEGIN, only SUBBEGIN (there is a reason besides this one, and it is that it just doesn't work to use BEGIN in a function when not in a transaction block -- you can try it with current sources). At this point you can claim that in this case you would not be able to call a function that uses subtransactions when not in a transaction block; that's why we need to automatically start a transaction block to call a function, if not already in one. > If you have the nesting level, then you know how many commits/rollbacks > to perform to get to an entrance state. Why bother? Just issue one and you are done. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "El sentido de las cosas no viene de las cosas, sino de las inteligencias que las aplican a sus problemas diarios en busca del progreso." (Ernesto Hernández-Novich)
On Fri, Jul 02, 2004 at 05:30:49PM -0400, Alvaro Herrera wrote: > You can't have subtransactions inside an implicit transaction block, so Haven't been following this thread closely, but just my 2 cents... If you collate queries using the semicolon, AFAIK the whole thing is executed as a single implicit transaction (if not in an explicit one already, of course). So is there anyone stopping a user from executing BEGIN ; UPDATE ... ; COMMIT ? Jeroen
On Fri, Jul 02, 2004 at 11:51:01PM +0200, Jeroen T. Vermeulen wrote: > On Fri, Jul 02, 2004 at 05:30:49PM -0400, Alvaro Herrera wrote: > > > You can't have subtransactions inside an implicit transaction block, so > > Haven't been following this thread closely, but just my 2 cents... > > If you collate queries using the semicolon, AFAIK the whole thing is > executed as a single implicit transaction (if not in an explicit one > already, of course). Oh, right, I forgot about this one ... Will think about it (it should be forbidden). -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) La web junta la gente porque no importa que clase de mutante sexual seas, tienes millones de posibles parejas. Pon "buscar gente que tengan sexo con ciervos incendiánse", y el computador dirá "especifique el tipo de ciervo" (Jason Alexander)
Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> You can't have subtransactions inside an implicit transaction block,
It would be folly to design on that assumption. We *will* have that
situation just as soon as plpgsql allows creating subtransactions
(which I trust you'll agree will happen soon). All you have to do
is call such a function from a bare SELECT. I do not think you'll
be able to legislate that people must say BEGIN first.
regards, tom lane
On Fri, Jul 02, 2004 at 07:43:47PM -0400, Tom Lane wrote: > Alvaro Herrera <alvherre@dcc.uchile.cl> writes: > > You can't have subtransactions inside an implicit transaction block, > > It would be folly to design on that assumption. We *will* have that > situation just as soon as plpgsql allows creating subtransactions > (which I trust you'll agree will happen soon). It is allowed already (this is why I hacked SPI in the first place). In fact, it can easily cause a server crash. Try this function: create function crashme() returns int language plpgsql as ' begin start transaction; commit transaction; return 1; end; '; Try running it without starting a transaction; the server crashes. If you run it inside a transaction block, there is no crash. The reason this happens is that the first START TRANSACTION starts the transaction block (since we are already in a transaction this is a no-op as far as the transaction is concerned), and the commit ends it, blowing the function state out of the water. This does not happen within a transaction block, and the nesting is OK (i.e. you have to issue one and only one COMMIT command to end the transaction block). This shows that the first BEGIN is different from any other: the first is some kind of no-op (the transaction starts regardless of it), while any subsequent BEGIN actually starts a subtransaction. Another thing to try is create function dontcrashme() returns int language plpgsql as ' begin start transaction; start transaction; commit transaction; return 1; end; '; Obviously this doesn't crash regardless of whether you are inside a transaction block or not. But you have to issue a COMMIT after the function is called to return to a sane state. What I'd like to do is start the transaction block before the function is called if we are not in a transaction block. This would mean that when the function calls BEGIN it won't be the first one -- it will actually start a subtransaction and will be able to end it without harm. I think this can be done automatically at the SPI level. One situation I don't know how to cope with is a multiquery statement, as pointed out by Jeroem. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) Al principio era UNIX, y UNIX habló y dijo: "Hello world\n". No dijo "Hello New Jersey\n", ni "Hello USA\n".
Alvaro Herrera wrote: >On Fri, Jul 02, 2004 at 07:43:47PM -0400, Tom Lane wrote: > > >>Alvaro Herrera <alvherre@dcc.uchile.cl> writes: >> >> >>>You can't have subtransactions inside an implicit transaction block, >>> >>> >>It would be folly to design on that assumption. We *will* have that >>situation just as soon as plpgsql allows creating subtransactions >>(which I trust you'll agree will happen soon). >> >> > >It is allowed already (this is why I hacked SPI in the first place). In >fact, it can easily cause a server crash. Try this function: > >create function crashme() returns int language plpgsql as ' >begin >start transaction; >commit transaction; >return 1; >end; >'; > >Try running it without starting a transaction; the server crashes. If >you run it inside a transaction block, there is no crash. > >The reason this happens is that the first START TRANSACTION starts the >transaction block (since we are already in a transaction this is a no-op >as far as the transaction is concerned), and the commit ends it, blowing >the function state out of the water. This does not happen within a >transaction block, and the nesting is OK (i.e. you have to issue one and >only one COMMIT command to end the transaction block). > >This shows that the first BEGIN is different from any other: the first >is some kind of no-op (the transaction starts regardless of it), while >any subsequent BEGIN actually starts a subtransaction. > >Another thing to try is > >create function dontcrashme() returns int language plpgsql as ' >begin >start transaction; >start transaction; >commit transaction; >return 1; >end; >'; > >Obviously this doesn't crash regardless of whether you are inside a >transaction block or not. But you have to issue a COMMIT after the >function is called to return to a sane state. > > >What I'd like to do is start the transaction block before the function >is called if we are not in a transaction block. This would mean that >when the function calls BEGIN it won't be the first one -- it will >actually start a subtransaction and will be able to end it without harm. >I think this can be done automatically at the SPI level. > >One situation I don't know how to cope with is a multiquery statement, >as pointed out by Jeroem. > > > Please tell me there is some sanity in this. If I follow you correctly, at no point should anyone be able to issue an explicit begin/end because they are already in an explicit/implicit transaction by default... How is the user/programmer to know when this is the case?
Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> If we change the syntax, say by using SUBCOMMIT/SUBABORT for
> subtransactions, then using a simple ABORT would abort the whole
> transaction tree.
This seems like a non-starter to me. That would make it impossible to write
SQL generic code that could be used from within a transaction or as a
top-level transaction.
Consider for example if I have application code that normally handles
archiving old data (excuse the odd made-up pseudo-code syntax):
archive_table($tab, $date) { query(" BEGIN INSERT INTO archive_$tab (select * from $tab where date < ?)
DELETE FROM $tab where date < ? END ", $date, $date);
}
Then I later decide I sometimes want to do that along with other jobs together
in a transaction. I can't just do:
query("BEGIN");
archive_table(tab1, date);
archive_table(tab2, date);
other_maintenance_work();
query("END");
Because then the archive_table() function would get an error from trying to use
BEGIN when it would need a SUBBEGIN. And it would not be any better if I
change archive_tab to use SUBBEGIN because I might be using it directly
elsewhere.
This seems like a irregularity in the API that makes sense only from an
implementation point of view. Top level transactions may be very different
from the implementation side, but from the user side they should really be
presented as being exactly the same as successive levels.
I do think a COMMIT ALL and ABORT ALL would be useful, but not for end-users.
I think they would be useful at a lower level. For example a web site could
issue an ABORT ALL at the end of generating the page to ensure any uncommitted
transactions fail and the connection is restored to a usable state for the
next page request.
--
greg
Greg Stark <gsstark@mit.edu> writes:
> This seems like a irregularity in the API that makes sense only from an
> implementation point of view.
You are attacking a straw man. This does *not* "make sense from an
implementation point of view" --- it's easier to have just one command
(and in fact that is what is in CVS tip). The proposal to use different
commands was advanced on the grounds that it's a more user-friendly API.
regards, tom lane
Greg Stark wrote: > Alvaro Herrera <alvherre@dcc.uchile.cl> writes: > > >>If we change the syntax, say by using SUBCOMMIT/SUBABORT for >>subtransactions, then using a simple ABORT would abort the whole >>transaction tree. > > > This seems like a non-starter to me. That would make it impossible to write > SQL generic code that could be used from within a transaction or as a > top-level transaction. I think it's vital that any use of the existing (pre-7.5) syntax for COMMIT/ROLLBACK/ABORT results in all transaction state being cleared, for compatibility with older applications. Consider JDBC's Connection.commit() and Connection.rollback() methods. They need to deal in terms of the top-level transaction: connection pools and similar are going to issue rollback() and expect all transaction state to be cleaned up, regardless of what the pool's client has done with the connection. The connection pool code is unlikely to be aware of subtransactions -- JDBC has no equivalent concept (it has savepoints, but that's it). If ROLLBACK always affects the top-level transaction, the JDBC driver's job is simple: Connection.rollback() always issues ROLLBACK. If you need some other syntax to get a top-level rollback, the driver's job gets messier: if we have at least a 7.5 server: issue ROLLBACK ALL else: issue ROLLBACK and older drivers which always issue ROLLBACK are going to break in nonobvious ways if their applications start using subtransactions. This seems like a gratuitous incompatibility to introduce. I'd expect other clients that aren't aware of subtransactions to stumble on this too. -O
On Sat, Jul 03, 2004 at 02:32:44AM -0500, Thomas Swan wrote: > Alvaro Herrera wrote: > >What I'd like to do is start the transaction block before the function > >is called if we are not in a transaction block. This would mean that > >when the function calls BEGIN it won't be the first one -- it will > >actually start a subtransaction and will be able to end it without harm. > >I think this can be done automatically at the SPI level. > > Please tell me there is some sanity in this. If I follow you > correctly, at no point should anyone be able to issue an explicit > begin/end because they are already in an explicit/implicit transaction > by default... How is the user/programmer to know when this is the case? I'm not sure I understand you. Of course you can issue begin/end. What you can't do is issue begin/end inside a function -- you always use subbegin/subcommit in that case. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "La espina, desde que nace, ya pincha" (Proverbio africano)
On Mon, 5 Jul 2004, Alvaro Herrera wrote: > > begin/end because they are already in an explicit/implicit transaction > > by default... How is the user/programmer to know when this is the case? > > I'm not sure I understand you. Of course you can issue begin/end. What > you can't do is issue begin/end inside a function -- you always use > subbegin/subcommit in that case. I've not understood why we need new tokens for this case. Maybe you've explained it somewhere that I've missed. But surely the server know if you are in a transaction or not, and can differentiate on the first BEGIN and the next BEGIN. -- /Dennis Björklund
Alvaro Herrera wrote: > On Sat, Jul 03, 2004 at 02:32:44AM -0500, Thomas Swan wrote: > > Alvaro Herrera wrote: > > > >What I'd like to do is start the transaction block before the function > > >is called if we are not in a transaction block. This would mean that > > >when the function calls BEGIN it won't be the first one -- it will > > >actually start a subtransaction and will be able to end it without harm. > > >I think this can be done automatically at the SPI level. > > > > Please tell me there is some sanity in this. If I follow you > > correctly, at no point should anyone be able to issue an explicit > > begin/end because they are already in an explicit/implicit transaction > > by default... How is the user/programmer to know when this is the case? > > I'm not sure I understand you. Of course you can issue begin/end. What > you can't do is issue begin/end inside a function -- you always use > subbegin/subcommit in that case. And if you use SUBBEGIN/SUBCOMMIT in a function that isn't already call inside from an explicit transaction, it will work because the call itself is its own implicit transaction, right? -- 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 Tue, Jul 06, 2004 at 11:37:18AM -0400, Bruce Momjian wrote: > Alvaro Herrera wrote: > > On Sat, Jul 03, 2004 at 02:32:44AM -0500, Thomas Swan wrote: > > > Alvaro Herrera wrote: > > > > > >What I'd like to do is start the transaction block before the function > > > >is called if we are not in a transaction block. This would mean that > > > >when the function calls BEGIN it won't be the first one -- it will > > > >actually start a subtransaction and will be able to end it without harm. > > > >I think this can be done automatically at the SPI level. > > > > > > Please tell me there is some sanity in this. If I follow you > > > correctly, at no point should anyone be able to issue an explicit > > > begin/end because they are already in an explicit/implicit transaction > > > by default... How is the user/programmer to know when this is the case? > > > > I'm not sure I understand you. Of course you can issue begin/end. What > > you can't do is issue begin/end inside a function -- you always use > > subbegin/subcommit in that case. > > And if you use SUBBEGIN/SUBCOMMIT in a function that isn't already call > inside from an explicit transaction, it will work because the call > itself is its own implicit transaction, right? Right. Note that this doesn't work with the current code -- in fact you can cause a server crash easily. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "Doing what he did amounts to sticking his fingers under the hood of the implementation; if he gets his fingers burnt, it's his problem." (Tom Lane)
On Tue, Jul 06, 2004 at 08:15:14AM +0200, Dennis Bjorklund wrote: > On Mon, 5 Jul 2004, Alvaro Herrera wrote: > > > > begin/end because they are already in an explicit/implicit transaction > > > by default... How is the user/programmer to know when this is the case? > > > > I'm not sure I understand you. Of course you can issue begin/end. What > > you can't do is issue begin/end inside a function -- you always use > > subbegin/subcommit in that case. > > I've not understood why we need new tokens for this case. Maybe you've > explained it somewhere that I've missed. But surely the server know if you > are in a transaction or not, and can differentiate on the first BEGIN and > the next BEGIN. I think the best argument for this is that we need a command to abort the whole transaction tree, and another to commit the whole transaction tree. Those _have_ to be ROLLBACK (or ABORT) and COMMIT (or END), because the spec says they work like that and it would be hell for an interface like JDBC if they didn't. So it's out of the picture to use those commands to end a subtransaction. Now, it's clear we need new commands to end a subtransaction. Do we also want a different command for begin? I think so, just to be consistent. Conclusion: we need a different syntax. So we invent an extension. We could use BEGIN NESTED for starting a subtransaction, roll it back with ROLLBACK NESTED or some such, and commit with COMMIT NESTED. But I like SUBBEGIN etc best, and no one had an opinion when I asked. So the current code has SUBBEGIN, SUBCOMMIT, SUBABORT. If people prefer another syntax, then we can have a vote or core hackers can choose -- I don't care what the syntax is, but it has to be different from BEGIN, COMMIT, ROLLBACK. We can later implement savepoints, which will have "SAVEPOINT foo" and "ROLLBACK TO foo" as interface. (Note that a subtransaction is slightly different from a savepoint, so we can't use ROLLBACK TO <foo> in subtransactions because that has a different meaning in savepoints). -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "La rebeldía es la virtud original del hombre" (Arthur Schopenhauer)
* Alvaro Herrera (alvherre@dcc.uchile.cl) wrote: > We could use BEGIN NESTED for starting a subtransaction, roll it back > with ROLLBACK NESTED or some such, and commit with COMMIT NESTED. But I > like SUBBEGIN etc best, and no one had an opinion when I asked. So the > current code has SUBBEGIN, SUBCOMMIT, SUBABORT. If people prefer Just to be pedantic and talking about consistency- Why SUBABORT instead of SUBROLLBACK? Stephen
On Tue, Jul 06, 2004 at 12:49:46PM -0400, Stephen Frost wrote: > * Alvaro Herrera (alvherre@dcc.uchile.cl) wrote: > > We could use BEGIN NESTED for starting a subtransaction, roll it back > > with ROLLBACK NESTED or some such, and commit with COMMIT NESTED. But I > > like SUBBEGIN etc best, and no one had an opinion when I asked. So the > > current code has SUBBEGIN, SUBCOMMIT, SUBABORT. If people prefer > > Just to be pedantic and talking about consistency- > Why SUBABORT instead of SUBROLLBACK? Just because it's ugly and too long ... I think the standard spelling is ROLLBACK, and ABORT is a Postgres extension. Since nested xacts are a Postgres extension, we might as well extend our own syntax :-) -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) La web junta la gente porque no importa que clase de mutante sexual seas, tienes millones de posibles parejas. Pon "buscar gente que tengan sexo con ciervos incendiánse", y el computador dirá "especifique el tipo de ciervo" (Jason Alexander)
On Tue, 2004-07-06 at 10:25, Alvaro Herrera wrote: > On Tue, Jul 06, 2004 at 08:15:14AM +0200, Dennis Bjorklund wrote: > > On Mon, 5 Jul 2004, Alvaro Herrera wrote: > > > > > > begin/end because they are already in an explicit/implicit transaction > > > > by default... How is the user/programmer to know when this is the case? > > > > > > I'm not sure I understand you. Of course you can issue begin/end. What > > > you can't do is issue begin/end inside a function -- you always use > > > subbegin/subcommit in that case. > > > > I've not understood why we need new tokens for this case. Maybe you've > > explained it somewhere that I've missed. But surely the server know if you > > are in a transaction or not, and can differentiate on the first BEGIN and > > the next BEGIN. > > I think the best argument for this is that we need a command to abort > the whole transaction tree, and another to commit the whole transaction > tree. Those _have_ to be ROLLBACK (or ABORT) and COMMIT (or END), > because the spec says they work like that and it would be hell for an > interface like JDBC if they didn't. So it's out of the picture to use > those commands to end a subtransaction. Why not rollback all or commit all? I really really don't like subbegin and subcommit. I get the feeling they'll cause more problems we haven't foreseen yet, but I can't put my finger on it. They just don't feel like "postgresql" to me. I'd rather see extra syntax to handle exceptions, like rollback all or whatnot, than subbegin et. al. > > Now, it's clear we need new commands to end a subtransaction. Do we > also want a different command for begin? I think so, just to be > consistent. Sorry, but I respectfully disagree that it's clear.
"Scott Marlowe" <smarlowe@qwest.net> writes: > Why not rollback all or commit all? > > I really really don't like subbegin and subcommit. I get the feeling > they'll cause more problems we haven't foreseen yet, but I can't put my > finger on it. Well I've already pointed out one problem. It makes it impossible to write generic code or reuse existing code and embed it within a transaction. Code meant to be a nested transaction within a larger transaction becomes non-interchangeable with code meant to be run on its own. I also have a different issue. The behaviour I'm expecting with most drivers will be to start a transaction immediately, and run every query within a subtransaction. This is what we've discussed previously with psql, but for the same reasons previously discussed I expect drivers to adopt the same approach, at least when not in autocommit mode. The goal would be to allow the application to control what happens when a given query returns an error and not force the application to roll the entire transaction back. This means the user can't use "BEGIN" or "END" at all himself. Since the driver would already have initiated a transaction itself. The *only* user-visible commands would become these awkward (and frankly, silly-sounding) "SUBBEGIN" and "SUBEND". I have an related question though. Will there be a out of band protocol method for controlling transaction status? If the v3 protocol allows the transaction status to be manipulated in binary messages that don't interact with user queries then a driver would still be able to reliably start and end transactions and nested transactions. If that were the case I guess I wouldn't care since a driver could then implement an external API that hid the irregularity of SUBfoo from the user and provided a consistent ->begin() ->end(). The driver could emulate this by inserting SUBfoo commands into the stream but then it would risk being subverted by user commands. -- greg
On Tue, 6 Jul 2004, Alvaro Herrera wrote: > We can later implement savepoints, which will have "SAVEPOINT foo" and > "ROLLBACK TO foo" as interface. (Note that a subtransaction is slightly > different from a savepoint, so we can't use ROLLBACK TO <foo> in > subtransactions because that has a different meaning in savepoints). What is the semantic difference? In my eye the subtransactions and the savepoints are basically the same thing except the label that is used. If that is the only difference? why are we implementing our own extension for subtransactions instead of implementing this standard feature. Of course the label stuff is a little more complicated, but all the really hard parts should be the same as what have already been done. The most naive implementation of the labels is to have a mapping from a label to the number of subcommit (for RELEASE SAVEPOINT) or subrolllbacks (for ROLLBACK TO SAVEPOINT) to execute. -- /Dennis Björklund
Dennis Bjorklund wrote: > On Tue, 6 Jul 2004, Alvaro Herrera wrote: > > >>We can later implement savepoints, which will have "SAVEPOINT foo" and >>"ROLLBACK TO foo" as interface. (Note that a subtransaction is slightly >>different from a savepoint, so we can't use ROLLBACK TO <foo> in >>subtransactions because that has a different meaning in savepoints). > > > What is the semantic difference? Savepoint "ROLLBACK TO foo" doesn't invalidate 'foo'. If "SAVEPOINT foo" is 'start new subtransaction foo', "ROLLBACK TO foo" must be 'roll back subtransaction foo and all children; start new subtransaction foo'. -O
On Wed, 7 Jul 2004, Oliver Jowett wrote: > Savepoint "ROLLBACK TO foo" doesn't invalidate 'foo'. If "SAVEPOINT foo" > is 'start new subtransaction foo', "ROLLBACK TO foo" must be 'roll back > subtransaction foo and all children; start new subtransaction foo'. If that is all there is, I much rather see this standard interface then a pg extension. -- /Dennis Björklund
Dennis Bjorklund wrote: > On Wed, 7 Jul 2004, Oliver Jowett wrote: > > >>Savepoint "ROLLBACK TO foo" doesn't invalidate 'foo'. If "SAVEPOINT foo" >>is 'start new subtransaction foo', "ROLLBACK TO foo" must be 'roll back >>subtransaction foo and all children; start new subtransaction foo'. > > > If that is all there is, I much rather see this standard interface then a > pg extension. So how do you propose supporting simple rollback of a subtransaction? It seems like an extension regardless of how it's done. -O
On Wed, 7 Jul 2004, Oliver Jowett wrote: > So how do you propose supporting simple rollback of a subtransaction? It > seems like an extension regardless of how it's done. If I understand you correctly what you want is a ROLLBACK TO SAVEPOINT foo; followed by a RELEASE SAVEPOINT foo; -- /Dennis Björklund
Dennis Bjorklund wrote:
> On Wed, 7 Jul 2004, Oliver Jowett wrote:
>
>
>>So how do you propose supporting simple rollback of a subtransaction? It
>>seems like an extension regardless of how it's done.
>
>
> If I understand you correctly what you want is a ROLLBACK TO SAVEPOINT
> foo; followed by a RELEASE SAVEPOINT foo;
Ugh.. nasty syntax and an extra empty transaction.
Also, how do you get an anonymous subtransaction? SAVEPOINT syntax would
seem to always require a name.
One of the use cases for subtransactions was to avoid rollback of the
entire transaction if there's an error in a single command -- you wrap
each command in a subtransaction and roll it back if it fails. If we
only have SAVEPOINT syntax this looks like:
-- Success case SAVEPOINT s_12345 INSERT INTO foo(...) VALUES (...) RELEASE SAVEPOINT s_12345
-- Error case SAVEPOINT s_12346 INSERT INTO foo(...) VALUES (...) ROLLBACK TO SAVEPOINT s_12346 RELEASE
SAVEPOINTs_12346
-- Repeat ad nauseam
This is pretty ugly. Given that the underlying mechanism is nested
subtransactions, why should it be necessary to jump through those sort
of hoops to gain access to them?
If you don't like adding extra commands, what about extending the
standard transaction control commands ("BEGIN NESTED" etc) instead?
-O
On Wed, 7 Jul 2004, Oliver Jowett wrote:
> > If I understand you correctly what you want is a ROLLBACK TO SAVEPOINT
> > foo; followed by a RELEASE SAVEPOINT foo;
>
> Ugh.. nasty syntax and an extra empty transaction.
If you translate it directly using only the primitives of the current
subbegin/subabort, yes. But that is not the only way to implement it. And
even if that was the first implementation due to not having time to make
it better before 7.5, then I still prefer a standard syntax that can be
improved then a non standard feature to be maintained for all future.
This is about the API to present to the user. The savepoint syntax is
standard, if we should invent our own way it should be for some real
benefit.
> Also, how do you get an anonymous subtransaction? SAVEPOINT syntax would
> seem to always require a name.
Yes, it does. But surely they can be nested so an inner use of name foo
hides an outer use of name foo. I'm not pretending to know all about the
standard savepoints, so I just assume they can be nested.
> One of the use cases for subtransactions was to avoid rollback of the
> entire transaction if there's an error in a single command -- you wrap
> each command in a subtransaction and roll it back if it fails. If we
> only have SAVEPOINT syntax this looks like:
>
> -- Success case
> SAVEPOINT s_12345
> INSERT INTO foo(...) VALUES (...)
> RELEASE SAVEPOINT s_12345
>
> -- Error case
> SAVEPOINT s_12346
> INSERT INTO foo(...) VALUES (...)
> ROLLBACK TO SAVEPOINT s_12346
> RELEASE SAVEPOINT s_12346
>
> -- Repeat ad nauseam
>
> This is pretty ugly. Given that the underlying mechanism is nested
> subtransactions,
So you do not want to use the standard syntax in order to save some tokens
in the source?
Also notice that the first and last statement is the same no matter if you
want to rollback or not. So it would be something like (with a nicer
savepoint name then yours):
SAVEPOINT insert;
INSERT INTO ....
... possible more work ...
if (some_error) ROLLBACK TO SAVEPOINT insert;
RELEASE SAVEPOINT insert;
I really don't see this as anything ugly with this. Maybe it doesn't fit
the current implementation, then lets change the implementation and not
just make an extension that fits a implementation.
> If you don't like adding extra commands, what about extending the
> standard transaction control commands ("BEGIN NESTED" etc) instead?
I'd like to use the ansi standard and hopefully portable syntax. I don't
see any real gains by having our own syntax. If the goal is just to save
some tokens I definetly see no reason. There might still be something more
to subtransactions, but I really have not seen it.
At the very least if we add extensions I would like to have a clear and
stated reason why it should be used instead of the standard feature. Every
time we add some syntax it has to be maintained forever and we lock in
users into postgresql. Something I don't like.
--
/Dennis Björklund
Dennis Bjorklund wrote: >>Also, how do you get an anonymous subtransaction? SAVEPOINT syntax would >>seem to always require a name. > > Yes, it does. But surely they can be nested so an inner use of name foo > hides an outer use of name foo. I'm not pretending to know all about the > standard savepoints, so I just assume they can be nested. The specs appear to say that reuse of a savepoint name moves the name rather than hiding it. There's also a concept of a savepoint level which seems to be essentially a namespace for savepoints, and provision for entering a new savepoint level during a call to a SQL function. > I'd like to use the ansi standard and hopefully portable syntax. I don't > see any real gains by having our own syntax. If the goal is just to save > some tokens I definetly see no reason. There might still be something more > to subtransactions, but I really have not seen it. My concern is that if we are building savepoints on top of nested subtransactions -- which is the approach so far -- then treating that system as something that only provides savepoints is counterproductive. Consider: SAVEPOINT point1 -- work 1 -- maybe ROLLBACK TO point1 SAVEPOINT point2 -- work 2 -- maybe ROLLBACK TO point2 SAVEPOINT point1 -- work 3 -- maybe ROLLBACK TO point1 SAVEPOINT point2 -- work 4 -- maybe ROLLBACK TOpoint2 -- repeat ad nauseam On the surface this looks cheap if you treat the transaction model as one flat transaction with N savepoints (which is what SAVEPOINT seems to be about doing, looking at it independent of animplementation) -- there are only two savepoints active at any particular point. But if the underlying model is actually nested transactions, you are going to end up with a very large number of active nested transactions, since at the point the server sees the reuse of 'point1' it's too late to commit the transaction maintaining that savepoint safely. This can be fixed by explicit RELEASE SAVEPOINTs after each block of work, but it's not obvious from the savepoint model why this is needed -- you only have 2 savepoints active anyway! Also: SAVEPOINT point1 DECLARE CURSOR foocursor FOR SELECT * from footable -- work RELEASE SAVEPOINT point1 FETCH FORWARD10 FROM foocursor -- oops, foocursor is no longer open That behaviour just doesn't fit into the flat-transaction-with-savepoints model at all. I guess the question is: are we adding a nested transaction facility or a savepoint facility? It seems to me we're doing the former, and the savepoint syntax plus mostly-standard savepoint behaviour is just compatibility icing. If that's the case, I'd prefer a syntax that reflects the nested-transaction nature of the beast. -O
On Wed, 2004-07-07 at 00:16, Dennis Bjorklund wrote: > On Tue, 6 Jul 2004, Alvaro Herrera wrote: > > > We can later implement savepoints, which will have "SAVEPOINT foo" and > > "ROLLBACK TO foo" as interface. (Note that a subtransaction is slightly > > different from a savepoint, so we can't use ROLLBACK TO <foo> in > > subtransactions because that has a different meaning in savepoints). > > What is the semantic difference? One is in the SQL spec? For that reason alone, we should probably eventually have the savepoint syntax work.
On Tue, 2004-07-06 at 23:36, Greg Stark wrote: > "Scott Marlowe" <smarlowe@qwest.net> writes: > > > Why not rollback all or commit all? > > > > I really really don't like subbegin and subcommit. I get the feeling > > they'll cause more problems we haven't foreseen yet, but I can't put my > > finger on it. > > Well I've already pointed out one problem. It makes it impossible to write > generic code or reuse existing code and embed it within a transaction. Code > meant to be a nested transaction within a larger transaction becomes > non-interchangeable with code meant to be run on its own. Would a rollback N / abort N where N is the number of levels to rollback / abort work?
Scott Marlowe wrote: >On Tue, 2004-07-06 at 23:36, Greg Stark wrote: > > >>"Scott Marlowe" <smarlowe@qwest.net> writes: >> >> >> >>>Why not rollback all or commit all? >>> >>>I really really don't like subbegin and subcommit. I get the feeling >>>they'll cause more problems we haven't foreseen yet, but I can't put my >>>finger on it. >>> >>> >>Well I've already pointed out one problem. It makes it impossible to write >>generic code or reuse existing code and embed it within a transaction. Code >>meant to be a nested transaction within a larger transaction becomes >>non-interchangeable with code meant to be run on its own. >> >> > >Would a rollback N / abort N where N is the number of levels to rollback >/ abort work? > > > Only, if you know the number of levels you are deep in the transaction. "ROLLBACK n" and "ROLLBACK ALL" together would be good alternatives to unwind nested transaction. Perhaps a function for pg_transaction_nested_level( ) or a pg_transaction_nested_level variable could help in this. Again, these are just opinions.
Alvaro, Hackers:
I've been giving this some thought. Here's what I came up with:
We should NOT use the savepoint syntax. Alvaro's Nested Transactions are not
savepoints, they don't meet the spec, and they don't behave the same. Using
standard syntax for a non-standard feature will, in my opinion, cause more
confusion than using extension syntax for what is, after all, a
PostgreSQL-specific feature.
HOWEVER, other databases already have nested transactions. We could do worse
than to imitate their syntax; since the syntax we use is arbitrary, we might
as well pick syntax which minimizes the pain of porting applications. Of
the other databases, the most important to imitate for this reason are of
couse SQL Server and Oracle, since those to cover some 80% of DBAs.
However, Oracle does not support
SQL Server uses:
Begin main transaction: BEGIN { TRANSACTION | WORK }
Begin inner transaction: BEGIN { TRANSACTION | WORK }
Commit inner transaction: COMMIT { TRANSACTION | WORK }
Commit all transactions: Not supported
Rollback inner transaction: Not supported
Rollback all transanctions: ROLLBACK { TRANSACTION | WORK }
Please note that, according to the above, MSSQL does not really support nested
transactions; the inner transactions *cannot* be rolled back, making them
useless. There are numerous online discussions about this.
Sybase uses identical syntax, except that Sybase supports Savepoints via an
extension of the BEGIN/COMMIT syntax:
Begin main transaction: BEGIN { TRANSACTION | WORK }
Begin inner transaction: BEGIN TRANSACTION _name_
Commit inner transaction: COMMIT { TRANSACTION _name_ }
Commit all transactions: Not supported
Rollback inner transaction: ROLLBACK TRANSACTION _name_
Rollback all transanctions: ROLLBACK { TRANSACTION | WORK }
This means that we CANNOT maintain compatibility with other databases without
supporting SAVEPOINT syntax, which we are not yet ready to do. As a result,
I would propose the following syntax:
Begin main transaction: BEGIN { TRANSACTION | WORK }
Begin inner transaction: BEGIN { TRANSACTION | WORK }
Commit inner transaction: COMMIT { TRANSACTION | WORK }
Commit all transactions: COMMIT ALL
Rollback inner transaction: ROLLBACK { TRANSACTION }
Rollback all transanctions: ROLLBACK ALL
This would have the flaw of appearing to support SQL Server syntax, while
actually having a different effect (that is, SQL Server programmers would
assume that a ROLLBACK would abort everything, but it wouldn't). If we
wanted to maintain compatibility in this regard, for easy porting of SQL
Server applications, we would:
Begin main transaction: BEGIN { TRANSACTION | WORK }
Begin inner transaction: BEGIN { TRANSACTION | WORK }
Commit inner transaction: COMMIT { TRANSACTION | WORK }
Commit all transactions: COMMIT ALL
Rollback inner transaction: ROLLBACK NESTED
Rollback all transanctions: ROLLBACK { TRANSACTION }
... but this puts us in the bad position of supporting somebody else's
logically inconsistent syntax. Thoughts?
--
Josh Berkus
Aglio Database Solutions
San Francisco
On Thu, Jul 08, 2004 at 10:40:36AM -0700, Josh Berkus wrote:
> This means that we CANNOT maintain compatibility with other databases without
> supporting SAVEPOINT syntax, which we are not yet ready to do. As a result,
> I would propose the following syntax:
>
> Begin main transaction: BEGIN { TRANSACTION | WORK }
> Begin inner transaction: BEGIN { TRANSACTION | WORK }
> Commit inner transaction: COMMIT { TRANSACTION | WORK }
> Commit all transactions: COMMIT ALL
> Rollback inner transaction: ROLLBACK { TRANSACTION }
> Rollback all transanctions: ROLLBACK ALL
We can _not_ do this. The reason is that COMMIT and ROLLBACK are
defined per spec to end the transaction. So they have to end the
transaction.
Keep in mind that a nested transaction _is not_ a transaction. You
cannot commit it; it doesn't behave atomically w.r.t. other concurrent
transactions. It is not a transaction in the SQL meaning of a
transaction.
So, when I say "it has to end the transaction" it cannot just end the
current nested transaction. It has to end the _real_ transaction.
My proposal would be:
1. Begin main transaction: BEGIN { TRANSACTION | WORK }
2. Commit main (all) transaction: COMMIT { TRANSACTION | WORK }
3. Rollback main (all) transaction: ROLLBACK { TRANSACTION }
4. Begin inner transaction: BEGIN NESTED { TRANSACTION | WORK }
5. Commit inner transaction: COMMIT NESTED { TRANSACTION | WORK }
6. Rollback inner transaction: ROLLBACK NESTED { TRANSACTION }
1, 2 and 3 are not negotiable. 4, 5 and 6 are.
--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Essentially, you're proposing Kevlar shoes as a solution for the problem
that you want to walk around carrying a loaded gun aimed at your foot.
(Tom Lane)
Alvaro,
> My proposal would be:>> 1. Begin main transaction: BEGIN { TRANSACTION | WORK }> 2. Commit main (all) transaction:
COMMIT{ TRANSACTION | WORK }> 3. Rollback main (all) transaction: ROLLBACK { TRANSACTION }>> 4. Begin inner
transaction:BEGIN NESTED { TRANSACTION | WORK }> 5. Commit inner transaction: COMMIT NESTED { TRANSACTION | WORK }> 6.
Rollbackinner transaction: ROLLBACK NESTED { TRANSACTION }>
I agree with your 1,2 and 3, for the reasons you specify.
I don't like your proposal for 5, because using the keyword COMMIT
implies something that really isn't true IMHO. This is due to the fact
as you point out subtransactions aren't really transactions. So when
you 'commit' a subtransaction you are not making the changes permanent
like a regular transaction. Instead you are saying these changes are OK
and the real transaction gets to decide if these changes should be
committed (or not). It is only the real transaction that ever does a
COMMIT (i.e. makes the changes permanent for others to see). IMHO it is
for these reasons that the standard SAVEPOINT syntax doesn't have a
concept of committing a savepoint, only of rolling back to a savepoint.
thanks,
--Barry
Alvaro Herrera wrote:
> On Thu, Jul 08, 2004 at 10:40:36AM -0700, Josh Berkus wrote:
>
>
>>This means that we CANNOT maintain compatibility with other databases without
>>supporting SAVEPOINT syntax, which we are not yet ready to do. As a result,
>>I would propose the following syntax:
>>
>>Begin main transaction: BEGIN { TRANSACTION | WORK }
>>Begin inner transaction: BEGIN { TRANSACTION | WORK }
>>Commit inner transaction: COMMIT { TRANSACTION | WORK }
>>Commit all transactions: COMMIT ALL
>>Rollback inner transaction: ROLLBACK { TRANSACTION }
>>Rollback all transanctions: ROLLBACK ALL
>
>
> We can _not_ do this. The reason is that COMMIT and ROLLBACK are
> defined per spec to end the transaction. So they have to end the
> transaction.
>
> Keep in mind that a nested transaction _is not_ a transaction. You
> cannot commit it; it doesn't behave atomically w.r.t. other concurrent
> transactions. It is not a transaction in the SQL meaning of a
> transaction.
>
> So, when I say "it has to end the transaction" it cannot just end the
> current nested transaction. It has to end the _real_ transaction.
>
>
> My proposal would be:
>
> 1. Begin main transaction: BEGIN { TRANSACTION | WORK }
> 2. Commit main (all) transaction: COMMIT { TRANSACTION | WORK }
> 3. Rollback main (all) transaction: ROLLBACK { TRANSACTION }
>
> 4. Begin inner transaction: BEGIN NESTED { TRANSACTION | WORK }
> 5. Commit inner transaction: COMMIT NESTED { TRANSACTION | WORK }
> 6. Rollback inner transaction: ROLLBACK NESTED { TRANSACTION }
>
>
> 1, 2 and 3 are not negotiable. 4, 5 and 6 are.
>
Alvaro Herrera wrote:
> On Thu, Jul 08, 2004 at 10:40:36AM -0700, Josh Berkus wrote:
>
> > This means that we CANNOT maintain compatibility with other databases without
> > supporting SAVEPOINT syntax, which we are not yet ready to do. As a result,
> > I would propose the following syntax:
> >
> > Begin main transaction: BEGIN { TRANSACTION | WORK }
> > Begin inner transaction: BEGIN { TRANSACTION | WORK }
> > Commit inner transaction: COMMIT { TRANSACTION | WORK }
> > Commit all transactions: COMMIT ALL
> > Rollback inner transaction: ROLLBACK { TRANSACTION }
> > Rollback all transanctions: ROLLBACK ALL
>
> We can _not_ do this. The reason is that COMMIT and ROLLBACK are
> defined per spec to end the transaction. So they have to end the
> transaction.
>
> Keep in mind that a nested transaction _is not_ a transaction. You
> cannot commit it; it doesn't behave atomically w.r.t. other concurrent
> transactions. It is not a transaction in the SQL meaning of a
> transaction.
>
> So, when I say "it has to end the transaction" it cannot just end the
> current nested transaction. It has to end the _real_ transaction.
>
>
> My proposal would be:
>
> 1. Begin main transaction: BEGIN { TRANSACTION | WORK }
> 2. Commit main (all) transaction: COMMIT { TRANSACTION | WORK }
> 3. Rollback main (all) transaction: ROLLBACK { TRANSACTION }
>
> 4. Begin inner transaction: BEGIN NESTED { TRANSACTION | WORK }
> 5. Commit inner transaction: COMMIT NESTED { TRANSACTION | WORK }
> 6. Rollback inner transaction: ROLLBACK NESTED { TRANSACTION }
>
>
> 1, 2 and 3 are not negotiable. 4, 5 and 6 are.
Let me jump in on this.
The initial proposal from Alvaro was to do SUBBEGIN/SUBCOMMIT. This has
the advantage of allowing BEGIN/COMMIT to commit the entire transaction,
and it is a keyword we can use in plpgsql that doesn't confuse
BEGIN/END.
The disadvantages are:
o adds prefix to keyword (SUB) which we don't do other placeso doesn't work well with other xact synonyms like
BEGIN/ENDor START TRANSACTION/COMMIT TRANSACTION.
Alvaro wants BEGIN/COMMIT to remain spec-compliant and commit the entire
transaction. One idea was to do BEGIN NESTED/COMMIT NESTED, but does
that allow plpgsql to use it? If not, it seems pretty useless. Imagine:
BEGIN NESTED = 3;
or something like that.
As far as savepoints, yes, we should support them. Josh is saying our
implementation isn't 100% spec compliant. In what way does it differ?
As far as implementing only savepoints, look at this:
BEGIN;BEGIN;INSERT INTO ...;COMMIT;BEGIN;INSERT INTO ...;COMMIT;BEGIN;INSERT INTO ...;COMMIT;
With savepoints, it looks pretty strange:
BEGIN;SAVEPOINT x1;INSERT INTO ...;SAVEPOINT x2;INSERT INTO ...;SAVEPOINT x3;INSERT INTO ...;
or with RELEASE:
BEGIN;SAVEPOINT x1;INSERT INTO ...;RELEASE SAVEPOINT x1;SAVEPOINT x1;INSERT INTO ...;RELEASE SAVEPOINT x1;SAVEPOINT
x1;INSERTINTO ...;RELEASE SAVEPOINT x1;
Yea, I guess it works. With nested transactions, the SQL mimics the
nested structure of many application languages, while savepoints look
like an add-on to SQL.
-- 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
ISTM - my summary would be 1. We seem to agree we should support SAVEPOINTs 2. We seem to agree that BEGIN/COMMIT should stay unchanged... > With savepoints, it looks pretty strange: > > BEGIN; > SAVEPOINT x1; > INSERT INTO ...; > SAVEPOINT x2; > INSERT INTO ...; > SAVEPOINT x3; > INSERT INTO ...; > This isn't how you would use SAVEPOINTs...look at this... BEGIN display one screen to user - book the flightINSERT INTO ...INSERT INTO ...UPDATE ...SAVEPOINT displayanother related screen - book the hotelINSERT INTODELETEUPDATEUPDATESAVEPOINT offer confirmation screen COMMIT (or ROLLBACK) RELEASE SAVEPOINT isn't used that often... > or with RELEASE: > > BEGIN; > SAVEPOINT x1; > INSERT INTO ...; > RELEASE SAVEPOINT x1; > SAVEPOINT x1; > INSERT INTO ...; > RELEASE SAVEPOINT x1; > SAVEPOINT x1; > INSERT INTO ...; > RELEASE SAVEPOINT x1; > We need to be careful to differentiate from the statement-level abort behaviour of other RDBMS and the behaviour of SAVEPOINT. It is theoretically possible to implement them both using nested transactions, but that doesn't mean they're the same thing. If a statement has an error, then PostgreSQL currently rolls back the entire transaction (transaction level abort). It would be good if this was not the behaviour, since other programs written for other RDBMS do NOT exhibit this behaviour - this matters a lot if/when statements error. This behaviour MUST happen implicitly, without additional SQL statements, otherwise its not the same behaviour. The effect is AS IF the user had issued the sequence of statements shown above - but they do not ACTUALLY issue those, hence the reason why the above sequences look a little wierd. In the current syntax we're discussing, Oracle's behaviour looks like this (with all statements in brackets being issued implicitly...) - using the same example I gave above BEGIN(SUBBEGIN) (SUBBEGIN) INSERT INTO ... (SUBCOMMIT) (SUBBEGIN) INSERT INTO ... (SUBCOMMIT) (SUBBEGIN) UPDATE ... (SUBCOMMIT)SUBCOMMIT(SUBBEGIN) (SUBBEGIN) INSERT INTO (SUBCOMMIT) (SUBBEGIN) DELETE (SUBCOMMIT) (SUBBEGIN) UPDATE (SUBCOMMIT) (SUBBEGIN) UPDATE (SUBCOMMIT)SUBCOMMIT COMMIT (or ROLLBACK) Note that you CANNOT choose to rollback the statement you're executing...it just does so if it fails. As to whether any of that behaviour is strange... That depends upon your viewpoints and experience, so I could understand that. The situation remains....it IS the behaviour and my understanding is that this was the behaviour we were seeking to emulate? My confusion with the SAVEPOINT/NESTED debate is - how do you know whether you SHOULD HAVE issued a SUBBEGIN? When I issue a SAVEPOINT, I don't care whether or not I've issued a SUBBEGIN before, I just do it and it works. The only way to do this seems to be to avoid having a flat nesting structure, but to have an infinite descent on one part of the nesting tree...so each statement IMPLICTLY starts with a SUBBEGIN, and SAVEPOINT and COMMIT just count 'em so they know how many SUBCOMMITs to issue to get back up again. e.g. BEGIN (SUBBEGIN) INSERT...(SUBBEGIN)INSERT.... (SUBBEGIN) INSERT... SAVEPOINT (== SUBCOMMIT, SUBCOMMIT, SUBCOMMIT) (SUBBEGIN) INSERT...(SUBBEGIN)INSERT.... (SUBBEGIN) INSERT... COMMIT (== SUBCOMMIT, SUBCOMMIT, SUBCOMMIT, COMMIT) Best Regards, Simon Riggs
> My proposal would be:
>
> 1. Begin main transaction: BEGIN { TRANSACTION | WORK }
> 2. Commit main (all) transaction: COMMIT { TRANSACTION | WORK }
> 3. Rollback main (all) transaction: ROLLBACK { TRANSACTION }
>
> 4. Begin inner transaction: BEGIN NESTED { TRANSACTION | WORK }
> 5. Commit inner transaction: COMMIT NESTED { TRANSACTION | WORK }
> 6. Rollback inner transaction: ROLLBACK NESTED { TRANSACTION }
>
>
> 1, 2 and 3 are not negotiable. 4, 5 and 6 are.
Hmm, 1-3 are at least negotiable for the abbreviated form 'BEGIN'
and 'END'. I think we could differentiate those.
The standard only has 'BEGIN TRANSACTION' and 'COMMIT [WORK]'
and 'ROLLBACK [WORK]'. I agree that we are not allowed to change
the semantics of those non abbreviated forms.
How about:
1. Begin main tx: BEGIN WORK | BEGIN TRANSACTION
2. Commit main (all) transaction: COMMIT [ TRANSACTION | WORK ]
3. Rollback main (all) transaction: ROLLBACK [ TRANSACTION | WORK ]
4. BEGIN: starts eighter a main or a subtransaction (for plsql BEGIN SUB)
5. END: commits nested, maybe also abort a nested tx that is already in abort state (for plsql END SUB)
6. ROLLBACK SUB[TRANSACTION]: rolls subtx back
Andreas
> As far as implementing only savepoints, look at this: > > BEGIN; > BEGIN; > INSERT INTO ...; > COMMIT; > BEGIN; > INSERT INTO ...; > COMMIT; > BEGIN; > INSERT INTO ...; > COMMIT; > > With savepoints, it looks pretty strange: > > BEGIN; > SAVEPOINT x1; > INSERT INTO ...; > SAVEPOINT x2; If you meant same as your nested example, it would be: BEGIN TRANSACTION;SAVEPOINT x;INSERT INTO ...;SAVEPOINT x; -- this implicitly commits previous subtxn xINSERT INTO ...;SAVEPOINTx;INSERT INTO ...; COMMIT; Andreas
Simon Riggs wrote:
>ISTM - my summary would be
>1. We seem to agree we should support SAVEPOINTs
>
>2. We seem to agree that BEGIN/COMMIT should stay unchanged...
>
>
>
>>With savepoints, it looks pretty strange:
>>
>>BEGIN;
>> SAVEPOINT x1;
>> INSERT INTO ...;
>> SAVEPOINT x2;
>> INSERT INTO ...;
>> SAVEPOINT x3;
>> INSERT INTO ...;
>>
>>
>>
>
>This isn't how you would use SAVEPOINTs...look at this...
>
>BEGIN
> display one screen to user - book the flight
> INSERT INTO ...
> INSERT INTO ...
> UPDATE ...
> SAVEPOINT
> display another related screen - book the hotel
> INSERT INTO
> DELETE
> UPDATE
> UPDATE
> SAVEPOINT
> offer confirmation screen
>COMMIT (or ROLLBACK)
>
>
No, SAVEPOINT is not some kind of intermediate commit, but a point where
a rollback can rollback to.
Look at this oracle stuff when googling for SAVEPOINT ROLLBACK:
BEGIN SAVEPOINT before_insert_programmers; insert_programmers (p_deptno); EXCEPTION WHEN
OTHERSTHEN ROLLBACK TO before_insert_programmers; END;
There's no need for an intermediate commit, because the top level
rollback would overrule it (if not, it would be an independent
transaction, not nested).
I'd opt for BEGIN as a start of a subtransaction (no need for special
semantics in plpgsql), the corresponding END simply changes the
transaction context to the parent level.
BEGIN is an unnamed savepoint in this case, so if we have SAVEPOINT
<name> we'd also have the corresponding ROLLBACK TO [SAVEPOINT] <name>.
For the unnamed savepoint ROLLBACK INNER or ROLLBACK SUB could be used.
This would be an extension to oracle's usage, which seems quite
reasonable to me.
Regards,
Andreas
> I'd opt for BEGIN as a start of a subtransaction (no need for special > semantics in plpgsql), the corresponding END simply changes the > transaction context to the parent level. But 'BEGIN' in plpgsql does not start a [sub]transaction, it starts a statement block. Are we intending to change that ? I think not. Andreas
Zeugswetter Andreas SB SD wrote: >>I'd opt for BEGIN as a start of a subtransaction (no need for special >>semantics in plpgsql), the corresponding END simply changes the >>transaction context to the parent level. >> >> > >But 'BEGIN' in plpgsql does not start a [sub]transaction, it starts a >statement block. Are we intending to change that ? I think not. > > > There are two possibilities: Either BEGIN *does* start a subtransaction, or BEGIN does not. I don't see how two nesting level hierarchies in a function should be handleable, i.e. having independent levels of statements blocks and subtransactions. BEGIN [whatever] suggests that there's also a statement closing that block of [whatever], but it's very legal for subtransactions to have no explicit end; the top level COMMIT does it all. The SAVEPOINT semantic seems much more appropriate to describe statement block independent transactions in this case, so if both is implemented for SQL, savepoint only seems enough for plpgsql. Regards, Andreas
> >But 'BEGIN' in plpgsql does not start a [sub]transaction, it starts a > >statement block. Are we intending to change that ? I think not. > > > > > > > There are two possibilities: > Either BEGIN *does* start a subtransaction, or BEGIN does not. I don't > see how two nesting level hierarchies in a function should be > handleable, i.e. having independent levels of statements blocks and > subtransactions. > > BEGIN [whatever] suggests that there's also a statement closing that > block of [whatever], but it's very legal for subtransactions to have no > explicit end; the top level COMMIT does it all. An 'END SUB' after a 'BEGIN SUB' in plpgsql could be required, and could mean start/end block and subtx. I do not really see a downside. But, it would imho only make sense if the 'END SUB' would commit sub or abort sub iff subtx is in aborted state (see my prev posting) Andreas
Andreas Pflug wrote: > Simon Riggs wrote: > >> ISTM - my summary would be >> 1. We seem to agree we should support SAVEPOINTs >> >> 2. We seem to agree that BEGIN/COMMIT should stay unchanged... >> >> >> >>> With savepoints, it looks pretty strange: >>> >>> BEGIN; >>> SAVEPOINT x1; >>> INSERT INTO ...; >>> SAVEPOINT x2; >>> INSERT INTO ...; >>> SAVEPOINT x3; >>> INSERT INTO ...; >>> >>> >> >> >> This isn't how you would use SAVEPOINTs...look at this... >> >> BEGIN >> display one screen to user - book the flight >> INSERT INTO ... >> INSERT INTO ... >> UPDATE ... >> SAVEPOINT >> display another related screen - book the hotel >> INSERT INTO >> DELETE >> UPDATE >> UPDATE >> SAVEPOINT >> offer confirmation screen >> COMMIT (or ROLLBACK) >> >> > > No, SAVEPOINT is not some kind of intermediate commit, but a point > where a rollback can rollback to. > Look at this oracle stuff when googling for SAVEPOINT ROLLBACK: > > BEGIN > SAVEPOINT before_insert_programmers; > insert_programmers (p_deptno); > EXCEPTION > WHEN OTHERS THEN ROLLBACK TO before_insert_programmers; > END; > > There's no need for an intermediate commit, because the top level > rollback would overrule it (if not, it would be an independent > transaction, not nested). > > I'd opt for BEGIN as a start of a subtransaction (no need for special > semantics in plpgsql), the corresponding END simply changes the > transaction context to the parent level. > BEGIN is an unnamed savepoint in this case, so if we have SAVEPOINT > <name> we'd also have the corresponding ROLLBACK TO [SAVEPOINT] > <name>. For the unnamed savepoint ROLLBACK INNER or ROLLBACK SUB could > be used. > This would be an extension to oracle's usage, which seems quite > reasonable to me. > What happens when you use subtransactions? I think there might be a visibility issue and how far do you unwind the depth of subtransactions or transactions? BEGIN UPDATE A SAVEPOINT X BEGIN BEGIN UPDATE B BEGIN UPDATE C ROLLBACK TO SAVEPOINT X COMMIT COMMIT COMMIT COMMIT Or SAVEPOINT X BEGIN UPDATE A ROLLBACK TO SAVEPOINT X COMMIT
On Fri, Jul 09, 2004 at 10:38:15AM -0500, Thomas Swan wrote: > visibility issue and how far do you unwind the depth of subtransactions > or transactions? > > BEGIN > UPDATE A > SAVEPOINT X > BEGIN > BEGIN > UPDATE B > BEGIN > UPDATE C > ROLLBACK TO SAVEPOINT X What happens here is that the user will go nuts. We will have a prominent entry in the docs: "using both nested transactions and savepoints inside a transaction can cause confusion. We recommend you stick to one or the other." Or something like that. (What would really happen: when ROLLBACK TO SAVEPOINT X is executed, nested transactions created after the SAVEPOINT will be closed.) So this is another reason why we should use COMMIT to close a nested transaction: it may refer to a transaction that is already closed because the user got confused. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "I can't go to a restaurant and order food because I keep looking at the fonts on the menu. Five minutes later I realize that it's also talking about food" (Donald Knuth)
> > An 'END SUB' after a 'BEGIN SUB' in plpgsql could be required, and could > mean start/end block and subtx. I do not really see a downside. > But, it would imho only make sense if the 'END SUB' would commit sub > or abort sub iff subtx is in aborted state (see my prev posting) > > Andreas > Hello, is good idea use keywords "begin sub" and "end sub"? Programmers like me will be an problems with reading and writing SP, because begin sub and mostly end sub are keywords from visual basic with different sense. BEGIN SUBTRANSACTION and END SUBTRANSACTION is longer but more readable regards Pavel Stehule
Pavel Stehule wrote: > > > > An 'END SUB' after a 'BEGIN SUB' in plpgsql could be required, and could > > mean start/end block and subtx. I do not really see a downside. > > But, it would imho only make sense if the 'END SUB' would commit sub > > or abort sub iff subtx is in aborted state (see my prev posting) > > > > Andreas > > > Hello, > > is good idea use keywords "begin sub" and "end sub"? Programmers like me > will be an problems with reading and writing SP, because begin sub and > mostly end sub are keywords from visual basic with different sense. > BEGIN SUBTRANSACTION and END SUBTRANSACTION is longer but more readable I think we agreed on BEGIN NESTED/COMMIT NESTED, and START NESTED TRANSACTION and COMMIT NESTED TRANSACTION. -- 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 Fri, 9 Jul 2004, Bruce Momjian wrote: > I think we agreed on BEGIN NESTED/COMMIT NESTED, and START NESTED > TRANSACTION and COMMIT NESTED TRANSACTION. Should I read this as pg will get its own implementation of sub transactions and not implement the almost equivalent standard (sql99) savepoint feature? Will we in the future see savepoints as well? And when that happen, should we then recommend that people use the standard feature and stay away from the pg only feature? Doesn't anyone but me think is all backwards? -- /Dennis Björklund
On Fri, 2004-07-09 at 11:45, Andreas Pflug wrote: > Simon Riggs wrote: > > >ISTM - my summary would be > >1. We seem to agree we should support SAVEPOINTs > > > >2. We seem to agree that BEGIN/COMMIT should stay unchanged... > > > > > > > >>With savepoints, it looks pretty strange: > >> > >>BEGIN; > >> SAVEPOINT x1; > >> INSERT INTO ...; > >> SAVEPOINT x2; > >> INSERT INTO ...; > >> SAVEPOINT x3; > >> INSERT INTO ...; > >> > >> > >> > > > >This isn't how you would use SAVEPOINTs...look at this... > > > >BEGIN > > display one screen to user - book the flight > > INSERT INTO ... > > INSERT INTO ... > > UPDATE ... > > SAVEPOINT > > display another related screen - book the hotel > > INSERT INTO > > DELETE > > UPDATE > > UPDATE > > SAVEPOINT > > offer confirmation screen > >COMMIT (or ROLLBACK) > > > > > > No, SAVEPOINT is not some kind of intermediate commit, but a point where > a rollback can rollback to. Hmmm....I'm not sure what you mean by "No". The SAVEPOINT is somewhere you can ROLLBACK to, yes - exactly what I'm saying. I've not introduced any concept of "intermediate commit"... Do you agree that my example is valid Oracle SQL? Best Regards, Simon Riggs
On Fri, Jul 09, 2004 at 07:10:06PM +0200, Dennis Bjorklund wrote: > On Fri, 9 Jul 2004, Bruce Momjian wrote: > > > I think we agreed on BEGIN NESTED/COMMIT NESTED, and START NESTED > > TRANSACTION and COMMIT NESTED TRANSACTION. > > Should I read this as pg will get its own implementation of sub > transactions and not implement the almost equivalent standard (sql99) > savepoint feature? I think we should get both. Clearly savepoints do not allow for a snapshot to be released; nested xacts do. OTOH savepoints are trivial to implement once nested xacts are in place. They are only syntactic sugar. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "Pensar que el espectro que vemos es ilusorio no lo despoja de espanto, sólo le suma el nuevo terror de la locura" (Perelandra, CSLewis)
On Fri, 2004-07-09 at 16:47, Alvaro Herrera wrote: > On Fri, Jul 09, 2004 at 10:38:15AM -0500, Thomas Swan wrote: > > > visibility issue and how far do you unwind the depth of subtransactions > > or transactions? > > > > BEGIN > > UPDATE A > > SAVEPOINT X > > BEGIN > > BEGIN > > UPDATE B > > BEGIN > > UPDATE C > > ROLLBACK TO SAVEPOINT X > > What happens here is that the user will go nuts. We will have a > prominent entry in the docs: "using both nested transactions and > savepoints inside a transaction can cause confusion. We recommend you > stick to one or the other." Or something like that. > > (What would really happen: when ROLLBACK TO SAVEPOINT X is executed, > nested transactions created after the SAVEPOINT will be closed.) > > So this is another reason why we should use COMMIT to close a nested > transaction: it may refer to a transaction that is already closed > because the user got confused. Agreed. Could we put two modes of operation in? i.e. if you use SAVEPOINTs/ROLLBACK TO SAVEPOINT, then you're not allowed to use nested transactions (and vice versa - so they are mutually exclusive)... Best Regards, Simon Riggs
On Fri, 9 Jul 2004, Alvaro Herrera wrote: > Clearly savepoints do not allow for a snapshot to be released; nested > xacts do. Why not? > OTOH savepoints are trivial to implement once nested xacts are in place. > They are only syntactic sugar. Not only, but simple yes. I'm just opposed to having the non standard syntax added for the little gain they give over just having standard savepoints. -- /Dennis Björklund
On Fri, Jul 09, 2004 at 09:07:58PM +0200, Dennis Bjorklund wrote: > On Fri, 9 Jul 2004, Alvaro Herrera wrote: > > > Clearly savepoints do not allow for a snapshot to be released; nested > > xacts do. > > Why not? What is it? -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "Pensar que el espectro que vemos es ilusorio no lo despoja de espanto, sólo le suma el nuevo terror de la locura" (Perelandra, CSLewis)
Alvaro Herrera wrote: > On Fri, Jul 09, 2004 at 09:07:58PM +0200, Dennis Bjorklund wrote: > > On Fri, 9 Jul 2004, Alvaro Herrera wrote: > > > > > Clearly savepoints do not allow for a snapshot to be released; nested > > > xacts do. > > > > Why not? > > What is it? Simon posted it. It is called RELEASE: > BEGIN; > SAVEPOINT x1; > INSERT INTO ...; > RELEASE SAVEPOINT x1; > SAVEPOINT x1; > INSERT INTO ...; > RELEASE SAVEPOINT x1; > SAVEPOINT x1; > INSERT INTO ...; > RELEASE SAVEPOINT x1; -- 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 Fri, 2004-07-09 at 20:34, Bruce Momjian wrote: > Alvaro Herrera wrote: > > On Fri, Jul 09, 2004 at 09:07:58PM +0200, Dennis Bjorklund wrote: > > > On Fri, 9 Jul 2004, Alvaro Herrera wrote: > > > > > > > Clearly savepoints do not allow for a snapshot to be released; nested > > > > xacts do. > > > > > > Why not? > > > > What is it? > > Simon posted it. It is called RELEASE: > > > BEGIN; > > SAVEPOINT x1; > > INSERT INTO ...; > > RELEASE SAVEPOINT x1; > > SAVEPOINT x1; > > INSERT INTO ...; > > RELEASE SAVEPOINT x1; > > SAVEPOINT x1; > > INSERT INTO ...; > > RELEASE SAVEPOINT x1; Yes, this is the DB2 and SQLAnywhere syntax. Oracle uses ROLLBACK TO SAVEPOINT...identical pretty much. Oracle's support of autonomous transactions looks to be identical to nested transactions (Alvaro's advice required there...). They don't allow you to explicitly call them, but you can use BEGIN/COMMIT in a host program that calls a stored procedure, which also contains BEGIN/COMMIT, effectively giving nested txns. (...hopefully clearing up any discussion on "intermediate commits" whoever mentioned those...) Best regards, Simon Riggs
On Fri, Jul 09, 2004 at 03:34:47PM -0400, Bruce Momjian wrote: > Alvaro Herrera wrote: > > On Fri, Jul 09, 2004 at 09:07:58PM +0200, Dennis Bjorklund wrote: > > > On Fri, 9 Jul 2004, Alvaro Herrera wrote: > > > > > > > Clearly savepoints do not allow for a snapshot to be released; nested > > > > xacts do. > > > > > > Why not? > > > > What is it? > > Simon posted it. It is called RELEASE: We can't actually release anything (commit the subtransactions), because they may be savepoints established after that point, and they are logically "inside" the previously established ones. At RELEASE we can't really release -- we just lose the name and thus the opportunity to rollback to it. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) Major Fambrough: You wish to see the frontier? John Dunbar: Yes sir, before it's gone.
Alvaro Herrera wrote: > On Fri, Jul 09, 2004 at 03:34:47PM -0400, Bruce Momjian wrote: > > Alvaro Herrera wrote: > > > On Fri, Jul 09, 2004 at 09:07:58PM +0200, Dennis Bjorklund wrote: > > > > On Fri, 9 Jul 2004, Alvaro Herrera wrote: > > > > > > > > > Clearly savepoints do not allow for a snapshot to be released; nested > > > > > xacts do. > > > > > > > > Why not? > > > > > > What is it? > > > > Simon posted it. It is called RELEASE: > > We can't actually release anything (commit the subtransactions), because > they may be savepoints established after that point, and they are > logically "inside" the previously established ones. At RELEASE we can't > really release -- we just lose the name and thus the opportunity to > rollback to it. Oh, good point. Yes, those savepoints in between are still active. But do we release anything on subxact commit? I though it was only on xact abort, and that does invalidate all the savepoints in between. -- 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
Simon Riggs wrote:
> On Fri, 2004-07-09 at 20:34, Bruce Momjian wrote:
> > Alvaro Herrera wrote:
> > > On Fri, Jul 09, 2004 at 09:07:58PM +0200, Dennis Bjorklund wrote:
> > > > On Fri, 9 Jul 2004, Alvaro Herrera wrote:
> > > >
> > > > > Clearly savepoints do not allow for a snapshot to be released; nested
> > > > > xacts do.
> > > >
> > > > Why not?
> > >
> > > What is it?
> >
> > Simon posted it. It is called RELEASE:
> >
> > > BEGIN;
> > > SAVEPOINT x1;
> > > INSERT INTO ...;
> > > RELEASE SAVEPOINT x1;
> > > SAVEPOINT x1;
> > > INSERT INTO ...;
> > > RELEASE SAVEPOINT x1;
> > > SAVEPOINT x1;
> > > INSERT INTO ...;
> > > RELEASE SAVEPOINT x1;
>
> Yes, this is the DB2 and SQLAnywhere syntax.
>
> Oracle uses ROLLBACK TO SAVEPOINT...identical pretty much.
I thouht ROLLBACK was different from RELEASE, no? I see ROLLBACK used
in SQL99 for savepoints:
ROLLBACK [ WORK ] [ AND [ NO ] CHAIN ] [ <savepoint clause> ]
RELEASE only discards the savepoint name, I thought.
> Oracle's support of autonomous transactions looks to be identical to
> nested transactions (Alvaro's advice required there...). They don't
> allow you to explicitly call them, but you can use BEGIN/COMMIT in a
> host program that calls a stored procedure, which also contains
> BEGIN/COMMIT, effectively giving nested txns.
Oracle has nested transactions too? Can you supply an example?
-- 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 Fri, 2004-07-09 at 21:18, Bruce Momjian wrote: > Simon Riggs wrote: > > > > Oracle uses ROLLBACK TO SAVEPOINT...identical pretty much. > > I thouht ROLLBACK was different from RELEASE, no? I see ROLLBACK used > in SQL99 for savepoints: > > ROLLBACK [ WORK ] [ AND [ NO ] CHAIN ] > [ <savepoint clause> ] > > > RELEASE only discards the savepoint name, I thought. > ERR-OHH Yes, dead right. Forgive my confusion, I knew there was something different about Oracle's support of RELEASE. Check out: https://cwisdb.cc.kuleuven.ac.be/ora10doc/server.101/b10759/ap_standard_sql001.htm last thing on page... > > Oracle's support of autonomous transactions looks to be identical to > > nested transactions (Alvaro's advice required there...). They don't > > allow you to explicitly call them, but you can use BEGIN/COMMIT in a > > host program that calls a stored procedure, which also contains > > BEGIN/COMMIT, effectively giving nested txns. > > Oracle has nested transactions too? Can you supply an example? It's hard to quote a short example.... so try this link instead http://www.hk8.org/old_web/oracle/guide8i/ch02_05.htm Wasn't there some description of autonomous transactions on the stuff I sent previously? Best Regards, Simon Riggs
Simon Riggs wrote: > On Fri, 2004-07-09 at 21:18, Bruce Momjian wrote: > > Simon Riggs wrote: > > > > > > Oracle uses ROLLBACK TO SAVEPOINT...identical pretty much. > > > > I thouht ROLLBACK was different from RELEASE, no? I see ROLLBACK used > > in SQL99 for savepoints: > > > > ROLLBACK [ WORK ] [ AND [ NO ] CHAIN ] > > [ <savepoint clause> ] > > > > > > RELEASE only discards the savepoint name, I thought. > > > > ERR-OHH Yes, dead right. Forgive my confusion, I knew there was > something different about Oracle's support of RELEASE. Check out: > > https://cwisdb.cc.kuleuven.ac.be/ora10doc/server.101/b10759/ap_standard_sql001.htm > > last thing on page... Interesting Oracle doesn't support RELEASE or savepoint levels: T271, Savepoints Oracle supports this feature, except: * Oracle does not support RELEASE SAVEPOINT. * Oracle does not support savepoint levels. > > > Oracle's support of autonomous transactions looks to be identical to > > > nested transactions (Alvaro's advice required there...). They don't > > > allow you to explicitly call them, but you can use BEGIN/COMMIT in a > > > host program that calls a stored procedure, which also contains > > > BEGIN/COMMIT, effectively giving nested txns. > > > > Oracle has nested transactions too? Can you supply an example? > > It's hard to quote a short example.... so try this link instead > > http://www.hk8.org/old_web/oracle/guide8i/ch02_05.htm > > Wasn't there some description of autonomous transactions on the stuff I > sent previously? This is not a nested transaction. It is "autonomous" meaning it can commit independent of the outer transaction: The grand new benefit of autonomous transactions for database triggersis that inside those triggers you can now issue COMMITsand ROLLBACKs,statements that are otherwise not allowed in database triggers. Thechanges you commit and roll backwill not, however, affect the maintransaction that caused the database trigger to fire. They will onlyapply to DML activitytaking place inside the trigger itself (or throughstored program units called within the trigger). -- 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 Fri, 9 Jul 2004, Alvaro Herrera wrote: > > Simon posted it. It is called RELEASE: > > We can't actually release anything (commit the subtransactions), because > they may be savepoints established after that point, and they are > logically "inside" the previously established ones. At RELEASE we can't > really release -- we just lose the name and thus the opportunity to > rollback to it. You can still perform the release. If we have SAVEPOINT p1; SAVEPOINT p2; RELEASE p2; then it's no problem, we released the topmost savepoint (commit the corresponding subtransaction). And if we have SAVEPOINT p1; SAVEPOINT p2; RELEASE p1; now you are saying that we just forget the name p1 and the subtransaction for p1 is still there for ever. But one should also link the subtransaction for p1 to p2, so when p2 is released then also the (now unnamed) subtransaction for p1 is commited. Of course we can't release p1 as long as p2 is still active. p2 is logically a subtransaction of p1. One don't really need an explicit link. When p2 is released all one needs to do is to look at the parent and see if that is still named, if not it should be commited and so on until we reach a named one. -- /Dennis Björklund
On Fri, Jul 09, 2004 at 04:07:19PM -0400, Bruce Momjian wrote: > Alvaro Herrera wrote: > > We can't actually release anything (commit the subtransactions), because > > they may be savepoints established after that point, and they are > > logically "inside" the previously established ones. At RELEASE we can't > > really release -- we just lose the name and thus the opportunity to > > rollback to it. > > Oh, good point. Yes, those savepoints in between are still active. But > do we release anything on subxact commit? I though it was only on xact > abort, and that does invalidate all the savepoints in between. Yes, we free some things. Granted it's not a lot, but we have stacks for several things that will be always be growing with savepoints, but we can chop their heads off with "commit nested." -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) Jason Tesser: You might not have understood me or I am not understanding you. Paul Thomas: It feels like we're 2 people divided by a common language...
Alvaro Herrera wrote: >On Fri, Jul 09, 2004 at 10:38:15AM -0500, Thomas Swan wrote: > > > >>visibility issue and how far do you unwind the depth of subtransactions >>or transactions? >> >>BEGIN >> UPDATE A >> SAVEPOINT X >> BEGIN >> BEGIN >> UPDATE B >> BEGIN >> UPDATE C >> ROLLBACK TO SAVEPOINT X >> >> > >What happens here is that the user will go nuts. We will have a >prominent entry in the docs: "using both nested transactions and >savepoints inside a transaction can cause confusion. We recommend you >stick to one or the other." Or something like that. > >(What would really happen: when ROLLBACK TO SAVEPOINT X is executed, >nested transactions created after the SAVEPOINT will be closed.) > >So this is another reason why we should use COMMIT to close a nested >transaction: it may refer to a transaction that is already closed >because the user got confused. > > > Technically, a ROLLBACK TO SAVE POINT X would be an ABORT on all nested transactions. COMMIT means that if the parent transaction commits then the child transaction will also commit. BEGIN BEGIN UPDATE A ROLLBACK UPDATE B COMMIT The changes from UPDATE A will not commit with the changes from UPDATE B. BEGIN BEGIN UPDATE A COMMIT UPDATE B COMMIT The changes from UPDATE A will commit with the changes from UPDATE B. BEGIN BEGIN UPDATE A COMMIT UPDATE B ROLLBACK The changes from UPDATE A will not commit with the changes from UPDATE B, and the changes from UPDATE B will not commit either.
Dear all, I've being following the discussion of the nested transaction. I apologize for that I can't help asking my questions as I get more confused about what exactly are nested transactions, at least as far as the concurrency control goes. It seems to me there are two different types of nested transactions, both to improve the parallelism to a transaction, but they have different semantics. The first type of nested transactions, I believe as described in this paper: http://portal.acm.org/citation.cfm?id=806709&dl=ACM&coll=portal has the semantics that the inner (or children) transactions are totally hidden within a outer (or parent) transaction. Concurrency control makes sure not only the entire (including children) parent transaction is serial with other (parent) transaction, but also all child transactions are serial inside the parent transaction. Clearly, this speedup the execution of the parent transaction when child transactions are executed in parallel. I think this semantics is also documented here: http://pybsddb.sourceforge.net/ref/transapp/nested.html On the other hand, I believe another semantics of nested transactions is that to allow child transactions to commit independently to the parent transaction. The logger example in this link given by a previous post in this discussion http://www.hk8.org/old_web/oracle/guide8i/ch02_05.htm is a good example on this semantics. As far as the concurrency control goes, the parent transaction and the children transactions are treated equally. I.e. if after a child transaction is finished and before its parent transaction commits, a conflict with the child transaction will not cause the parent transaction to rollback. Again, this allows more parallelism to the the parent transaction. Am I on the right track understanding the serializability semantics here? I'd appreciate it if someone can direct me some authorative text on these issues. Thanks, -Min
On Fri, 9 Jul 2004, Alvaro Herrera wrote: > Yes, we free some things. Granted it's not a lot, but we have stacks > for several things that will be always be growing with savepoints, They will not always be growing for savepoints, you can free things when using savepoints just as with subtransactions. -- /Dennis Björklund
Alvaro Herrera wrote: > We can't actually release anything (commit the subtransactions), because > they may be savepoints established after that point, and they are > logically "inside" the previously established ones. At RELEASE we can't > really release -- we just lose the name and thus the opportunity to > rollback to it. The 2003 draft claims that RELEASE SAVEPOINT invalidates savepoints subsequent to the RELEASE: 1) Let S be the <savepoint name>. 2) If S does not identify a savepoint established in the current savepoint level,then an exception condition is raised:savepoint exception invalid specification. 3) The savepoint identified by S and all savepoints established in the current savepoint level subsequent to the establishment of S are destroyed. So it sounds like we can commit the subtransaction on RELEASE. Note that this is *not* the same when a savepoint name is reused; that just moves the name, but "contained" savepoints are still valid. -O
Oliver Jowett wrote: > The 2003 draft claims that RELEASE SAVEPOINT invalidates savepoints > subsequent to the RELEASE: "subsequent to the released savepoint" rather. -O
Dennis Bjorklund wrote: > On Fri, 9 Jul 2004, Bruce Momjian wrote: > >> I think we agreed on BEGIN NESTED/COMMIT NESTED, and START NESTED >> TRANSACTION and COMMIT NESTED TRANSACTION. > > Should I read this as pg will get its own implementation of sub > transactions and not implement the almost equivalent standard (sql99) > savepoint feature? > > Will we in the future see savepoints as well? I'm not a core developer, but that is what it looks like. > And when that happen, should > we then recommend that people use the standard feature and stay away from > the pg only feature? Nested transactions and savepoints serve two different purposes. They have some overlap, but for the most part solve two distinct problems. > > Doesn't anyone but me think is all backwards? > I don't think so, especially as there has been some talk of implimenting savepoints as a subset of nested transactions. --miker
Min Xu (Hsu) wrote: > Dear all, > > I've being following the discussion of the nested transaction. I > apologize for that I can't help asking my questions as I get more > confused about what exactly are nested transactions, at least as far as > the concurrency control goes. > > It seems to me there are two different types of nested transactions, > both to improve the parallelism to a transaction, but they have > different semantics. > > The first type of nested transactions, I believe as described in this paper: > > http://portal.acm.org/citation.cfm?id=806709&dl=ACM&coll=portal > > has the semantics that the inner (or children) transactions are totally > hidden within a outer (or parent) transaction. Concurrency control makes > sure not only the entire (including children) parent transaction is > serial with other (parent) transaction, but also all child transactions > are serial inside the parent transaction. Clearly, this speedup the > execution of the parent transaction when child transactions are executed > in parallel. I think this semantics is also documented here: > > http://pybsddb.sourceforge.net/ref/transapp/nested.html > > On the other hand, I believe another semantics of nested transactions is > that to allow child transactions to commit independently to the parent > transaction. The logger example in this link given by a previous post > in this discussion > > http://www.hk8.org/old_web/oracle/guide8i/ch02_05.htm > > is a good example on this semantics. As far as the concurrency control > goes, the parent transaction and the children transactions are treated > equally. I.e. if after a child transaction is finished and before its > parent transaction commits, a conflict with the child transaction will > not cause the parent transaction to rollback. Again, this allows more > parallelism to the the parent transaction. > > Am I on the right track understanding the serializability semantics > here? I'd appreciate it if someone can direct me some authorative text > on these issues. You are actually talking about much more powerful nested transactions than we have implemented currently. The first allows for parallel execution, which is certainly interesting. The second allows subtransactions to be committed/rolled back independent of the outer transaction. We don't support that either. Our current implementation merely allows parts of a transaction to be rolled back using ROLLBACK NESTED. -- 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 Sat, Jul 10, 2004 at 09:46:56AM +1200, Oliver Jowett wrote: > Alvaro Herrera wrote: > > >We can't actually release anything (commit the subtransactions), because > >they may be savepoints established after that point, and they are > >logically "inside" the previously established ones. At RELEASE we can't > >really release -- we just lose the name and thus the opportunity to > >rollback to it. > > The 2003 draft claims that RELEASE SAVEPOINT invalidates savepoints > subsequent to the RELEASE: In our case, invalidating a savepoint does not mean we can release its resources. We can only do that if it's the latest defined savepoint. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "Cuando miro a alguien, más me atrae cómo cambia que quién es" (J. Binoche)
On Fri, Jul 09, 2004 at 11:28:23PM +0200, Dennis Bjorklund wrote: > On Fri, 9 Jul 2004, Alvaro Herrera wrote: > > > Yes, we free some things. Granted it's not a lot, but we have stacks > > for several things that will be always be growing with savepoints, > > They will not always be growing for savepoints, you can free things when > using savepoints just as with subtransactions. I still don't see when I can release a savepoint's state. You showed a particular case, where we can finish a released savepoint that is the innermost transaction. However, as soon as there is another savepoint set after the released savepoint was set, we can't free the second. I mean this: begin; ... work ...; savepoint foo; ... more work ...; savepoint bar; ... yet more ... ; release foo; At this time I can't release savepoint foo because the implementation (nested) requires me to keep it open as long as savepoint bar exists. If I released bar at a later time, I could close both, but not before. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "¿Qué importan los años? Lo que realmente importa es comprobar que a fin de cuentas la mejor edad de la vida es estar vivo" (Mafalda)
On Fri, 9 Jul 2004, Alvaro Herrera wrote: > I mean this: > > begin; > ... work ...; > savepoint foo; > ... more work ...; > savepoint bar; > ... yet more ... ; > release foo; > > > At this time I can't release savepoint foo because the implementation > (nested) requires me to keep it open as long as savepoint bar exists. > If I released bar at a later time, I could close both, but not before. Yes, and that is exactly what should be done, what is wrong with that behaviour? If you do the same as above with nested transactions BEGIN; ... work ...; SUBBEGIN; ... more work ...; SUBBEGIN; ... yet more ... ; and now you can only commit the last subbegin. Subtransactions does not give you anything more then savepoints in this example. If anything there might be a possibility to do more with savepoints then nested transactions since as you say, you can release an earlier savepoint then the last. But that is something one can try to optimize later, if possible to optimize at all. Subtransactions can _not_ free more things then savepoints can. It's just an empty argument. In fact, I still see no real advantage to subtransactions at all. We are only playing the lock-in game when we introduce postgresql features that do almost the same thing as standard features. -- /Dennis Björklund
On Fri, 9 Jul 2004, Mike Rylander wrote: > Nested transactions and savepoints serve two different purposes. They have > some overlap, but for the most part solve two distinct problems. Then show some examples that illustrait the difference. So far all examples shown that uses subtransactions could just as well have been written using savepoints. I don't agree that they have two different purposes. > I don't think so, especially as there has been some talk of implimenting > savepoints as a subset of nested transactions. It is not a subset. It's the other way around. Nested transactions are a subset of savepoints Savepoints have more possibilities, you can invalidate older savepoints then the last (with subtransactions you can only commit/rollback the last). If you don't use that then it's exactly the same as subtransactions. The only "feature" subtransactions have that savepoints doesn't is the lack of names. Every savepoint have a name. If we want an extension it could be to get the database to generate a fresh savepoint name. The client can of course also generate unique savepoint names if it want. That subtransactions do more than savepoints is just smoke an mirrors. So far there have been no example to validate that point of view, and I don't think there will be any. If anyone know of something that you can do with subtransactions and not with savepoints, please speak up. -- /Dennis Björklund
On Fri, 9 Jul 2004, Alvaro Herrera wrote: > On Fri, Jul 09, 2004 at 11:28:23PM +0200, Dennis Bjorklund wrote: > > On Fri, 9 Jul 2004, Alvaro Herrera wrote: > > > > > Yes, we free some things. Granted it's not a lot, but we have stacks > > > for several things that will be always be growing with savepoints, > > > > They will not always be growing for savepoints, you can free things when > > using savepoints just as with subtransactions. > > I still don't see when I can release a savepoint's state. > > You showed a particular case, where we can finish a released savepoint > that is the innermost transaction. However, as soon as there is another > savepoint set after the released savepoint was set, we can't free the > second. > > I mean this: > > begin; > ... work ...; > savepoint foo; > ... more work ...; > savepoint bar; > ... yet more ... ; > release foo; > > > At this time I can't release savepoint foo because the implementation > (nested) requires me to keep it open as long as savepoint bar exists. > If I released bar at a later time, I could close both, but not before. According to ANSI 2003, savepoints should be considered in terms of nesting. That is, the spec talks to nesting levels (4.35.2): "An SQL-transaction has one or more savepoint levels, exactly one of which is the current savepoint level. The savepoint levels of an SQL-transaction are nested, such that when a new savepoint level NSL is established, the current savepoint level CSL ceases to be current and NSL becomes current. When NSL is destroyed, CSL becomes current again." And: "If a <rollback statement> references a savepoint SS, then all changes made to SQL-data or schema subsequent to the establishment of the savepoint are canceled, all savepoints established since SS was established are destroyed, and the SQL-transaction is restored to its state as it was immediately following the execution of the <savepoint statement>." This is also relevant: "It is implementation-defined whether or not, or how, a <rollback statement> that references a <savepoint specifier> affects diagnostics area contents, the contents of SQL descriptor areas, and the status of prepared statements." So, releasing foo would release bar (16.5): "3) The savepoint identified by S and all savepoints established in the current savepoint level subsequent to the establishment of S are destroyed." Also, the spec makes mention of savepoint behaviour in functions (10.4): "2) If, before the completion of the execution of the SQL routine body of R, an attempt is made to execute an SQL-transaction statement that is not a <savepoint statement> or a <release savepoint statement>, or is a <rollback statement> that does not specify a <savepoint clause>, then an exception condition is raised: SQL routine exception prohibited SQL-statement attempted. " It also states that an SQL-invoked function lives in its own savepoint level (4.27): "An SQL-invoked procedure may optionally be specified to require a new savepoint level to be established when it is invoked and destroyed on return from the executed routine body. The alternative of not taking a savepoint can also be directly specified with OLD SAVEPOINT LEVEL. When an SQL-invoked function is invoked a new savepoint level is always established." We do not currently support SQL-invoked procedures (that is, routines executed from SQL with CALL <procname>, which don't need to return a value and which can accept IN OUT and OUT parameters) so we need only deal with the SQL-invoked function case. So, running back to 10.4: "12) If R is an SQL-invoked function or if R is an SQL-invoked procedure and the descriptor of R includes an indication that a new savepoint level is to be established when R is invoked, then the current savepoint level is destroyed." So, any savepoints created during the function are destroyed. What isn't clearly discussed is what they mean by destroy. That is, the 1) ability to reference the savepoint, or 2) all modifications to SQL-data made since the savepoint was created. I cannot see how it could be (2) can be the case. Section 16.5 discusses <release savepoint statement> whose function is to 'destroy a savepoint': "3) The savepoint identified by S and all savepoints established in the current savepoint level subsequent to the establishment of S are destroyed." It makes no reference to have any effect like rollback. So, I think that we can only release things once we rollback to a savepoint or once we commit. This is not to say we should follow this implementation. I've dug this up to try and present one (reasonably) consistent perspective on it. Thanks, Gavin
On Sat, 10 Jul 2004, Gavin Sherry wrote: > "3) The savepoint identified by S and all savepoints established in the > current savepoint level subsequent to the establishment of S are > destroyed." So the standard savepoints are even more like the subtransactions that alvaro have implemented then I realised before. One can not just release an earlier savepoint and keep a later one. Interesting. -- /Dennis Björklund
Simon Riggs wrote: >>>BEGIN >>> display one screen to user - book the flight >>> INSERT INTO ... >>> INSERT INTO ... >>> UPDATE ... >>> SAVEPOINT >>> display another related screen - book the hotel >>> INSERT INTO >>> DELETE >>> UPDATE >>> UPDATE >>> SAVEPOINT >>> offer confirmation screen >>>COMMIT (or ROLLBACK) >>> >>> >>> >>> >>No, SAVEPOINT is not some kind of intermediate commit, but a point where >>a rollback can rollback to. >> >> > >Hmmm....I'm not sure what you mean by "No". The SAVEPOINT is somewhere >you can ROLLBACK to, yes - exactly what I'm saying. > >I've not introduced any concept of "intermediate commit"... > >Do you agree that my example is valid Oracle SQL? > > If you name that SAVEPOINT statements, yes. But the ordering of statements makes the second savepoint useless, because it's after all datamodifying statements; the first three are not covered by a savepoint at all, only the toplevel xaction. That's probably not what you wanted. Regards, Andreas
Bruce Momjian wrote: > >Interesting Oracle doesn't support RELEASE or savepoint levels: > >T271, Savepoints Oracle supports this feature, except: > > * Oracle does not support RELEASE SAVEPOINT. > > Yes, and IMHO it's just some housekeeping stuff, informing the backend that you'll never want to rollback to that savepoint any more. If there are no resources to release internally, a noop. > * Oracle does not support savepoint levels. > > The levels are created implicitely, because a rollback to savepoint1 will rollback *all* work done since then. This effectively makes all subsequent savepoints children of the first. The effect of RELEASE SAVEPOINT would be to restrict the tree depth, by concentrating all released savepoints into their parents. > >This is not a nested transaction. It is "autonomous" meaning it can >commit independent of the outer transaction: > I like that too... in 7.6. Regards, Andreas
Alvaro Herrera wrote: > On Sat, Jul 10, 2004 at 09:46:56AM +1200, Oliver Jowett wrote: > >>Alvaro Herrera wrote: >> >> >>>We can't actually release anything (commit the subtransactions), because >>>they may be savepoints established after that point, and they are >>>logically "inside" the previously established ones. At RELEASE we can't >>>really release -- we just lose the name and thus the opportunity to >>>rollback to it. >> >>The 2003 draft claims that RELEASE SAVEPOINT invalidates savepoints >>subsequent to the RELEASE: (that should read "subsequent to the released savepoint") > In our case, invalidating a savepoint does not mean we can release its > resources. We can only do that if it's the latest defined savepoint. I don't understand why this is true if the invalidation comes from a RELEASE statement. I understand the problems with savepoint name reuse invalidating an earlier savepoint -- we do have to keep the earlier txn open in that case. Say I have: SAVEPOINT s1 -- work 1 SAVEPOINT s2 -- work 2 RELEASE SAVEPOINT s1 -- Invalidates s1 and s2 Can't we translate that to: begin subtransaction s1 -- work 1 begin subtransaction s2 -- work 2 commit subtransaction s1 -- and implicitlys2 We don't need to keep subtransaction s2 open -- we will never need to roll it back as the RELEASE of s1 invalidates it. What am I missing? -O
On Sat, 10 Jul 2004, Mike Rylander wrote: > They do, if only to make particular constructs easier to write. This is an > opinion, but for example an EXCEPTION framework for plpgsql would be easier > to implement and use if it used the nested transactions rather than > savepoint syntax: > > CREATE FUNCTION blah () RETURNS INT LANGUAGE PLPGSQL AS ' > BEGIN > BEGIN NESTED; > do some work... > BEGIN NESTED; > do other work... > EXCEPTION WHEN SQLSTATE = already_exists THEN > do alternate work with its own error checking... > END NESTED; > EXCEPTION WHEN SQLSTATE = fkey_violation THEN > ROLLBACK NESTED; > END NESTED; > END;'; > > I realize this can be done with nested savepoints and that is what the spec > requires, Lets look at what it can look like: BEGIN SAVEPOINT nested; do some work... SAVEPOINT nested2; do other work... EXCEPTION WHEN SQLSTATE = already_exists THEN ROLLBACK TO SAVEPOINTnested2; do alternate work with its own error checking... RELEASE nested2; EXCEPTION WHEN SQLSTATE = fkey_violation THEN ROLLBACK TO SAVEPOINT nested; RELEASEnested; END; Now, in what way is this more complicated? I'm not 100% sure how the exceptions that you used above work. Do that always rollback the transaction thay are in? In one of the exceptions you did a rollback but not in the other. In my example I added a rollback in the first exception handler. Maybe you forgot it there? In any case. I don't see this as any harder then your example. > > Savepoints have more possibilities, you can invalidate older savepoints > > then the last (with subtransactions you can only commit/rollback the > > last). > > This implies that savepoints are flat. It won't be that way under the > covers, but it does give that impression, and flat savepoint space is > definitely suited to a different class of problems than nested > transactions. First, my claim above was wrong. As Gavin pointed out in another mail, if one have savepoints p1 and p2 and release p1 then also p2 is released. It's possible to implement both kinds of behaviour using Alvaros work, but the standard demands the simpler one where p2 is also released. Now, about the flatness. Savepoints are not flat. They are sort of flat in a savepoint level. But, for example when you call a function you get a new savepoint level. I actually don't want to call it flat at all. The example above does not overwrite the savepoints "nested" and "nested2" that might exist before the call, since this is a higher savepoint level. I'm not sure exactly what it is that defines a new savepoint level, but a function call does and maybe some other things. > BTW, I would imagine that savepoints will be implemented as nested > transactions with detachable labels... the label can move from a > transaction to one of its descendants, and that outer (sub)transaction will > be implicitly COMMITed with its parent. Yes. That's my view as well. > Alvaro found it easier to implement nested transactions, he forged ahead and > did it. Now, because of good design or simple luck, we should be able to > implement savepoints fairly easily. I think the difference between them are so small that it's not a big deal at all. In my view savepoints and nested transactions are almost the same thing. The only difference being that the savepoints have names. Savepoints are nested. You can not have savepoints p1 and then p2 and try to only rollback p1. Then you rollback p2 as well, why. Because they are nested. > spec WRT savepoints, we actually get to present a richer interface to the > user If it's richer or not is the question. And then one have to compare that to the downside of adding a non standard interface. I don't think it is richer at all, but I'd be happy to change my mind if someone can show an example where nested transactions solve something that you can't just as well solve with savepoints. -- /Dennis Björklund
Dennis Bjorklund <db@zigo.dhs.org> writes:
> On Fri, 9 Jul 2004, Mike Rylander wrote:
>> Nested transactions and savepoints serve two different purposes. They have
>> some overlap, but for the most part solve two distinct problems.
> Then show some examples that illustrait the difference. So far all
> examples shown that uses subtransactions could just as well have been
> written using savepoints.
And vice versa. It's a matter of convenience of notation, and I tend
to agree with Mike's comment that each wins in some cases.
> Savepoints have more possibilities, you can invalidate older savepoints
> then the last
Nonsense. Invalidating an older savepoint must invalidate everything
after it as well. The fact that the savepoint syntax allows you to
express conceptually-ridiculous operations (like that one) is not a
point in its favor IMHO.
regards, tom lane
On Sat, 10 Jul 2004, Tom Lane wrote: > Nonsense. Invalidating an older savepoint must invalidate everything > after it as well. The fact that the savepoint syntax allows you to > express conceptually-ridiculous operations (like that one) is not a > point in its favor IMHO. Luckily the standard was written like that! On the other hand, it's not hard to implement the other behaviour either if that is what one wants (and we don't). It would only forget the name of the earlier savepoint. The corresponding transaction in itself have to stay. -- /Dennis Björklund
On Sat, Jul 10, 2004 at 06:22:00PM +0200, Dennis Bjorklund wrote: > On the other hand, it's not hard to implement the other behaviour either > if that is what one wants (and we don't). It would only forget the name of > the earlier savepoint. The corresponding transaction in itself have to > stay. That's why it's absurd. Why allow an operation which isn't really an operation? -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "God is real, unless declared as int"
On Fri, Jul 09, 2004 at 08:03:36PM +0100, Simon Riggs wrote: > On Fri, 2004-07-09 at 16:47, Alvaro Herrera wrote: > > So this is another reason why we should use COMMIT to close a nested > > transaction: it may refer to a transaction that is already closed > > because the user got confused. Sorry! I wanted to say that we SHOULDN'T use "commit" to close a nested transaction. Rather we want to use a different command just so the confusion does not close the outer transaction, which would not be what the user wanted to do. > Could we put two modes of operation in? > i.e. if you use SAVEPOINTs/ROLLBACK TO SAVEPOINT, then you're not > allowed to use nested transactions (and vice versa - so they are > mutually exclusive)... This may be a good idea. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "Always assume the user will do much worse than the stupidest thing you can imagine." (Julien PUYDT)
People, Are we perhaps getting away from the issues here? The reason for this discussion was to determine the user-level syntax for Alvaro's nested transactions. We can discuss all we want about how he should have maybe implemented some things differently, but we're supposed to start beta-testing in 5 days and the current tangentalism of this discussion is unlikely to produce such a result. What we really have to determine is one of 4 options regarding the syntax for Alvaro's patch: 1) We adopt one of the two PostgreSQL-specific syntaxes suggested by Alvaro, based on SUBBEGIN or BEGIN NESTED. This would probably be the easiest solution, but adds inconsistency with both the standard and other databases. 2) We adopt the syntax of the other databases to really push Nested Transactions (as opposed to Savepoints), namely MSSQL and SyBase. This would aid thousands of DBAs wishing to migrate to PostgreSQL, but would also mean adopting a logically inconsistent syntax which is even further from the standard than Alvaro's proposal. 3) We adopt a slightly mutated form of the SQL3 SAVEPOINT syntax. This would have the twin benefit of both allowing us to improve our standards compliance and make savepoints completely compliant in the next version, as well as helping those wishing to migrate from Oracle to PostgreSQL (currently our largest source of migrations). Its disadvantage is the subtle differences between Alvaro's patch and the standard, which might not be obvious to users and lead to difficult to locate errors. This option also comes in two flavors:a) we implement savepoint names, troubleshooting the namespace and scoping issues, which would really make this a different feature and delay beta testing, orb) we do anonymous savepoints for now, which more-or-less exactly matches the current behavior of Alvaro's patch, and do complaint, named savepoints in the next version. 4) We hold back this patch until the next version. There is some merit in this, due to the lack of consensus on functionality and Alvaro's dissapointing discovery that we will not be able to use savepoints in functions until next version. However, it would also mean effectively dropping a major feature from 7.5 pretty much because we can't make up our minds, and because nobody gave Alvaro adequate feedback when it was more timely. If you couldn't tell, I favor option 3) b) This syntax would look like: BEGIN TRANSACTION; --begin maindo stuff;SAVEPOINT; -- begin "nested transaction 1" do more stuff; SAVEPOINT; -- begin"nested transaction 2" inside "NT 1" do stuff; RELEASE SAVEPOINT; -- "commit" NT 2 do some more stuff; test conditions: if bad: ROLLBACK TO SAVEPOINT; -- rollback NT1, erasing NT2 in the process if good: RELEASE SAVEPOINT; -- "commit" NT1 and by implication NT2do some more stufftests: if problem: ROLLBACK; --rollback entire transaction, including NT1 and NT2;if good: COMMIT; -- commit entire transaction, including NT1 and/orNT2 if they were good, excluding them if they were rolled back In other words:SAVEPOINT == BEGIN NESTEDRELEASE SAVEPOINT == COMMIT NESTEDROLLBACK TO SAVEPOINT == ROLLBACK NESTED If I'm not mistaken, the above matches the functionality already coded by Alvaro. It begins but does not complete our compliance with SQL3 Savepoint syntax, putting us on the right road but making developers aware that there are some differences between our implementation and the standard. Thus developers would be able to adopt the current syntax now, and the same applications would still run when we complete standards-compliant syntax later. HOWEVER, I do still find one major flaw in Alvaro's implementation that I can't seem to get other people on this list to take seriously, or maybe I'm just not understanding the answers. One-half the point of Savepoints/Nested Transactions is the ability to recover from certain kinds of errors (like duplicate keys) inside a transaction and still commit the transaction after the abort condition has been rolled back. But the ability to detect an abort state *from the SQL command line* (or a database port connection) has not been addressed. I've seen some comments about functions to find an abort state from libpq in the text, but I'm not even clear if this has been coded or is just theoretical. Parsing the output of STDERR is *not* adequate. We need to be able to query whether we are in an abort state, or we make NTs absolutely useless to any client application that has connections which cannot, or do not yet, incorporate new libpq functions, something which could take considerable time after the 7.5 release.Do we already have an ability to query the SQLSTATE from the command line? If so, what numbers indicate an abort state, if any? Without this issue being addressed, I will change my opinion and votefor option (4) because clearly the NT patch will not be ready for prime-time. -- Josh Berkus Aglio Database Solutions San Francisco
On Sat, 10 Jul 2004, Alvaro Herrera wrote: > That's why it's absurd. Why allow an operation which isn't really an > operation? Same reason why you allow an addition with 0. One can say that it's not really an operation either. One can have many different semantics, here are 3 versions: 1) You release savepoints in any order2) You release savepoints in reverse order3) You release any savepoints and later onesthen the one you released are automatically released. I don't see any of these as absurd. The ansi spec uses number 3. It might seem absurd to you, given the implementation you have made. -- /Dennis Björklund
On Sat, Jul 10, 2004 at 09:46:00PM +0200, Dennis Bjorklund wrote: > One can have many different semantics, here are 3 versions: > > 1) You release savepoints in any order > 2) You release savepoints in reverse order > 3) You release any savepoints and later ones then the > one you released are automatically released. > > I don't see any of these as absurd. The ansi spec uses number 3. Ah-ha, now I see what I failed to see previously: all later savepoints are also released! This makes a lot more sense. So "release" is exactly like commit nested, allowing several levels to be "committed". -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "La fuerza no está en los medios físicos sino que reside en una voluntad indomable" (Gandhi)
Josh Berkus wrote: > If you couldn't tell, I favor option 3) b) This syntax would look like: > > BEGIN TRANSACTION; --begin main > do stuff; > SAVEPOINT; -- begin "nested transaction 1" > do more stuff; > SAVEPOINT; -- begin "nested transaction 2" inside "NT 1" > do stuff; > RELEASE SAVEPOINT; -- "commit" NT 2 > do some more stuff; > test conditions: if bad: > ROLLBACK TO SAVEPOINT; -- rollback NT1, erasing NT2 in the process > if good: > RELEASE SAVEPOINT; -- "commit" NT1 and by implication NT2 > do some more stuff > tests: if problem: > ROLLBACK; -- rollback entire transaction, including NT1 and NT2; > if good: > COMMIT; -- commit entire transaction, including NT1 and/or NT2 > if they were good, excluding them if they were rolled back Well, Oracle doesn't suppor RELEASE, so we are matching the standard but perhaps not allowing easy migration from Oracle. > In other words: > SAVEPOINT == BEGIN NESTED > RELEASE SAVEPOINT == COMMIT NESTED > ROLLBACK TO SAVEPOINT == ROLLBACK NESTED > > If I'm not mistaken, the above matches the functionality already coded by > Alvaro. It begins but does not complete our compliance with SQL3 Savepoint > syntax, putting us on the right road but making developers aware that there > are some differences between our implementation and the standard. Thus > developers would be able to adopt the current syntax now, and the same > applications would still run when we complete standards-compliant syntax > later. > > HOWEVER, I do still find one major flaw in Alvaro's implementation that I > can't seem to get other people on this list to take seriously, or maybe I'm > just not understanding the answers. One-half the point of Savepoints/Nested > Transactions is the ability to recover from certain kinds of errors (like > duplicate keys) inside a transaction and still commit the transaction after > the abort condition has been rolled back. > But the ability to detect an abort state *from the SQL command line* (or a > database port connection) has not been addressed. I've seen some comments > about functions to find an abort state from libpq in the text, but I'm not > even clear if this has been coded or is just theoretical. Parsing the > output of STDERR is *not* adequate. We need to be able to query whether we > are in an abort state, or we make NTs absolutely useless to any client > application that has connections which cannot, or do not yet, incorporate new > libpq functions, something which could take considerable time after the 7.5 > release. > Do we already have an ability to query the SQLSTATE from the command line? > If so, what numbers indicate an abort state, if any? > Without this issue being addressed, I will change my opinion and vote for > option (4) because clearly the NT patch will not be ready for prime-time. Don't we see the error from libpq PQexec() return value and other interfaces? Are you saying how do we detect a failure from a psql script? -- 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 Sat, 10 Jul 2004, Josh Berkus wrote: > In other words: > SAVEPOINT == BEGIN NESTED > RELEASE SAVEPOINT == COMMIT NESTED > ROLLBACK TO SAVEPOINT == ROLLBACK NESTED Here it should be: ROLLBACK TO SAVEPOINT == ROLLBACK NESTED; SAVEPOINT; And just to clearify, this is an extension to the spec that we then have to support for a long time. Adding this now and then replacing it with the standard syntax is not very sexy. If we add this we should support it "for ever". -- /Dennis Björklund
Dennis Bjorklund wrote: > On Sat, 10 Jul 2004, Josh Berkus wrote: > > > In other words: > > SAVEPOINT == BEGIN NESTED > > RELEASE SAVEPOINT == COMMIT NESTED > > ROLLBACK TO SAVEPOINT == ROLLBACK NESTED > > Here it should be: > > ROLLBACK TO SAVEPOINT == ROLLBACK NESTED; SAVEPOINT; > > And just to clearify, this is an extension to the spec that we then have > to support for a long time. Adding this now and then replacing it with the > standard syntax is not very sexy. If we add this we should support > it "for ever". Just to clarify, this is to allow rolling back to the same savepoint multiple times. If we named savepoints, the new savepoint would be the same name as the one we just rolled back. -- 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
Dennis, Bruce, > Just to clarify, this is to allow rolling back to the same savepoint > multiple times. If we named savepoints, the new savepoint would be the > same name as the one we just rolled back. Hmmm ... yeah, it would be nice to find a way around this so that we don't have non-standard behavior we have to work around once savepoint names are implemented. Suggestions? -- Josh Berkus Aglio Database Solutions San Francisco
Bruce Momjian wrote: > Dennis Bjorklund wrote: > > On Sat, 10 Jul 2004, Josh Berkus wrote: > > > > > In other words: > > > SAVEPOINT == BEGIN NESTED > > > RELEASE SAVEPOINT == COMMIT NESTED > > > ROLLBACK TO SAVEPOINT == ROLLBACK NESTED > > > > Here it should be: > > > > ROLLBACK TO SAVEPOINT == ROLLBACK NESTED; SAVEPOINT; > > > > And just to clearify, this is an extension to the spec that we then have > > to support for a long time. Adding this now and then replacing it with the > > standard syntax is not very sexy. If we add this we should support > > it "for ever". > > Just to clarify, this is to allow rolling back to the same savepoint > multiple times. If we named savepoints, the new savepoint would be the > same name as the one we just rolled back. Sorry, I confused folks. I should have corrected this line too: > > ROLLBACK TO SAVEPOINT == ROLLBACK NESTED; BEGIN NESTED; It is not a non-standard behavior. It is only an implementation detail used internally that allows nested transactions to implement savepoints. -- 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
I just posted a clarification. It isn't a problem. --------------------------------------------------------------------------- Josh Berkus wrote: > Dennis, Bruce, > > > Just to clarify, this is to allow rolling back to the same savepoint > > multiple times. If we named savepoints, the new savepoint would be the > > same name as the one we just rolled back. > > Hmmm ... yeah, it would be nice to find a way around this so that we don't > have non-standard behavior we have to work around once savepoint names are > implemented. Suggestions? > > -- > Josh Berkus > Aglio Database Solutions > San Francisco > -- 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
Bruce, > Well, Oracle doesn't suppor RELEASE, so we are matching the standard but > perhaps not allowing easy migration from Oracle. Well, that's Oracle's problem. Considering the amount of influence they had over the standard, there's no excuse for their syntax. Also, if someone converts and Oracle script which does not do RELEASE, it's still ok with us; they just end up nesting multiple levels and not "releasing" until the main transaction is committed. > Don't we see the error from libpq PQexec() return value and other > interfaces? As far as I know, DBD::pg does not at this time; it detects an error but does not return the SQLSTATE, and I'm *sure* that PHP 4 does not. I'm sure there are other interfaces in the same boat. And nobody has answered the question of what SQLSTATE ranges indicate an abort state as opposed to something else -- I get the feeling that this is not at all defined. > Are you saying how do we detect a failure from a psql > script? Right. There are applications out there: shell scripts, ODBC applications, etc., which are unlikely to *ever* have the ability to read states from libpq. These applications need to have the ability to detect an abort *by query* ala T-SQL (e.g. the @@ERROR system variable), so that they can issue the proper ROLLBACKs. -- Josh Berkus Aglio Database Solutions San Francisco
On Sat, 10 Jul 2004, Bruce Momjian wrote: > > > > SAVEPOINT == BEGIN NESTED > > > > RELEASE SAVEPOINT == COMMIT NESTED > > > > ROLLBACK TO SAVEPOINT == ROLLBACK NESTED > > > > > > Here it should be: > > > > > > ROLLBACK TO SAVEPOINT == ROLLBACK NESTED; SAVEPOINT; > > > > > > And just to clearify, this is an extension to the spec that we then have > > > to support for a long time. Adding this now and then replacing it with the > > > standard syntax is not very sexy. If we add this we should support > > > it "for ever". > > Sorry, I confused folks. I should have corrected this line too: > > > > ROLLBACK TO SAVEPOINT == ROLLBACK NESTED; BEGIN NESTED; Hmm, yes. Correct. Luckily, we already had: SAVEPOINT == BEGIN NESTED so it all worked out :-) > It is not a non-standard behavior. It is only an implementation detail > used internally that allows nested transactions to implement savepoints. The non-standard part I was talking about was the savepoints without names, and that is what we should support for ever if we introduce them. -- /Dennis Björklund
Josh Berkus wrote: > Bruce, > > > Well, Oracle doesn't suppor RELEASE, so we are matching the standard but > > perhaps not allowing easy migration from Oracle. > > Well, that's Oracle's problem. Considering the amount of influence they had > over the standard, there's no excuse for their syntax. Also, if someone > converts and Oracle script which does not do RELEASE, it's still ok with us; > they just end up nesting multiple levels and not "releasing" until the main > transaction is committed. OK. > > Don't we see the error from libpq PQexec() return value and other > > interfaces? > > As far as I know, DBD::pg does not at this time; it detects an error but does > not return the SQLSTATE, and I'm *sure* that PHP 4 does not. I'm sure there > are other interfaces in the same boat. And nobody has answered the question > of what SQLSTATE ranges indicate an abort state as opposed to something else > -- I get the feeling that this is not at all defined. They have no way of reporting a failed query back to the user? How do people program in those environments? Right now any failed query aborts the transaction so it seems it would be pretty easy. > > Are you saying how do we detect a failure from a psql > > script? > > Right. There are applications out there: shell scripts, ODBC applications, > etc., which are unlikely to *ever* have the ability to read states from > libpq. These applications need to have the ability to detect an abort *by > query* ala T-SQL (e.g. the @@ERROR system variable), so that they can issue > the proper ROLLBACKs. Well, that involves either creating a conditional capability in the backend, or in psql, neither of which will happen for 7.5. The best we can do is allow COMMIT NESTED INGORE ERROR (or COMMIT NESTED INGORE ROLLBACK) and just let the script keep going. I am thinking of cases where you want to drop an object you aren't sure exists in a transaction. Anything more complicated like issuing a replacement query will have to wait for 7.6. -- 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
Bruce, > They have no way of reporting a failed query back to the user? How do > people program in those environments? Right now any failed query aborts > the transaction so it seems it would be pretty easy. Believe it or not, PHP4 doesn't. This is one of the reasons why coders in other languages don't consider PHP a "real" programming language; the lack of exception handling. However, given this limitation we can't really use NTs in PHP4 anyway, so it's sort of a moot point. Sorry for bringing it up. To my current knowledge (and hopefully Andrew will speak up if I'm wrong) the DBD::Pg driver reports back a query exception, but not the SQLSTATE. This means that we can detect an abort (assuming all exceptions are aborts) but not what caused the abort, except by parsing the error message for text -- a hazardous approach at best. But you would be right to point out that this is a problem with the DBD::Pg driver. There are, however, other client applications where the problem is more ingrained. I've done an application for ColdFusion recently, and discovered that CF is completely unable to detect even the limited error-reporting capability of ODBC. This means that if CF can't query it, it doesn't exist. > Well, that involves either creating a conditional capability in the > backend, or in psql, neither of which will happen for 7.5. The best we > can do is allow COMMIT NESTED INGORE ERROR (or COMMIT NESTED INGORE > ROLLBACK) and just let the script keep going. I am thinking of cases > where you want to drop an object you aren't sure exists in a > transaction. Anything more complicated like issuing a replacement query > will have to wait for 7.6. OK, I didn't realize that it was a difficult thing. I think it should go on the TODO list but you are the judge of what's a quick fix and what's not. (BTW, aside from this limitation, I am *not* in favor of COMMIT NESTED IGNORE ERROR) -- -Josh BerkusAglio Database SolutionsSan Francisco
Dennis, > The non-standard part I was talking about was the savepoints without > names, and that is what we should support for ever if we introduce them. I don't have a problem with that idea. Anonymous Savepoints should be easy to support if we are supporting Named (spec) Savepoints. And the two should even integrate easily -- a *lot* more easily than Savepoints and Nested Xacts with a different syntax would. And, it's also a convenient shortcut for the most common case -- transactions with 1 level of nesting and only a couple of non-overlapping savepoints. Of course, if Alvaro can knock out Named Savepoints in a week, then sure, let's go for it. But I've not heard him saying he can. However, this does bring up an important issue; if we implement anonymous savepoints, then should the current implementation accept savepoint names and just ignore them? If not, it makes porting and coding for the spec much more difficult; if so, ported applications could develop subtle erroneous behaviour through wrong rollbacks. -- -Josh BerkusAglio Database SolutionsSan Francisco
Josh Berkus wrote: > Bruce, > > > They have no way of reporting a failed query back to the user? How do > > people program in those environments? Right now any failed query aborts > > the transaction so it seems it would be pretty easy. > > Believe it or not, PHP4 doesn't. This is one of the reasons why coders in > other languages don't consider PHP a "real" programming language; the lack of > exception handling. However, given this limitation we can't really use NTs > in PHP4 anyway, so it's sort of a moot point. Sorry for bringing it up. > > To my current knowledge (and hopefully Andrew will speak up if I'm wrong) the > DBD::Pg driver reports back a query exception, but not the SQLSTATE. This > means that we can detect an abort (assuming all exceptions are aborts) but > not what caused the abort, except by parsing the error message for text -- a > hazardous approach at best. But you would be right to point out that this is > a problem with the DBD::Pg driver. > > There are, however, other client applications where the problem is more > ingrained. I've done an application for ColdFusion recently, and discovered > that CF is completely unable to detect even the limited error-reporting > capability of ODBC. This means that if CF can't query it, it doesn't exist. Well, I don't think we need exception handling to support failed transactions. Don't these function calls return some failure result code? > > Well, that involves either creating a conditional capability in the > > backend, or in psql, neither of which will happen for 7.5. The best we > > can do is allow COMMIT NESTED INGORE ERROR (or COMMIT NESTED INGORE > > ROLLBACK) and just let the script keep going. I am thinking of cases > > where you want to drop an object you aren't sure exists in a > > transaction. Anything more complicated like issuing a replacement query > > will have to wait for 7.6. > > OK, I didn't realize that it was a difficult thing. I think it should go on > the TODO list but you are the judge of what's a quick fix and what's not. Adding something to psql or the backend like IF (ERROR) ... would be a big job, I would think. > (BTW, aside from this limitation, I am *not* in favor of COMMIT NESTED IGNORE > ERROR) The syntax was for support of script languages that don't have conditional constructs, like psql scripts, where you want the subxact to commit but if it fails, you don't want that to affect the outer transaction. Are you saying there are very few cases where you don't care if the subxact commits or aborts? -- 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
Josh Berkus wrote: > Dennis, > > > The non-standard part I was talking about was the savepoints without > > names, and that is what we should support for ever if we introduce them. > > I don't have a problem with that idea. Anonymous Savepoints should be easy > to support if we are supporting Named (spec) Savepoints. And the two should > even integrate easily -- a *lot* more easily than Savepoints and Nested Xacts > with a different syntax would. And, it's also a convenient shortcut for the > most common case -- transactions with 1 level of nesting and only a couple of > non-overlapping savepoints. > > Of course, if Alvaro can knock out Named Savepoints in a week, then sure, > let's go for it. But I've not heard him saying he can. It seems anonymous savepoints really don't buy us anything. They don't match the Oracle behavior, and don't do anything more than nested transactions. I agree we want them, but I don't see the value they add value right now. > However, this does bring up an important issue; if we implement anonymous > savepoints, then should the current implementation accept savepoint names and > just ignore them? If not, it makes porting and coding for the spec much > more difficult; if so, ported applications could develop subtle erroneous > behaviour through wrong rollbacks. I don't see how we can ignore the savepoint names without having our code work unpredicatably. We could check for the most recent savepoint name and error out if they reference any other name than the most recent savepoint. -- 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
Josh Berkus wrote: > But the ability to detect an abort state *from the SQL command line* > (or a database port connection) has not been addressed. This has existed since 7.4. If some interfaces don't expose it, fix those interfaces.
Bruce Momjian wrote: > >The syntax was for support of script languages that don't have >conditional constructs, like psql scripts, where you want the subxact to >commit but if it fails, you don't want that to affect the outer >transaction. Are you saying there are very few cases where you don't >care if the subxact commits or aborts? > > > Trying to enable nested transaction on something that has no conditionals seems strange to me. If you're writing an app so complicated you so you need NTs, you'd probably not code is as psql script. BTW, do we have real world examples of apps that are waiting to be ported to pgsql, needing nested transactions? Looking at the coding constructions used in those apps could help deciding what semantics would help them. Compiere comes to my mind, being oracle now, so they'd probably prefer named savepoints. Regards, Andreas
Bruce Momjian wrote: > It seems anonymous savepoints really don't buy us anything. They > don't match the Oracle behavior, and don't do anything more than > nested transactions. I agree we want them, but I don't see the value > they add value right now. The value they add is that they follow the SQL standard, which is a lot better sell than "proprietary transaction management scheme". Those people who think they can redefine the SQL standard for purely aesthetic reasons have paid the price over and over again.
Bruce, > It seems anonymous savepoints really don't buy us anything. They don't > match the Oracle behavior, and don't do anything more than nested > transactions. I agree we want them, but I don't see the value they add > value right now. Anonymous Savepoints == Nested Transactions This issue is whether we're going to use a PostgreSQL-specific, non-standard, syntax for NTs, or use a syntax that puts us on the road to implementing spec-compliant savepoints. Given that the functionality is exactly the same in either case, I don't see why you would want to implement syntax which is 100% Postgres-specific. -- -Josh BerkusAglio Database SolutionsSan Francisco
Josh Berkus wrote: > 3) We adopt a slightly mutated form of the SQL3 SAVEPOINT syntax. This would > have the twin benefit of both allowing us to improve our standards compliance > and make savepoints completely compliant in the next version, as well as > helping those wishing to migrate from Oracle to PostgreSQL (currently our > largest source of migrations). Its disadvantage is the subtle differences > between Alvaro's patch and the standard, which might not be obvious to users > and lead to difficult to locate errors. This option also comes in two > flavors: > a) we implement savepoint names, troubleshooting the namespace and scoping > issues, which would really make this a different feature and delay beta > testing, or > b) we do anonymous savepoints for now, which more-or-less exactly matches the > current behavior of Alvaro's patch, and do complaint, named savepoints in the > next version. As Dennis has said, whatever we do now we should support "for ever". If we end up with compliant SAVEPOINT (eventually in 7.6+) plus some nonstandard syntax (from 7.5), what is the nonstandard syntax you would prefer to see? I'd prefer a syntax that reflects the primitives actually in use i.e. BEGIN NESTED. [...] > In other words: > SAVEPOINT == BEGIN NESTED > RELEASE SAVEPOINT == COMMIT NESTED > ROLLBACK TO SAVEPOINT == ROLLBACK NESTED As pointed out by others ROLLBACK TO SAVEPOINT is actually ROLLBACK NESTED + BEGIN NESTED. This means that if we only have savepoint syntax, there is no way to do a plain rollback of a nested transaction (you have to ROLLBACK TO SAVEPOINT foo; RELEASE SAVEPOINT foo which I find pretty ugly and nonobvious, and it gives you an extra empty subtransaction) > But the ability to detect an abort state *from the SQL command line* (or a > database port connection) has not been addressed. There is a transaction state indicator in the V3 protocol's ReadyForQuery message. One of the states is "in aborted transaction". It's been around since 7.4. Whatever logic is needed for running different/conditional SQL based on transaction state then belongs on the client side, IMO. -O
Peter Eisentraut wrote: > Bruce Momjian wrote: > > It seems anonymous savepoints really don't buy us anything. They > > don't match the Oracle behavior, and don't do anything more than > > nested transactions. I agree we want them, but I don't see the value > > they add value right now. > > The value they add is that they follow the SQL standard, which is a lot > better sell than "proprietary transaction management scheme". Those > people who think they can redefine the SQL standard for purely > aesthetic reasons have paid the price over and over again. Uh, anonymous savepoints aren't in the standard, so we aren't any closer to the standard with them or without them, and Oracle doesn't have them either. -- 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
Josh Berkus wrote: > > Well, that involves either creating a conditional capability in the > > backend, or in psql, neither of which will happen for 7.5. The best we > > can do is allow COMMIT NESTED INGORE ERROR (or COMMIT NESTED INGORE > > ROLLBACK) and just let the script keep going. I am thinking of cases > > where you want to drop an object you aren't sure exists in a > > transaction. Anything more complicated like issuing a replacement query > > will have to wait for 7.6. > > OK, I didn't realize that it was a difficult thing. I think it should go on > the TODO list but you are the judge of what's a quick fix and what's not. > > (BTW, aside from this limitation, I am *not* in favor of COMMIT NESTED IGNORE > ERROR) OK, no one likes that idea, so let's forget it. Do we want to allow BEGIN NESTED to start a main transaction? Oracle can use SAVEPOINTS all the time because it knows it is always in a transaction, but PostgreSQL is not always. I don't see a downside to allowing it. COMMIT will still commit the entire transaction, of course. -- 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 Sat, 2004-07-10 at 15:21, Josh Berkus wrote: > Bruce, > > > They have no way of reporting a failed query back to the user? How do > > people program in those environments? Right now any failed query aborts > > the transaction so it seems it would be pretty easy. > > Believe it or not, PHP4 doesn't. This is one of the reasons why coders in > other languages don't consider PHP a "real" programming language; the lack of > exception handling. However, given this limitation we can't really use NTs > in PHP4 anyway, so it's sort of a moot point. Sorry for bringing it up. Uh, I think it can: http://www.php.net/manual/en/function.pg-result-error.php
On Sat, Jul 10, 2004 at 08:25:16PM -0400, Bruce Momjian wrote: > Do we want to allow BEGIN NESTED to start a main transaction? I have a better question: do we allow SAVEPOINT (i.e. to establish a savepoint, i.e. to open a nested transaction) within an aborted transaction block? This is allowed in nested transactions, so: begin; select 0/0; -- aborts begin; select 1; -- the usual "commands will be ignored till the end" commit; commit; -- it really rolls back But in savepoints it's not clear that we want to allow to establish a savepoint, so do you prefer begin; select 0/0; savepoint foo; -- "commands will be ignored" select 1; -- "commands will be ignored" releasefoo; -- "commands will be ignored" commit; -- it really rolls back Or begin; select 0/0; savepoint foo; -- executes it select 1; -- "commands will be ignored" release foo; -- executes it commit; -- it really rolls back There is not a lot of difference. This was allowed in nested transactions because we wanted the nesting be to OK when using it in a possibly aborted transaction block, so the user would not commit a transaction that could not have been created. In savepoints it's a nonissue because the command to end the outer xact is different. My opinion is that we should disallow both SAVEPOINT and RELEASE when in an aborted transaction block. Only ROLLBACK TO, ROLLBACK and COMMIT would be allowed. In this scenario, ROLLBACK TO would always return to a non-aborted transaction state, or the target savepoint would not exist and the state would remain the same. There are several places where the code could be made simpler with this. Opinions please? -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "Siempre hay que alimentar a los dioses, aunque la tierra esté seca" (Orual)
On Sat, 10 Jul 2004, Bruce Momjian wrote: > Oracle can use SAVEPOINTS all the time because it knows it is always in > a transaction, but PostgreSQL is not always. PostgreSQL is also alsways in a transaction. If some use autocommit and go SAVEPOINT foo; RELEASE foo; The first will work and that transaction will end. Then the next is in a new transaction and will fail with an error saying that foo does not exist. That's how it should be. If people don't like or understand autocommit they should not use it. This is especially true in other cases where people do updates that really must be in a single transaction. Your idea is that if we use nested BEGIN/COMMIT one can always issue these even if one does not know if one have done BEGIN before or not. To me this is a problem with autocommit that is solved by not using autocommit. I don't think this is a problem we should solve. -- /Dennis Björklund
Scott, > Uh, I think it can: > > http://www.php.net/manual/en/function.pg-result-error.php Heh. I half-knew that if I pointed this out that someone would correct me with a link to new code. In my defense, I will point out that the mentioned PHP feature is less than 4 months old. > Not a real language indeed. :-) <grin> I hope you relize that that was said as someone who uses PHP for a lot of projects ... -- -Josh BerkusAglio Database SolutionsSan Francisco
Bruce, > Do we want to allow BEGIN NESTED to start a main transaction? Oracle > can use SAVEPOINTS all the time because it knows it is always in a > transaction, but PostgreSQL is not always. I don't see a downside to > allowing it. COMMIT will still commit the entire transaction, of > course. Hmmm. I can see where this could cause trouble, allowing users and developers to be unclear about whether or not they are in an explicit transaction and thus leading to significant debugging issues. So I'm not keen on, it, no. What's the benefit? Elein? And before you start the "function" argument: due to function autocommit, a function is automatically part of a main implict transaction. So functions are a non-argument as they will *always* be using NESTED/SAVEPOINT. This would only become a concern if we started supporting non-transactional stored procedures (ala Sybase) which nobody has even discussed working on. -- -Josh BerkusAglio Database SolutionsSan Francisco
Alvaro, > I have a better question: do we allow SAVEPOINT (i.e. to establish a > savepoint, i.e. to open a nested transaction) within an aborted > transaction block? My opinion? No. I would personally not want to allow it. > My opinion is that we should disallow both SAVEPOINT and RELEASE when in > an aborted transaction block. Only ROLLBACK TO, ROLLBACK and COMMIT > would be allowed. I agree. > In this scenario, ROLLBACK TO would always return to > a non-aborted transaction state, or the target savepoint would not > exist and the state would remain the same. This is also good. From my perspective, as a builder of some *very* database-centric applications, if one has an abort contidition that proceeds to try to establish a Savepoint as if the abort didn't exist then one needs to do some debugging. I'm sorry I missed the original discussion on this or I would have expressed this opinion earlier. For that matter: begin;savepoint; select 0/0; -- abort savepoint; -- commands will be ignored select 1; -- commands will beignored release; -- commands will be ignoredrelease; -- abort main xact 'cause we didn't rollback commit; -- abort message Is the above more or less correct, Alvaro? -- -Josh BerkusAglio Database SolutionsSan Francisco
On Sun, 2004-07-11 at 16:01, Josh Berkus wrote: > Scott, > > > Uh, I think it can: > > > > http://www.php.net/manual/en/function.pg-result-error.php > > Heh. I half-knew that if I pointed this out that someone would correct me > with a link to new code. In my defense, I will point out that the mentioned > PHP feature is less than 4 months old. Actually, it's part of PHP since 4.2.0, which was released on 22 April 2002. That's long enough most folks should know of it by now. (see http://www.php.net/releases.php) > > Not a real language indeed. :-) > > <grin> I hope you relize that that was said as someone who uses PHP for a lot > of projects ... I know you do, I'm just amazed at how many people will dog PHP when it's not the same language they downloaded and tried 5 years ago :-(
On Sun, Jul 11, 2004 at 03:15:46PM -0700, Josh Berkus wrote: > For that matter: > > begin; > savepoint; > select 0/0; -- abort > savepoint; -- commands will be ignored > select 1; -- commands will be ignored > release; -- commands will be ignored > release; -- abort main xact 'cause we didn't rollback > commit; -- abort message > > Is the above more or less correct, Alvaro? Save a minor detail. It would be begin;savepoint; select 0/0; -- abort savepoint; -- commands will be ignored select 1; -- commands will be ignored release; -- commands will be ignored release; -- commands will be ignored commit; -- abort message Note that I'm trying to tell you something with the indenting; all those commands are inside one and the same subtransaction. And I'm not planning to do anonymous savepoint. Do these buy us anything? -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "Find a bug in a program, and fix it, and the program will work today. Show the program how to find and fix a bug, and the program will work forever" (Oliver Silfridge)
Alvaro Herrera wrote: > On Sun, Jul 11, 2004 at 03:15:46PM -0700, Josh Berkus wrote: > > > For that matter: > > > > begin; > > savepoint; > > select 0/0; -- abort > > savepoint; -- commands will be ignored > > select 1; -- commands will be ignored > > release; -- commands will be ignored > > release; -- abort main xact 'cause we didn't rollback > > commit; -- abort message > > > > Is the above more or less correct, Alvaro? > > Save a minor detail. It would be > > begin; > savepoint; > select 0/0; -- abort > savepoint; -- commands will be ignored > select 1; -- commands will be ignored > release; -- commands will be ignored > release; -- commands will be ignored > commit; -- abort message > > Note that I'm trying to tell you something with the indenting; all those > commands are inside one and the same subtransaction. > > And I'm not planning to do anonymous savepoint. Do these buy us > anything? Don't bother if you can do named ones. -- 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
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > To my current knowledge (and hopefully Andrew will speak up if I'm > wrong) the DBD::Pg driver reports back a query exception, but not > the SQLSTATE. The current production driver will report back the error, but not the SQLSTATE. The next version (now in cvs) will report back the SQLSTATE. It will probably go beta in a couple of weeks and eventually become version 1.33. - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200407102121 -----BEGIN PGP SIGNATURE----- iD8DBQFA8e5QvJuQZxSWSsgRAo5TAKDD1OX5xV4mfyUC8RAt+0SA8gbjiACeJIFV 2rZfNrm9OTFJ+/kzAjUiMJM= =TkiX -----END PGP SIGNATURE-----
On 7/10/2004 6:55 PM, Josh Berkus wrote: > Bruce, > >> It seems anonymous savepoints really don't buy us anything. They don't >> match the Oracle behavior, and don't do anything more than nested >> transactions. I agree we want them, but I don't see the value they add >> value right now. > > Anonymous Savepoints == Nested Transactions Almost > > This issue is whether we're going to use a PostgreSQL-specific, non-standard, > syntax for NTs, or use a syntax that puts us on the road to implementing > spec-compliant savepoints. > > Given that the functionality is exactly the same in either case, I don't see > why you would want to implement syntax which is 100% Postgres-specific. > I don't think they are 100% the same. The SQL3 spec defines in 7.15 and 13.4 that each sql procedure statement and each subquery on close implicitly destroy all savepoints that have been created during that statement or subquery. I am however certain that nested transactions do not offer any additional functionality that would not be available through savepoints. So what I am missing is the reason why we would want a non-standard syntax at all. Especially using the keyword BEGIN in the syntax would strike me as dumb, because it will create a parsing and reading nightmare for PL/pgSQL, since that language uses BEGIN ... END; for grouping statements like C uses curly braces. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
On 7/11/2004 12:22 AM, Alvaro Herrera wrote: > There is not a lot of difference. This was allowed in nested > transactions because we wanted the nesting be to OK when using it in a > possibly aborted transaction block, so the user would not commit a > transaction that could not have been created. In savepoints it's a > nonissue because the command to end the outer xact is different. > > > My opinion is that we should disallow both SAVEPOINT and RELEASE when in > an aborted transaction block. Only ROLLBACK TO, ROLLBACK and COMMIT > would be allowed. In this scenario, ROLLBACK TO would always return to > a non-aborted transaction state, or the target savepoint would not > exist and the state would remain the same. As I interpret the spec ROLLBACK TO foo will rollback all savepoints that have been created since savepoint foo was created including ones explicitly released. That means, that every subxid >= foo is aborted, and a new foo subtransaction created. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
<posted & mailed> Dennsnippetssklund wrote: > On Fri, 9 Jul 2004, Mike Rylander wrote: > >> Nested transactions and savepoints serve two different purposes. They >> have some overlap, but for the most part solve two distinct problems. > > Then show some examples that illustrait the difference. So far all > examples shown that uses subtransactions could just as well have been > written using savepoints. > After seeing some more snippets of the SQL2003 spec it seems that this is true, and that there is more of a syntactic difference than functional. This does not seem to be the case for Oracle (the other major implementation that has been cited for SAVEPOINT syntax), as savepoints in Oracle are not logically nested. Note that I am going on the statements from others on this list for this point... > I don't agree that they have two different purposes. They do, if only to make particular constructs easier to write. This is an opinion, but for example an EXCEPTION framework for plpgsql would be easier to implement and use if it used the nested transactions rather than savepoint syntax: CREATE FUNCTION blah () RETURNS INT LANGUAGE PLPGSQL AS ' BEGIN BEGIN NESTED; do some work... BEGIN NESTED; do other work... EXCEPTION WHEN SQLSTATE = already_exists THEN do alternate work with its own errorchecking... END NESTED; EXCEPTION WHEN SQLSTATE = fkey_violation THEN ROLLBACK NESTED; END NESTED; END;'; I realize this can be done with nested savepoints and that is what the spec requires, but in other major implementations of savepoints this nested exception handling would be more difficult to write. Again, simply my opinion. > >> I don't think so, especially as there has been some talk of implementing >> savepoints as a subset of nested transactions. > > It is not a subset. It's the other way around. Nested transactions are a > subset of savepoints Perhaps I got my wires crossed a bit there. And again, after looking at some more of the SQL2003 spec this does seem to be the case. I cry your pardon! :) > > Savepoints have more possibilities, you can invalidate older savepoints > then the last (with subtransactions you can only commit/rollback the > last). This implies that savepoints are flat. It won't be that way under the covers, but it does give that impression, and flat savepoint space is definitely suited to a different class of problems than nested transactions. > If you don't use that then it's exactly the same as > subtransactions. > I don't see this. Nested transactions present a hierarchal interface to the user, savepoints don't, especially considering that those familiar with PL/SQL know that savepoints are not nested. Now, savepoints can be used IN a hierarchy, but they do not DEFINE one as nested transactions do. I look at it this way: Let's have both, and where a user wants a flat transaction space, as that may suit the needs of the problem, they will use SAVEPOINT syntax; if the user would perfer an explicit hierarchy they can use nested transactions. Everyone wins! > The only "feature" subtransactions have that savepoints doesn't is the > lack of names. Every savepoint have a name. If we want an extension it > could be to get the database to generate a fresh savepoint name. The > client can of course also generate unique savepoint names if it want. I don't think they can be compared like that, feature for feature. Although I agree with you that they provide extremely similar feature sets, the present different interfaces to the user. They may end up being backed by the exact same code but the syntax and logical structure will surely differ, and when a user wants labeled rollback point they will use savepoints. When s/he wants hierarchical rollback points they will use the nested transactions syntax. BTW, I would imagine that savepoints will be implemented as nested transactions with detachable labels... the label can move from a transaction to one of its descendants, and that outer (sub)transaction will be implicitly COMMITed with its parent. > > That subtransactions do more than savepoints is just smoke an mirrors. So > far there have been no example to validate that point of view, and I don't > think there will be any. If anyone know of something that you can do with > subtransactions and not with savepoints, please speak up. > You have opened my eyes to the fact that savepoints and nested transactions can be used for most of the same problems, however I don't see this as a one-or-the-other proposition. Alvaro found it easier to implement nested transactions, he forged ahead and did it. Now, because of good design or simple luck, we should be able to implement savepoints fairly easily. To me this is the best we could have hoped for, as it means that not only will be support the entire SQL2003 spec WRT savepoints, we actually get to present a richer interface to the user, one that includes a feature explicitly designed to model the hierarchical nature of certain datasets and/or solutions. Of course anyone reading this can guess which interface I am looking forward to, but the point is that we will have both where most others don't have a complete implementation of either! --miker
<posted & mailed>
Dennis Bjorklund wrote:
> On Sat, 10 Jul 2004, Mike Rylander wrote:
>
>> They do, if only to make particular constructs easier to write. This is
>> an opinion, but for example an EXCEPTION framework for plpgsql would be
>> easier to implement and use if it used the nested transactions rather
>> than savepoint syntax:
>>
>> CREATE FUNCTION blah () RETURNS INT LANGUAGE PLPGSQL AS '
>> BEGIN
>> BEGIN NESTED;
>> do some work...
>> BEGIN NESTED;
>> do other work...
>> EXCEPTION WHEN SQLSTATE = already_exists THEN
>> do alternate work with its own error checking...
>> END NESTED;
>> EXCEPTION WHEN SQLSTATE = fkey_violation THEN
>> ROLLBACK NESTED;
>> END NESTED;
>> END;';
>>
>> I realize this can be done with nested savepoints and that is what the
>> spec requires,
>
> Lets look at what it can look like:
>
> BEGIN
> SAVEPOINT nested;
> do some work...
> SAVEPOINT nested2;
> do other work...
> EXCEPTION WHEN SQLSTATE = already_exists THEN
> ROLLBACK TO SAVEPOINT nested2;
> do alternate work with its own error checking...
> RELEASE nested2;
> EXCEPTION WHEN SQLSTATE = fkey_violation THEN
> ROLLBACK TO SAVEPOINT nested;
> RELEASE nested;
> END;
>
>
> Now, in what way is this more complicated?
Only in that you need to define a name for each savepoint in order to create
the hierarchy. And that is my point, savepoints impose more work on the
user to create a logical hierarchy, not that they cannot be used for
hierarchical structures.
>
> I'm not 100% sure how the exceptions that you used above work. Do that
> always rollback the transaction thay are in? In one of the exceptions you
> did a rollback but not in the other. In my example I added a rollback in
> the first exception handler. Maybe you forgot it there?
That was just pseudo-code and wholly invented in my head, but based on an
earlier expample of possible EXCEPTION syntax. The idea is that when a
subtransaction is in an aborted state due to an error the EXCEPTION clause
would implicitly roll back that subtransaction and open a new transaction
from its own block. This EXCEPTION subtrans is only used in the case of an
error in the matching BEGIN NESTED block, and the two share the COMMIT
statement, syntacticly speaking. Think of it as a "try { ... } catch
[type] { ... } finally { commit }" type structure.
>
> In any case. I don't see this as any harder then your example.
>
It's not harder, per se, but it does impose a more difficult to maintain
syntax, IMHO.
>> > Savepoints have more possibilities, you can invalidate older savepoints
>> > then the last (with subtransactions you can only commit/rollback the
>> > last).
>>
>> This implies that savepoints are flat. It won't be that way under the
>> covers, but it does give that impression, and flat savepoint space is
>> definitely suited to a different class of problems than nested
>> transactions.
>
> First, my claim above was wrong. As Gavin pointed out in another mail, if
> one have savepoints p1 and p2 and release p1 then also p2 is released.
> It's possible to implement both kinds of behaviour using Alvaros work, but
> the standard demands the simpler one where p2 is also released.
>
> Now, about the flatness. Savepoints are not flat. They are sort of flat in
> a savepoint level. But, for example when you call a function you get a new
> savepoint level. I actually don't want to call it flat at all. The example
> above does not overwrite the savepoints "nested" and "nested2" that might
> exist before the call, since this is a higher savepoint level.
>
OK, savepoints are not REALLY flat, but they are not hierarchically nested
either. They are cumulative. They can be used, as you showed above, in a
hierarchy, but as I said, they are not by their nature "nested".
> I'm not sure exactly what it is that defines a new savepoint level, but a
> function call does and maybe some other things.
>
As for savepoint levels in functions, that is a scoping issue imposed by the
functions themselves, not by the savepoint syntax. It would be nonsensical
to rollback to a savepoint outside a function, just as it would be
nonsensical to rollback the outer transaction from within the function.
Allowing either would cause undesired "action at a distance" and possibly
violate the A in ACID. The way I see it, savepoint levels should be
specified by function calls, as you said, and by the transaction nesting
level.
>> BTW, I would imagine that savepoints will be implemented as nested
>> transactions with detachable labels... the label can move from a
>> transaction to one of its descendants, and that outer (sub)transaction
>> will be implicitly COMMITed with its parent.
>
> Yes. That's my view as well.
>
Well, at least we agree on that ;)
>> Alvaro found it easier to implement nested transactions, he forged ahead
>> and
>> did it. Now, because of good design or simple luck, we should be able to
>> implement savepoints fairly easily.
>
> I think the difference between them are so small that it's not a big deal
> at all. In my view savepoints and nested transactions are almost the same
> thing. The only difference being that the savepoints have names.
> Savepoints are nested. You can not have savepoints p1 and then p2 and try
> to only rollback p1. Then you rollback p2 as well, why. Because they are
> nested.
>
Well, at this point there is a great difference when compared to other
implementations. And, in reality, that is our competition. The spec is
there to "level the playing field", as it were. And with a nested
transaction-backed implementation of savepoints we will be closer to the
goal line than our competition.
>> spec WRT savepoints, we actually get to present a richer interface to the
>> user
>
> If it's richer or not is the question. And then one have to compare that
> to the downside of adding a non standard interface.
>
And the upside, which I consider great. I could see it becoming an
implementation leader for others to follow.
> I don't think it is richer at all, but I'd be happy to change my mind if
> someone can show an example where nested transactions solve something that
> you can't just as well solve with savepoints.
>
It is yet to be seen if nested transactions in PG will be everythink I hope
they can be, and perhaps the benefits will indeed be individually
qualitative, instead of globally quantitative.
Therefore, I don't know if I can show empirically that having user-exposed
nested transaction is "better" because it comes down to individual choice
of style. I can definitely see nested transactions containing (and
defining the level of) savepoints as being a HUGE boon to the logical
maintainability of stored procedures and long running, recurring scripts.
The end result will be more tools in the hands of users. I am sure the docs
will explain that (currently) PG nested transactions are an extention to
the standard.
--
--miker