Re: committing inside cursor loop

On 20 February 2018 at 14:11, Peter Eisentraut
<peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
> Here is a patch that allows COMMIT inside cursor loops in PL/pgSQL. As
> alluded to in earlier threads, this is done by converting such cursors
> to holdable automatically. A special flag "auto-held" marks such
> cursors, so we know to clean them up on exceptions.
>
> This is currently only for PL/pgSQL, but the same technique could be
> applied easily to other PLs.

Amazingly clean, looks great.

I notice that PersistHoldablePortal() does ExecutorRewind().

In most cases, the cursor loop doesn't ever rewind. So it would be
good if we could pass a parameter that skips the rewind since it will
never be needed and causes a performance hit. What I imagine is we can
just persist the as-yet unfetched portion of the cursor from the
current row onwards, rather than rewind and store the whole cursor
result.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services