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

Tom Lane wrote:
> Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)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