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
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
