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