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
