Re: Nested Transaction TODO list - Mailing list pgsql-hackers

From Oliver Jowett
Subject Re: Nested Transaction TODO list
Date
Msg-id 40E8CD25.4060505@opencloud.com
Whole thread Raw
In response to Nested Transaction TODO list  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Nested Transaction TODO list
List pgsql-hackers
Tom Lane wrote:

> Still need to agree about externally visible behavior (a different stmt
> than begin/commit for subxacts?  What about savepoints?)  Also, what about
> exposing this functionality in plpgsql?  Seems like we need some kind of
> exception handling syntax to make this useful.  What does Oracle do?

As I just mentioned in another thread, whatever the syntax for nested 
transactions I'd like to see plain COMMIT/ABORT/ROLLBACK always affect 
the top-level transaction.

Oracle appears to have:
  SAVEPOINT savepointname  ROLLBACK [WORK] [TO [SAVEPOINT] savepointname]

You can issue SAVEPOINT with the same name while the old savepoint is 
valid, and the name will be moved. Rolling back to a savepoint does not 
invalidate that savepoint, i.e. you can roll back to a savepoint 
multiple times.

One generalization of this to nested transactions would be:
 SUBBEGIN [transactionname] SUBCOMMIT [transactionname] SUBABORT [transactionname]

SUBBEGIN outside an explicit transaction block works like BEGIN.

Active transactions may have names. SUBBEGIN with a name associates the 
name with the new transaction; if the name is already in use, it's also 
removed from the old transaction. Alternatively we could only look at 
the most-deeply-nested transaction with a given name when specifying 
transactions by name. That would make savepoint behaviour slightly 
different to Oracle (Oracle could see a savepoint as invalid that we 
consider valid), but it looks like it'd make things a bit easier for 
procedural languages as functions can't accidentally trash a name 
"belonging" to your caller so long as they resolve all transactions they 
start.

SUBCOMMIT or SUBABORT work on the current transaction level (if no name 
is specified) or all transactions down to (and including) the named 
transaction level if a name is given.

"SAVEPOINT savepointname" becomes an alias for "SUBBEGIN savepointname". 
"ROLLBACK TO [SAVEPOINT] savepointname" becomes an alias for "SUBABORT 
savepointname; SUBBEGIN savepointname".

We could spell SUBBEGIN and friends differently -- is it better to add 
more syntax to the existing transaction manipulation commands along the 
lines of "BEGIN [NESTED] [TRANSACTION|WORK] [transactionname]", 
"ROLLBACK [NESTED] [TRANSACTION|WORK] [transactionname]" etc?

Any comments?

-O


pgsql-hackers by date:

Previous
From: Christopher Kings-Lynne
Date:
Subject: Re: Adding column comment to information_schema.columns
Next
From: Bruce Momjian
Date:
Subject: My trip to Germany and Armenia