Thanks, Adrian. On 07-Aug-2019, at 13:19, Adrian Klaver <adrian.kla...@aklaver.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.kla...@aklaver.com > <mailto:adrian.kla...@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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> -- Adrian Klaver adrian.kla...@aklaver.com
