Thanks! Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode? - Mailing list pgsql-general

>>>>> bryn@yugabyte.com wrote:
>>>>>
>>>>> ...it's not clear who actually implements the opening "start transaction" and the closing "commit" around every
submittedSQL statement when autocommit is "on". Is this done in client-side code (maybe implying three round trips per
intendedSQL statement)? Or is it done server-side? 
>>>>
>>>> rjuju123@gmail.com wrote
>>>>
>>>> It's always done on the client side, postgres itself doesn't know about this feature.
>>>
>>> xof@thebuild.com wrote:
>>>
>>> It's done server-side.  Note that what really happens is that, when a statement begins execution and there is no
opentransaction, a snapshot is taken and then released when the statement finishes (just as happens in READ COMMITTED
mode). No piece of code literally injects a BEGIN and a COMMIT statement to make it happen. 
>>
>> xof@thebuild.com wrote:
>>
>> PostgreSQL has no idea that mode even exists: it either sees statements without transactions, which run in their own
transaction,or BEGIN / COMMIT statements.  Because client stacks have traditionally loved to provide their own
transactionsemantics, they might inject BEGIN and COMMIT statements, but that's not something PostgreSQL sees. 
>>
>> (And I have never liked the term "autocommit mode"; it really doesn't reveal much about what is going on.
>
> david.g.johnston@gmail.com wrote:
>
> To rephrase the other responses, the client-defined setting has no inherent relationship to the concept of a
PostgreSQLsession.  How the client uses that setting is internal to the client and whatever abstraction(s) it provides
theprogrammer. 
>
> So far as the server is concerned it is always auto (implicit) begin, and also auto (implicit) commit - absent
receivinga BEGIN SQL Command in which case it disables implicit commit and (more or less) waits for a COMMIT or
ROLLBACKbefore ending the transaction that it implicitly started. 

Thanks, all, for these replies. In summary, I think that it all makes sense to me now.

I've written a bit more, here, for my own benefit. Do please speak up if I got it wrong.

Christophe also said “I may have misinterpreted your question...” No worries. I didn’t have the proper vocabulary to
aska clear question. I might just as well have said “Tell me about autocommit—in all of the different domains where
there'ssomething to say. Anyway... it seems clear from the fact that nobody mentioned a doc reference that there is no
usefulaccount in the PG docs of what's been explained in this thread. Pity. 

I think that I can summarize what seems to be the consensus expressed by David's and Christophe's replies with
referenceto a thought experiment. Imagine that I studied up the material described here: 

Chapter 55. Frontend/Backend Protocol
https://www.postgresql.org/docs/current/protocol.html

and that I wrote a program using the low-level language of my choice to send and receive TCP/IP messages to/from my
PostgreSQLserver. Assume that I limit myself to so-called Simple Queries and that I don't use "prepare" and "execute". 

You've told me that, at this level of communication, there is no "autocommit" mode to set. Rather, things just behave
ina certain way, like you've described. 

1. If I send over just "insert into s.t(v) values(17)" then a second session will see the effect of my "insert"
immediately.(I don't need a trailing semicolon; but I'm allowed to have one if I like to.) Moreover, If I set «
log_statement= 'all' » in my postgresql.conf and tail the log file, all I see is my bare insert statement. I don't see
"begin"before it and "commit" after it. I conclude, therefore, that the defined semantics of sending over a single SQL
statementare to start a transaction under the covers, to execute the statement, and then to commit it under the covers.
It'stempting to say that the effect of my statement is automatically committed—or to say that PostgreSQL natively
implementsautomatic commit. But I s'pose that it's enough simply to describe what happens without inventing any
terminology.

2. If I send over "begin" and then "insert into s.t(v) values(42)", then (so far) a second session will not see the
effectof my SQL's. It sees this only when I send over "commit". (If I send over "rollback" instead of "commit", then
othersessions never know what I did.) 

3. Chapter 55 also has a section "Multiple Statements In A Simple Query". But this feature seems to do no more
semanticallybeyond implicitly achieving what I could do by surrounding several statements explicitly with "begin; ...
commit;".There is, though, the non-semantic aspect of round-trip reduction. It seems that psql doesn't expose doing
manystatements in a simple query. (At least, that's what I see in the server log when I put several statements on a
singleline (with psql's autocommit on). 

4. If I use psql and set autocommit to off, I see that it knows whether or not my session has an ongoing txn; and if it
doesn'thave one, then it sends "begin;" before whatever SQL statement I enter—leaving it then up to me to commit or
rollback.

5. I see what David means by saying « [from the server's P.o.V.] it is always auto (implicit) begin, and also auto
(implicit)commit - absent receiving a BEGIN SQL Command in which case it disables implicit commit and (more or less)
waitsfor a COMMIT or ROLLBACK before ending the transaction that it implicitly started. » 

Finally, as far as my own practice is concerned, I can't see that a client-side "autocommit off" mode like psql
supportsbrings me anything of value. If I want to execute two or more SQL's in a single txn, then I'll start of with
"begin;"(and set the isolation level that I need). Or (more likely) I'll encapsulate the statements in a pre-created
user-definedsubprogram or, on the fly, in an anonymous block (having first set the session's default isolation level).