On Wed, Aug 7, 2019 at 8:28 PM Bryn Llewellyn <bryn@yugabyte.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.