Re: committing inside cursor loop

On 20 February 2018 at 14:45, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> writes:
>> 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.
>
> I haven't really read this patch, but this bit jumped out at me:
>
> + Inside a cursor loop, <command>ROLLBACK</command> is not allowed. (That
> + would have to roll back the cursor query, thus invalidating the loop.)

Hmm, why?

Rollback would only invalidate the cursor if it hadn't yet hit a
Commit. But if you did a commit, then the cursor would become holdable
and you could happily continue reading through the loop even after the
rollback.

So if Commit changes a pinned portal into a holdable cursor, we just
make Rollback do that also. Obviously only for pinned portals, i.e.
the query/ies whose results we are currently looping through.

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