Re: [HACKERS] Transaction control in procedures

On Tue, Dec 5, 2017 at 1:25 PM, Peter Eisentraut
<peter(dot)eisentraut(at)2ndquadrant(dot)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.

>> - COMMIT or ROLLBACK inside a procedure with a SET clause attached,
>
> That also needs to be prohibited because of the way the GUC nesting
> currently works. It's probably possible to fix it, but it would be a
> separate effort.
>
>> and/or while SET LOCAL is in effect either at the inner or outer
>> level.
>
> That seems to work fine.

These two are related -- if you don't permit anything that makes
temporary changes to GUCs at all, like SET clauses attached to
functions, then SET LOCAL won't cause any problems. The problem is if
you do a transaction operation when something set locally is in the
stack of values, but not at the top.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company