On 20 February 2018 at 14:45, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Peter Eisentraut <peter.eisentraut@2ndquadrant.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