On Tue, Jul 06, 2004 at 12:17:50AM +1200, Oliver Jowett wrote: > 7.4/7.5's behaviour leaves the cursor state unchanged by the rollback: > > DECLARE foo CURSOR WITH HOLD FOR SELECT * FROM sometable > > BEGIN > FETCH FORWARD 10 FROM foo -- returns rows 1..10 > ROLLBACK > > BEGIN > FETCH FORWARD 10 FROM foo -- returns rows 11..20 > ROLLBACK That makes me wonder why people want to maintain transactionality w.r.t. nested transactions but not with "outer" ones. Odd!
I can see the technical problem, of course, although I think it should respect rollbacks if at all possible without sacrificing significant performance *in the commit case.* Verify for failure, but optimize for success. Even if the cursor cannot go backwards I'd rather see those rows buffered and "spliced back" into the cursor's stream on rollback. Perhaps the reasoning is that you process the batches returned by the cursor inside a transaction, and have separate error handling for failed batches. 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. > >There's just been a discussion here about how > >nested transactions should not be allowed to FETCH from cursors defined in > >a wider scope for precisely this reason: to ensure neat transactional > >behaviour. > > This breaks things like JDBC that want to use cursors to batch access to This is a restriction on nested transactions, which aren't even in a real release yet. I thought you said you can't break compatibility without changing the code? ;) > 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? Jeroen ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])