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

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

Reply via email to