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

Поиск
Список
Период
Сортировка
От Bryn Llewellyn
Тема Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?
Дата
Msg-id AF47F4A2-E00B-4B0D-BA7A-B76F3CF22468@yugabyte.com
обсуждение исходный текст
Ответ на Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?  (Adrian Klaver <adrian.klaver@aklaver.com>)
Ответы Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?  (Adrian Klaver <adrian.klaver@aklaver.com>)
Список pgsql-general
Дерево обсуждения
Yes, I do believe that I understand this. But there’s no getting away from the fact that the AUTOCOMMIT mode, and what this implies, is a server-side phenomenon—at least as several PostgreSQL experts have assured me. For example, when you use client-side Python with the psycopg2 driver, then once you’ve done “my_session = psycopg2.connect(connect_str)”, you can then do “my_session.set_session(autocommit=False)”. And then everything we’ve been saying in the psql context now applies in that context—yes?

B.t.w., I’m guessing that the “begin” SQL command that you see in the log that I mentioned is actually issued by (some) clients—at least psql and Python-on-psycopg2—as an explicit call from the client. In other words, it isn’t the server that generates this. Does anyone know for sure how this works?

On 07-Aug-2019, at 11:56, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

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.klaver@aklaver.com

В списке pgsql-general по дате отправления:

Предыдущее
От: Rob Sargent
Дата:
Сообщение: Re: Recomended front ends?
Следующее
От: Adrian Klaver
Дата:
Сообщение: Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?