Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

[Bryn Llewellyn <bryn@xxxxxxxxxxxx>]

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.

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.ntt@xxxxxxxxx> wrote:

Hello, Bryn.

At Tue, 6 Aug 2019 15:18:51 -0700, Bryn Llewellyn <bryn@xxxxxxxxxxxx> wrote in <EE6D19C1-1CA6-424B-91AC-63A1A64A5921@xxxxxxxxxxxx>

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