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

On Thu, 1 Jul 2004, 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.
>

This isn't just directly declared CURSORs, but also V3 protocol portals
which makes it very difficult for a driver to use. An individual writing
direct BEGIN, DECLARE CURSOR, and so on statements can work around the
restrictions here because they know exactly what they are doing and
exactly what statements are sent to the backend. From a driver
perspective it has no idea what the end user's intention is and
therefore cannot do things like transform a query to a cursor based
statement or even use the V3 protocol because it has no idea if the caller
is going to use a subtransaction at some point.

Kris Jurka