Re: Portals and nested transactions

Josh Berkus wrote:
> Tom,
>
> As much as I can understand the arguments -- many of them performance-oriented
> -- for handling Portals non-transactionally, I simply don't see how we can do
> it and not create huge problems for anyone who uses both cursors and NTs
> together ... as those who use either are liable to do.

I'd argue against rolling back portal state on subxact commit for three
reasons that aren't performance-related: it makes (some?) client code
harder, it's incompatible with other implementations of savepoints, and
it's inconsistent with how WITH HOLD cursors already behave.

...

The JDBC driver is going to be unhappy if this happens. It is not
expecting the portal state of any cursors backing its ResultSets to
change unexpectedly, as a ROLLBACK TO SAVEPOINT will do. To correctly
handle this, at a minimum it needs notification of changes to the
transaction nesting level as they happen (did anything get resolved
here?); then it has to store the client-side state of each open portal
whenever a new subxact (== SAVEPOINT) is opened, and restore the
appropriate state on rollback.

I'd expect any layer that uses portals/cursors to buffer results to have
similar problems.

There are two problems going on here:

1) The state of the portal is not necessarily directly visible to the
application -- in the case of the JDBC driver they are used to buffer
large resultsets -- so at that level the behaviour on rollback isn't
visible or useful to the application anyway, and rolling back state
actually makes life more difficult for the buffering code.

2) The application-visible result object semantics (the ResultSet in
JDBC's case) may have its own semantics that don't correspond to the
behaviour of portals, and it may not be possible to arbitarily change
the result object's semantics (the only thing that the JDBC spec says
about ResultSets vs. ROLLBACK is specifying the holdability of the
resultset -- rolling back resultset state on rollback to savepoint is
going to break most existing JDBC apps that use savepoints, IMO).

So the driver ends up doing lots of extra work to fake nontransactional
behaviour.

...

Rolling back state is the opposite of what DB2 does according to the DB2
docs, as I mentioned in an earlier email:

# The impact on cursors resulting from a ROLLBACK TO SAVEPOINT depends
on the statements within the savepoint
* If the savepoint contains DDL on which a cursor is dependent, the
cursor is marked invalid. Attempts to use such a cursor results in an
error (SQLSTATE 57007).
* Otherwise:
o If the cursor is referenced in the savepoint, the cursor remains
open and is positioned before the next logical row of the result table.
(A FETCH must be performed before a positioned UPDATE or DELETE
statement is issued.)
o Otherwise, the cursor is not affected by the ROLLBACK TO
SAVEPOINT (it remains open and positioned).

I don't know what Oracle does.

The 2003 draft says that the behaviour of cursors established before the
savepoint that was rolled back to is implementation-defined. Bah.

...

Finally, we don't roll back WITH HOLD cursor state on top-level
transaction rollback. Why are the semantics in a subxact rollback different?

-O