Re: [HACKERS] Transaction control in procedures

On 12/1/17 15:28, Robert Haas wrote:
> This feature doesn't have many tests. I think it should have a lot
> more of them. It's tinkering with the transaction control machinery
> of the system in a fairly fundamental way, and that could break
> things.

Thank you, these are great ideas.

> I suggest, in particular, testing how it interactions with resources
> such as cursors and prepared statements. For example, what happens if
> you commit or roll back inside a cursor-for loop (given that the
> cursor is not holdable)?

This was discussed briefly earlier in the thread. The mid-term fix is
to convert pinned cursors to holdable ones before a COMMIT in PL/pgSQL
and then clean them up separately later. I have that mostly working,
but I'd like to hold it for a separate patch submission. The short-term
fix is to prohibit COMMIT and ROLLBACK while a portal is pinned.

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 or ROLLBACK inside a PLpgsql block with an attached EXCEPTION
> block, or when an SQL SAVEPOINT has been established previously.

I think that needs to be prohibited because if you end transactions in
an exception-handled block, you can no longer actually roll back that
block when an exception occurs, which was the entire point.

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

> - COMMIT or ROLLBACK with open large objects.

I haven't been able to reproduce any problems with that, but maybe I
haven't tried hard enough.

> - COMMIT inside a procedure fails because of a serialization failure,
> deferred constraint, etc.

That works fine. The COMMIT fails and control exits the procedure using
the normal exception propagation.

I'll submit an updated patch with some fixes for the above and more
documentation.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services