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 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
