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