Re: Why must AUTOCOMMIT be ON to do txn control in plpgsqlprocedure? - Mailing list pgsql-general

From Kyotaro Horiguchi
Subject Re: Why must AUTOCOMMIT be ON to do txn control in plpgsqlprocedure?
Date
Msg-id 20190807.162639.263082453.horikyota.ntt@gmail.com
Whole thread Raw
In response to Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?  (Bryn Llewellyn <bryn@yugabyte.com>)
Responses Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?
List pgsql-general
Hello, Bryn.

At Tue, 6 Aug 2019 15:18:51 -0700, Bryn Llewellyn <bryn@yugabyte.com> wrote in
<EE6D19C1-1CA6-424B-91AC-63A1A64A5921@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
explicitPostgreSQL 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
executinga 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

pgsql-general by date:

Previous
From: Luca Ferrari
Date:
Subject: Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?
Next
From: Laura Smith
Date:
Subject: Re: Guidance needed on an alternative take on common prefix SQL