Alvaro Herrera wrote:
On Fri, Jul 02, 2004 at 01:37:46PM -0500, Thomas Swan wrote:This is the reason the outermost block is irrelevant to the point. Inner transactions (including the implicit ones mentioned) commit only if their parent transactions commit. If there is an implicit begin/commit, then everything underneath should be subbegin/subcommit. If it is sometimes implicit then the subbegin/begin state is non-deterministic. Without the underlying or stack depth, it is difficult to predict. In psql, autocommit (on/off) behavoir becomes a little muddy if you go with the SUBBEGIN and SUBCOMMIT construct.
Alvaro Herrera wrote:
Isn't this counterintuitive. It seems that BEGIN and COMMIT/ABORT should be sufficient regardless of the level. If you are inside a current transaction those commands start a new transaction inside of the current transaction level, just like pushing on and popping off elements on a stack.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.
No, the first level is quite different from any other, and that's why it should use a different syntax. Really any level above level 1 is not a transaction at all; it's a unit that you can rollback independently but nothing more; you can't commit it independently. I think a better term than "subtransaction" or "nested transaction" is "rollback unit" or some such.
Some functions and procedures may not be called inside of transactions or subtransactions.
No. Some functions cannot be called inside a transaction block. Whether you are or not inside a subtransaction within the transaction block is not important. In fact, the application doesn't care what nesting level it is in; the only thing that it cares about is if it is in a transaction block or not.
Please note that I'm using the term "transaction block" and not
"transaction." The distinction is important because everything is
always inside a transaction, though it may be an implicit one. A
transaction block, on the other hand, is always an explicit thing.
And a subtransaction is also an explicit thing.
Below should BEGIN (1) be a SUBBEGIN or a BEGIN? Both examples would give equivalent results.
-- BEGIN (implicit) BEGIN (1) BEGIN SOMETHING BEGIN SOMETHING ROLLBACK ROLLBACK COMMIT (1) COMMIT (implicit) -- BEGIN (1) BEGIN SOMETHING BEGIN SOMETHING ROLLBACK ROLLBACK COMMIT (1) --
Unless you have some way to tell (by query) the state you are in is a subtransaction and how many levels you are deep into the nested transaction, deciding whether to use SUBBEGIN and SUBCOMMIT/SUBABORT vs the traditional BEGIN COMMIT/ABORT becomes nondeterministic.
The application always has to keep track if it is inside a transaction
block or not. This has always been true and it continues to be so.
Whether you are inside a subtransaction or not is not really important.
If you want to commit the whole transaction block just issue COMMIT, and
all levels will be committed.
psql will tell me how deep I am in transactions?
That's an rather big assumption? It may not be the app, it may include stored procedures and functions as well. Imagine a little function called dance( ). Dance begins a transaction, does a little work, and then aborts. If I am not in a transaction and I write dance as a subtransaction then I have the problem and call it then I have a problem. If I am in a transaction and I write dance as a transaction, then I have a problem. There's no universal way to write the function, without having to refer to an external state unless I make the scope universal. Hence, SUBBEGIN and SUBCOMMIT are bad ideas.Similarly if you want to abort. But if you want to retry a subtransaction which has just failed you better know whether you are on a subtransaction or not ... I mean if the app doesn't know that then it isn't using subtransactions, is it?
Knowing just the nesting level will not help you -- the app has to knowIf you have the nesting level, then you know how many commits/rollbacks to perform to get to an entrance state.
_what_ to retry. And if it isn't going to retry anything then there's
no point in using subtransactions at all.
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html