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

On Wed, Aug 7, 2019 at 12:18 PM Luca Ferrari <fluca1978(at)gmail(dot)com> wrote:

> On Wed, Aug 7, 2019 at 8:28 PM Bryn Llewellyn <bryn(at)yugabyte(dot)com> wrote:
>
> > B.t.w., I noticed that “set transaction isolation level serializable”
> must be the very first statement after “rollback” (or “commit”). Even an
> invocation of txid_current() after the rollback and before the ““set
> transaction” causes this runtime error: “SET TRANSACTION ISOLATION LEVEL
> must be called before any query”.
> >
>
> Well, SET TRANSACTION ISOLATION must be the very first instruction of
> every transaction, not only within the case you describe.
>

IMHO, The documentation, probably in chapter 13, could use some exposition
on this topic.

What is being described here is basically:

[implicit] BEGIN (using default transaction options)
CALL
>>>>BEGIN (with inherited default transactions options)
>>>>ROLLBACK (to get rid of the nested transaction setup by the call with
the inherited default options)
>>>>START WITH OPTIONS
>>>>COMMIT;
<<<<CALL END
[implicit] COMMIT

As far as psql is concerned there is only one statement and once its
executed psql issues the implicit commit to match the implicit begin is
sent previously.

It should be better documented which combinations of outer and inner
transaction commands are considered valid and which are not. WIth
examples. The current scattering of words leaves the user to perform
trial-and-error, just as the OP has, to determine what is allowed.

The nested transaction seems like it has to be correct since otherwise the
rollback as a first statement would attempt to rollback the transaction the
call itself is executing within...

Note I only have access to v10 at the moment so I haven't tried my own
experiments. To my main point I shouldn't have to - the expected behavior
should be something I could directly interpret from the documentation and
in my admitted brief attempt I could not do so.

David J.