Re: [Re] Re: PREPARE and transactions

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