From: | Bryn Llewellyn <bryn(at)yugabyte(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)com>, 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 23:34:12 |
Message-ID: | 544214C2-8FF3-4900-82B8-3BCABA581827@yugabyte.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Thanks, Adrian.
On 07-Aug-2019, at 13:19, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
On 8/7/19 12:54 PM, Bryn Llewellyn wrote:
> 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?
The server responds to instructions from the client.
General rule:
https://www.postgresql.org/docs/11/sql-call.html
"If CALL is executed in a transaction block, then the called procedure cannot execute transaction control statements. Transaction control statements are only allowed if CALL is executed in its own transaction.
"
> 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?
psql:
https://doxygen.postgresql.org/bin_2psql_2common_8c.html
Starting ~ line 1355
if (transaction_status == PQTRANS_IDLE &&
!pset.autocommit &&
!command_no_begin(query))
{
results = PQexec(pset.db, "BEGIN");
if (PQresultStatus(results) != PGRES_COMMAND_OK)
...
psycopg2:
https://github.com/psycopg/psycopg2/blob/master/psycopg/connection_int.c
~line 1294
> On 07-Aug-2019, at 11:56, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2019-08-07 23:51:38 | Re: How to check if a field exists in NEW in trigger |
Previous Message | Igal @ Lucee.org | 2019-08-07 22:54:33 | Re: How to check if a field exists in NEW in trigger |