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

On Wed, Aug 7, 2019 at 12:19 AM Bryn Llewellyn <bryn(at)yugabyte(dot)com> wrote:
> 1. my call p2() starts a txn.

In my opinion this is the point: a procedure must be "owner" of the
transaction to issue transaction control statements. You can watch
different behaviors placing here and there txid_current() before and
within p2 and re-run with autocommit off an on.
On autocmmmit = on the call opens a transaction and the procedure is
the only thing within the transaction, so it has full control. With
autocommit off you gain an implicit begin and the procedure is
possibly not the only thing you have within the transaction.
You can test it with:

testdb=# begin;
BEGIN
testdb=# call p2();
INFO: TXID 1300004994
ERROR: invalid transaction termination
CONTEXT: PL/pgSQL function p2() line 9 at ROLLBACK
testdb=# rollback;
ROLLBACK

Having said that, I believe there is no reason ever to begin a
procedure with a rollback.

Here's a small example that performs a few commits and rollback
<https://github.com/fluca1978/PostgreSQL-11-Quick-Start-Guide/blob/master/Chapter04/Chapter04_Listing29.sql>

Luca

P.S:
I don't believe that asking on a public mailing list for a company to
answer is the right thing to do, you can always ask themselves on your
own.