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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
