Alvaro Herrera <[EMAIL PROTECTED]> 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
---------------------------(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
