On 6 December 2017 at 22:34, Merlin Moncure <mmonc...@gmail.com> wrote: > On Wed, Dec 6, 2017 at 8:41 AM, Peter Eisentraut > <peter.eisentr...@2ndquadrant.com> wrote: >> On 12/5/17 13:33, Robert Haas wrote: >>> On Tue, Dec 5, 2017 at 1:25 PM, Peter Eisentraut >>> <peter.eisentr...@2ndquadrant.com> wrote: >>>> I think ROLLBACK in a cursor loop might not make sense, because the >>>> cursor query itself could have side effects, so a rollback would have to >>>> roll back the entire loop. That might need more refined analysis before >>>> it could be allowed. >>> >>> COMMIT really has the same problem; if the cursor query has side >>> effects, you can't commit those side effects piecemeal as the loop >>> executed and have things behave sanely. >> >> The first COMMIT inside the loop would commit the cursor query. This >> isn't all that different from what you'd get now if you coded this >> manually using holdable cursors or just plain client code. Clearly, you >> can create a mess if the loop body interacts with the loop expression, >> but that's already the case. >> >> But if you coded something like this yourself now and ran a ROLLBACK >> inside the loop, the holdable cursor would disappear (unless previously >> committed), so you couldn't proceed with the loop. >> >> The SQL standard for persistent stored modules explicitly prohibits >> COMMIT and ROLLBACK in cursor loop bodies. But I think people will >> eventually want it. > > The may want it, but silently promoting all cursors to held ones is > not the way to give it to them, unless we narrow it down the the > 'for-loop derived cursor' only.
I don't think we should do that automatically for all cursors, but it seems clear that we would want that iff the loop contains COMMIT or ROLLBACK. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
