subtransactions and FETCH behaviour (was Re: PREPARE and transactions)

Jeroen T. Vermeulen wrote:

> That makes me wonder why people want to maintain transactionality w.r.t.
> nested transactions but not with "outer" ones.  Odd!

Yep.

> But then the FETCH should still occur before the transaction as far as I'm
> concerned.  You fetch a batch (if it fails, you terminate) and *try* to
> process it.

This is a non-starter for JDBC: it has no control over when an 
application decides to access a ResultSet in a way that results in a 
FETCH of new data.

Buffering *all* the ResultSet data client-side isn't an option -- 
cursors are used specifically to handle resultsets that don't fit into 
heap on the client side. And implementing a disk cache or similar a) 
doesn't work if you don't have disk access, b) is bandwidth-intensive 
and c) is really silly -- that's work that belongs on the server side, 
or why bother with implementing cursors at all?!

Invalidating all open resultsets on creation of a savepoint would make 
savepoints useless in many cases, and isn't hinted at in the JDBC spec 
for savepoints so is likely to break many otherwise portable apps.

Having ResultSets spontaneously change position on transaction 
boundaries would cause even more portability problems -- and it goes 
completely against how that API is designed (it's meant to *insulate* 
the application from details like cursors that may be used behind the 
scenes).

>>I don't like rollback of FETCH for much the same reasons as I don't like 
>>rollback of PREPARE -- lots more work on the client side. See my mail on 
>>the other thread. Avoiding changing the behaviour of FETCH in the above 
>>case is also an argument against it.
> 
> 
> In the case of FETCH, where does that extra work come from?

See my other email. The driver will either have to use SCROLL cursors 
and FETCH ABSOLUTE everywhere (which involves an extra Materialize step 
in the plan for nontrivial queries) or track each open cursor's position 
at the start of every active subtransaction so it can restore that 
information on rollback. The driver needs to track where the server 
thinks the cursor is positioned so it can do an appropriate FETCH or 
Execute when the application requests data in a resultset that's not 
currently available on the client side.

Reporting the new cursor positions at the protocol level when rollback 
happens might help but it's still fairly ugly and would need a protocol 
version change.

Also consider that the V3 protocol Execute message is essentially a 
FETCH (you can only do FETCH FORWARD count, but it's otherwise 
equivalent). This is another case of overlap between the SQL level and 
the protocol level and has much of the same problems as we have with 
PREPARE vs. Bind/Execute. The protocol-level portal state shouldn't 
suddenly change on a transaction boundary. I can understand closing 
nonholdable portals when the creating transaction closes (the data 
source just disappeared) but having the portal change *position* would 
be very weird.

-O