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.g.johns...@gmail.com> wrote: > On Wed, Aug 7, 2019 at 12:18 PM Luca Ferrari <fluca1...@gmail.com> wrote: > >> On Wed, Aug 7, 2019 at 8:28 PM Bryn Llewellyn <b...@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. > > >
