Re: nested-xacts cursors (was Re: Performance with new

Tom Lane wrote:
> Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> 
>>Well, my opinion is that cursors and other resources should at least be
>>usable from a inner subtransaction in its parent -- because if that
>>can't be done we are wasting some of the benefits, because we can't just
>>"stick everything in a subtransaction" to be able to retry if it fails.  
>>It is a pity that we can't roll back FETCH or lo_close() but at least we
>>can keep them declared/open across a subtransaction commit.
> 
> 
> AFAICS we can't allow an inner transaction to use a cursor that was
> declared in an outer transaction, because if the inner transaction fails
> then it's not just a matter of the FETCH not rolling back; the
> subtransaction abort will restore state in the bufmgr and other places
> that is simply inconsistent with the state of the cursor's plantree.

Another data point: DB2 claims this behaviour:

# The impact on cursors resulting from a ROLLBACK TO SAVEPOINT depends 
on the statements within the savepoint  * If the savepoint contains DDL on which a cursor is dependent, the 
cursor is marked invalid. Attempts to use such a cursor results in an 
error (SQLSTATE 57007).  * Otherwise:    o If the cursor is referenced in the savepoint, the cursor remains 
open and is positioned before the next logical row of the result table. 
(A FETCH must be performed before a positioned UPDATE or DELETE 
statement is issued.)    o Otherwise, the cursor is not affected by the ROLLBACK TO 
SAVEPOINT (it remains open and positioned).

-O