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

All stored procedures run in their own transaction. My guess is that when
you turn off autocommit, you are not committing something after your
transaction ends. Also, I have never seen a rollback at the start of a
proc. A pure hypothetical is that it is doing nothing or definitely not
what you think it is.

BTW, this is why you cant close a transaction within a stored procedure. It
doesnt make sense.

Thanks,
~Ben

On Wed, Aug 7, 2019, 4:41 PM David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> 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.
>
>
>