Re: Nested Transaction TODO list

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