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