From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Bryn Llewellyn <bryn(at)yugabyte(dot)com>, Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)com> |
Cc: | pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure? |
Date: | 2019-08-07 18:56:07 |
Message-ID: | a94455b1-0e11-5a46-52f9-9114895846e8@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 8/7/19 11:46 AM, Bryn Llewellyn wrote:
> Thanks for your response, Kyotaro. I’m happy, now, to accept the rule
> that “call proc_that_does_txn_control()” is legal only when AUTOCOMMIT
> is ON. Esp. when I’m told (on twitter, by 2ndQuadrant’s Peter
> Eisentraut, that this rule is “an implementation restriction, for the
> most part.” See HERE
> <https://twitter.com/petereisentraut/status/1158802910865756160>.
>
> About your “In-procedure transaction control premises that no
> transaction is active before calling the procedure”… yes. Nevertheless,
> as the code that Umair Sahid showed us in the blog post that I
> referenced in my email that started this thread, you can indeed start
> end end transactions from an executing proc (as long as the session’s
> AUTOCOMMIT mode s ON).
>
The key is that the AUTOCOMMIT status is just a specific case of the
general rule. The general rule being that a PROCEDURE cannot do
transaction ending commands when it it called within an outer
transaction. You can run into the same issue in other situations e.g.
ORM's that start a transaction behind the scenes. In other words this is
not psql specific. As long as you understand the general rule then
things become clearer.
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | stan | 2019-08-07 18:57:08 | Recomended front ends? |
Previous Message | Bryn Llewellyn | 2019-08-07 18:46:20 | Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure? |