Re: [HACKERS] Nested Transactions, Abort All
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. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nested Transactions, Abort All
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. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Nested Transactions, Abort All
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
Re: [HACKERS] Nested Transactions, Abort All
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 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, 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
Re: [HACKERS] Nested Transactions, Abort All
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 Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Nested Transactions, Abort All
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 Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Nested Transactions, Abort All
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 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? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Nested Transactions, Abort All
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 :-( ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nested Transactions, Abort All
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) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Nested Transactions, Abort All
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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Nested Transactions, Abort All
-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 [EMAIL PROTECTED] PGP Key: 0x14964AC8 200407102121 -BEGIN PGP SIGNATURE- iD8DBQFA8e5QvJuQZxSWSsgRAo5TAKDD1OX5xV4mfyUC8RAt+0SA8gbjiACeJIFV 2rZfNrm9OTFJ+/kzAjUiMJM= =TkiX -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Nested Transactions, Abort All
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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Nested Transactions, Abort All
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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Nested Transactions, Abort All
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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Nested Transactions, Abort All
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. HmmmI'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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Nested Transactions, Abort All
Bruce Momjian wrote: Interesting Oracle doesn't support RELEASE or savepoint levels: T271, SavepointsOracle 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Nested Transactions, Abort All
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 implicitly s2 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nested Transactions, Abort All
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? 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Nested Transactions, Abort All
Dennis Bjorklund [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Nested Transactions, Abort All
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nested Transactions, Abort All
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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Nested Transactions, Abort All
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) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Nested Transactions, Abort All
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 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. It might seem absurd to you, given the implementation you have made. -- /Dennis Björklund ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Nested Transactions, Abort All
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, 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. 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 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 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
Re: [HACKERS] Nested Transactions, Abort All
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) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Nested Transactions, Abort All
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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Nested Transactions, Abort All
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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Nested Transactions, Abort All
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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Nested Transactions, Abort All
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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Nested Transactions, Abort All
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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nested Transactions, Abort All
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Nested Transactions, Abort All
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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Nested Transactions, Abort All
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Nested Transactions, Abort All
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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Nested Transactions, Abort All
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 Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nested Transactions, Abort All
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 Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Nested Transactions, Abort All
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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Nested Transactions, Abort All
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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Nested Transactions, Abort All
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. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Nested Transactions, Abort All
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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Nested Transactions, Abort All
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. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Nested Transactions, Abort All
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 Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Nested Transactions, Abort All
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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Nested Transactions, Abort All
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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Nested Transactions, Abort All
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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Nested Transactions, Abort All
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nested Transactions, Abort All
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 release foo; -- 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) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Nested Transactions, Abort All
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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Nested Transactions, Abort All
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
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Nested Transactions, Abort All
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 x INSERT INTO ...; SAVEPOINT x; INSERT INTO ...; COMMIT; Andreas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Nested Transactions, Abort All
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. Regards, Andreas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Nested Transactions, Abort All
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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Nested Transactions, Abort All
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Nested Transactions, Abort All
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Nested Transactions, Abort All
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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Nested Transactions, Abort All
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) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Nested Transactions, Abort 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 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Nested Transactions, Abort All
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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Nested Transactions, Abort All
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Nested Transactions, Abort All
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. HmmmI'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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Nested Transactions, Abort All
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) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Nested Transactions, Abort All
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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Nested Transactions, Abort All
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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Nested Transactions, Abort All
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) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Nested Transactions, Abort All
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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Nested Transactions, Abort All
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. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Nested Transactions, Abort All
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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Nested Transactions, Abort All
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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Nested Transactions, Abort All
Oliver Jowett wrote: The 2003 draft claims that RELEASE SAVEPOINT invalidates savepoints subsequent to the RELEASE: subsequent to the released savepoint rather. -O ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nested Transactions, Abort All
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=806709dl=ACMcoll=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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Nested Transactions, Abort All
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) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Nested Transactions, Abort All
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) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Nested Transactions, Abort All
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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Nested Transactions, Abort All
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
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] Nested Transactions, Abort All
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. Rollback inner 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.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Nested Transactions, Abort All
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 places
o doesn't work well with other xact synonyms like BEGIN/END or
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;
INSERT INTO ...;
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
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Nested Transactions, Abort All
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nested Transactions, Abort All
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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Nested Transactions, Abort All
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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Nested Transactions, Abort All
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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Nested Transactions, Abort All
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 SAVEPOINT s_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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Nested Transactions, Abort All
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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Nested Transactions, Abort All
On Tue, 2004-07-06 at 23:36, Greg Stark wrote: Scott Marlowe [EMAIL PROTECTED] 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? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Nested Transactions, Abort All
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. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Nested Transactions, Abort All
Scott Marlowe wrote: On Tue, 2004-07-06 at 23:36, Greg Stark wrote: Scott Marlowe [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Nested Transactions, Abort All
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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Nested Transactions, Abort All
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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Nested Transactions, Abort All
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) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nested Transactions, Abort All
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) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Nested Transactions, Abort All
* Alvaro Herrera ([EMAIL PROTECTED]) 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 signature.asc Description: Digital signature
Re: [HACKERS] Nested Transactions, Abort All
On Tue, Jul 06, 2004 at 12:49:46PM -0400, Stephen Frost wrote: * Alvaro Herrera ([EMAIL PROTECTED]) 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) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Nested Transactions, Abort All
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. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Nested Transactions, Abort All
Scott Marlowe [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Nested Transactions, Abort All
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) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Nested Transactions, Abort All
Greg Stark wrote: Alvaro Herrera [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Nested Transactions, Abort All
Alvaro Herrera [EMAIL PROTECTED] 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
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Nested Transactions, Abort All
Greg Stark [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Nested Transactions, Abort All
Tom Lane wrote: Mike Benoit [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Nested Transactions, Abort All
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Nested Transactions, Abort All
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) ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
