Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Thanks David. I’m relieved to hear that I’m not the only one who finds it hard to predict the behavior of some things in PostgreSQL just from reading the docs. Hypothesising and experimenting seem to be needed for the trickier cases.

You use the phrase "nested transaction”. This Google search gets no hits:

  "nested transaction" site:www.postgresql.org/docs/11/

And as I’ve come to understand the phrase in the wider world, "nested transaction” is synonymous with “autonomous transaction”. PostgreSQL 11.2 doesn’t support these. But I’ve heard that some future release might. So, as I see it, a single session can do only one transaction after another with no overlapping. My experiments using txid_current()—mentioned elsewhere in the offshoots from my original post—are consistent with this hypothesis.

On 07-Aug-2019, at 13:40, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:

On Wed, Aug 7, 2019 at 12:18 PM Luca Ferrari <fluca1978@xxxxxxxxx> wrote:
On Wed, Aug 7, 2019 at 8:28 PM Bryn Llewellyn <bryn@xxxxxxxxxxxx> 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.




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux