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.

> What I think we could do, though, is record the Portal's high-level state
> as the number of rows fetched from it.  On abort, rewind the Portal and
> then fetch that number of rows again (this is the same method used by
> MOVE ABSOLUTE).  We could optimize things a little bit by not doing this
> repositioning until and unless the Portal is actually used again.  Still,
> it wouldn't be cheap...

 From what you're describing, this seems like the wisest course.   I can't 
endorse us getting into any situation where *some* operations are rolled back 
by an NT abort, and some are not.    That seems like begging for 12-hour-long 
debugging sessions.

The only cost of doing things transactionally seems to be the performance cost 
of re-fetching the Portal in the event of a subtransaction abort containing a 
Portal command.   If it's a comparison between the performance loss of 
re-fetching a Portal, and the debugging nightmare of not knowing what state a 
Portal is in after an abort and rollback (consider NTs containing loops), 
I'll take the latter any day.   

> Of course this only handles SELECT-query portals, not portals that contain
> data-modification commands.  But the latter cannot be suspended partway
> through anyhow, so there is no scenario where we need to recover to a
> partly-executed state.  (Recall what I said before about not allowing
> continuation of a portal that itself got an error.)

Yes, and the possibility of updatable cursors makes the transactional argument 
even more compelling.   

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Wed, 14 Jul 2004 10:01:27 -0700
Josh Berkus <josh@agliodbs.com>
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

Thu, 15 Jul 2004 09:52:35 +1200
Oliver Jowett <oliver@opencloud.com>
Re: Portals and nested transactions