Re: [Re] Re: PREPARE and transactions

Jeroen T. Vermeulen wrote:

> Well, I'd say DEALLOCATE is implicitly lumped in with PREPARE.  But as for
> FETCH, are you referring to cursors that live outside transactions (but get
> manipulated inside transactions)?  Are those implemented yet and if so, how
> does FETCH work there? 

I'm thinking WITH HOLD cursors -- they've been around since 7.4. 
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

> 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 
a large resultset. Saying that you can't access resultsets created 
before opening a new subtransaction (or equivalently, before a 
SAVEPOINT) -- but only if the driver has decided to use a cursor behind 
the scenes! -- is a pretty draconian requirement and certainly isn't in 
the JDBC spec anywhere. Iterating through a resultset emitting updates 
is a pretty common model, and you may well want a savepoint just before 
starting on the updates.

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.

-O