Thanks for your response, Kyotaro. I’m happy, now, to accept the rule that “call proc_that_does_txn_control()” is legal only when AUTOCOMMIT is ON. Esp. when I’m told (on twitter, by 2ndQuadrant’s Peter Eisentraut, that this rule is “an implementation restriction, for the most part.” See HERE <https://twitter.com/petereisentraut/status/1158802910865756160>.
About your “In-procedure transaction control premises that no transaction is active before calling the procedure”… yes. Nevertheless, as the code that Umair Sahid showed us in the blog post that I referenced in my email that started this thread, you can indeed start end end transactions from an executing proc (as long as the session’s AUTOCOMMIT mode s ON). So, logic tells me that once a txn is ended by issuing “commit” or “rollback”, you should be allowed to start the next one explicitly with “start transaction”. However, as mentioned, this causes a runtime error. I’ve decided simply not to care because I’ve discovered how to write my proc so that it passes the functionality tests that it ought to. I have to rely on the fact the the statements I’m interested in doing (including setting the isolation level) all implicitly start a txn and so “start transaction” isn’t needed! Thanks to all who responded. The synthesis of what you all wrote helped me enormously. Case closed. On 07-Aug-2019, at 00:26, Kyotaro Horiguchi <horikyota....@gmail.com> wrote: Hello, Bryn. At Tue, 6 Aug 2019 15:18:51 -0700, Bryn Llewellyn <b...@yugabyte.com> wrote in <ee6d19c1-1ca6-424b-91ac-63a1a64a5...@yugabyte.com> > Here’s how I’ve tried to describe what I see for p2() with AUTOCOMMIT ON for > myself: > > 1. my call p2() starts a txn. > > 2. However, during the execution of the proc, the usual autocommit behavior > is programmatically turned off by explicit PostgreSQL code. > > 3. Other explicit PostgreSQL code makes “start transaction” inside a proc > simply cause a runtime error under all circumstances. However, txns can be > ended by “commit” or “rollback”. And new ones can be started—but only > implicitly by executing a SQL statement that, as a top level SQL, would start > a txn. In-procedure transaction control premises that no transaction is active before calling the procedure. https://www.postgresql.org/docs/11/sql-call.html > If CALL is executed in a transaction block, then the called > procedure cannot execute transaction control > statements. Transaction control statements are only allowed if > CALL is executed in its own transaction. With AUTOCOMMIT=off, implicit BEGIN is invoked just before CALLing p2() if no transaction is active. Thus p2() is always called having a transaction active, which inhibits in-procedure transaction control. I'm not sure why you want to turn AUTOCOMNIT off, but even with AUTOCOMMIT on, explict BEGIN prevents per-command COMMIT as you perhaps know. https://www.postgresql.org/docs/11/app-psql.html > When on (the default), each SQL command is automatically > committed upon successful completion. To postpone commit in > this mode, you must enter a BEGIN or START TRANSACTION SQL > command. regards. -- Kyotaro Horiguchi NTT Open Source Software Center
