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
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
