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

Tom Lane wrote:

> I don't like using ParameterStatus because it's not designed for dealing
> with values that may change many times in a single query.  Also it sends
> strings, which this really isn't.

What about including the new nesting level in the 
SUBBEGIN/SUBCOMMIT/SUBABORT CommandStatus string? Yes, it's still a 
string for a numeric nesting level, but that's also how we pass numeric 
data such as insert/update rowcounts.

> I haven't looked at JDBC, but at least in the libpq code, what we could
> safely do is extend the existing no transaction/in transaction/in failed
> transaction field to provide a five-way distinction: those three cases
> plus in subtransaction/in failed subtransaction.  You could not directly
> tell the depth of your subxact from this, but do you need to?

This will break the existing JDBC driver in nonobvious ways: the current 
code silently ignores unhandled transaction states in ReadyForQuery, so 
you could conceivably end up in situations where the driver thinks 
you're outside a transaction when you're actually inside a 
subtransaction, and issues spurious BEGINs. It's simple enough to handle 
the new states, but it *is* an incompatible protocol change. Please bump 
the protocol version if you do make this change. And if you're going to 
bump the protocol version, I have some other changes I'd like to see at 
the same time :)

If named SAVEPOINT syntax (along the lines of what Oracle has) is 
available in 7.5, and there is a mechanism to COMMIT/ROLLBACK the 
top-level transaction directly, the JDBC driver doesn't actually need to 
know anything about subtransactions to correctly implement JDBC's 
commit, rollback, & savepoint interface -- except perhaps to deal with 
the case where the application rolls back a subtransaction which 
invalidates an earlier SAVEPOINT. Having the nesting level available 
will probably make savepoint support a bit easier but it's not vital.

If we don't have COMMIT/ROLLBACK of top-level transactions, then the 
JDBC driver needs to know the current nesting level so it knows how many 
subtransactions to deal with when a top-level commit/rollback is 
requested via the JDBC API. I'd actually prefer to see plain COMMIT and 
ROLLBACK continue to operate on the top-level transaction, since doing 
anything else is going to break pre-7.5 clients (such as older JDBC 
drivers) that assume that you're entirely outside a transaction after 
COMMIT/ROLLBACK.

If we don't have SAVEPOINT syntax, then the JDBC driver needs to know 
the current nesting level so it can track which (client-maintained) 
savepoints are still valid in the face of the application doing 
subtransaction work itself.

The only other thing the JDBC driver uses the transaction state for is 
to work out when BEGINs are needed when JDBC-level autocommit is off, 
and when to allow transaction isolation level changes. As far as I can 
see, "in subtransaction" is equivalent to "in top-level transaction" for 
both of those cases.

So overall, knowing that you're in a subtransaction without knowing the 
nesting level does not seem very useful..

-O