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