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

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


-- 
Adrian Klaver
adrian.klaver@aklaver.com