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

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.

If we don't restore bufmgr state at subxact commit, I think that it
would work to do

begin;
begin;
declare cursor c ...;
end; -- cursor, bufmgr state NOT changed here
fetch from c;
...

It seems though that we might have a lot of problems with figuring out
which subsystems ought to restore state at subxact commit and which not.

Another point is that this will NOT work:

begin;
begin;
declare cursor c ...;
end; -- cursor, bufmgr state NOT changed here

begin;
fetch from c;
abort; -- oops, wrong state restored here

so the rule would have to be something like "cursors can only be
touched by the highest subxact nesting level they have ever been
visible to". Yuck.

regards, tom lane