Обсуждение: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?
I’ve searched in vain for an account of how "autocommit" mode actually works. (I tried the built-in search feature withinthe PG docs. And I tried Google.) It seems clear enough that turning "autocommit" mode "on" or "off" is done by using a client-env-specific command like "\set"is psql, or "SET" in ECPG (Embedded SQL in C) or "self.session.set_session(autocommit=...)" in Python. And that themode is a property of the current session. But 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 intended SQL statement)? Or is it done server-side?
Hi, On Sat, Feb 18, 2023 at 03:49:26PM -0800, Bryn Llewellyn wrote: > > But it's not clear who actually implements the opening "start transaction" > and the closing "commit" around every submitted SQL statement when autocommit > is "on". > > Is this done in client-side code (maybe implying three round trips per > intended SQL statement)? Or is it done server-side? It's always done on the client side, postgres itself doesn't know about this feature.
Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?
От
Christophe Pettus
Дата:
> On Feb 18, 2023, at 15:49, Bryn Llewellyn <bryn@yugabyte.com> wrote: > > Or is it done server-side? It's done server-side. Note that what really happens is that, when a statement begins execution and there is no open transaction,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.
Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?
От
Christophe Pettus
Дата:
> On Feb 18, 2023, at 15:49, Bryn Llewellyn <bryn@yugabyte.com> wrote: > > I’ve searched in vain for an account of how "autocommit" mode actually works. I realize now I may have misinterpreted your question... apologies if so! If you mean the BEGIN and COMMIT statement thatsome client libraries insert into the stream when autocommit is off, that's the client, not PostgreSQL. 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.
Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?
От
"David G. Johnston"
Дата:
On Sat, Feb 18, 2023 at 4:49 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:
And that the mode is a property of the current session.
To rephrase the other responses, the client-defined setting has no inherent relationship to the concept of a PostgreSQL session. How the client uses that setting is internal to the client and whatever abstraction(s) it provides the programmer.
So far as the server is concerned 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) waits for a COMMIT or ROLLBACK before ending the transaction that it implicitly started.
David J.
Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?
От
Ian Lawrence Barwick
Дата:
2023年2月19日(日) 9:51 Christophe Pettus <xof@thebuild.com>: > > > > > On Feb 18, 2023, at 15:49, Bryn Llewellyn <bryn@yugabyte.com> wrote: > > > > I’ve searched in vain for an account of how "autocommit" mode actually works. > > I realize now I may have misinterpreted your question... apologies if so! If you mean the BEGIN and COMMIT statement thatsome client libraries insert into the stream when autocommit is off, that's the client, not PostgreSQL. > > 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. Historical trivia: PostgreSQL had a (backend) "autocommit" GUC in 7.3 only, which remained as a dummy GUC until 9.5 (see: https://pgpedia.info/a/autocommit.html ). Regards Ian Barwick
Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?
От
Christophe Pettus
Дата:
> On Feb 18, 2023, at 18:52, Ian Lawrence Barwick <barwick@gmail.com> wrote: > > Historical trivia: PostgreSQL had a (backend) "autocommit" GUC in 7.3 > only, which remained as > a dummy GUC until 9.5 (see: https://pgpedia.info/a/autocommit.html ). Well, that was a pretty whacky idea. :-)
>>>>> 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).
> On Feb 20, 2023, at 11:57, Bryn Llewellyn <bryn@yugabyte.com> wrote: > 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.) This may or may not be true. If the second session currently has a transaction open in REPEATABLE READ or SERIALIZABLE mode,it *won't* see the effects of that statement, since it took its snapshot at the start of the transaction (to be technical,at the first statement in that transaction), and holds it until commit time. However, a transaction in READ COMMITTEDmode *will* see the results after the statement completes. > I can't see that a client-side "autocommit off" mode like psql supports brings me anything of value. There's general agreement on that point. https://www.cybertec-postgresql.com/en/disabling-autocommit-in-postgresql-can-damage-your-health/
Re: Thanks! Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?
От
"David G. Johnston"
Дата:
On Mon, Feb 20, 2023 at 12:57 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:
3. Chapter 55 also has a section "Multiple Statements In A Simple Query". But this feature seems to do no more semantically beyond 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 many statements in a simple query. (At least, that's what I see in the server log when I put several statements on a single line (with psql's autocommit on).
IIRC psql -c 'insert into tbl values (1); select * from tbl;' uses the simply query protocol with multiple statements.
Then, 55.2.2.1:
> When a simple Query message contains more than one SQL statement (separated by semicolons), those statements are executed as a single transaction
So in effect bundling within the Simple Query Protocol has a similar effect to wrapping all statements in a begin/commit pair.
David J.
> xof@thebuild.com wrote: > >> bryn@yugabyte.com wrote: >> >> 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.) > > This may or may not be true. If the second session currently has a transaction open in REPEATABLE READ or SERIALIZABLEmode, it *won't* see the effects of that statement, since it took its snapshot at the start of the transaction(to be technical, at the first statement in that transaction), and holds it until commit time. However, a transactionin READ COMMITTED mode *will* see the results after the statement completes. > >> I can't see that a client-side "autocommit off" mode like psql supports brings me anything of value. > > There's general agreement on that point. > > https://www.cybertec-postgresql.com/en/disabling-autocommit-in-postgresql-can-damage-your-health/ Thanks, Christophe. Yes, I sacrificed correctness for brevity. I should have stipulated that observations made from a secondconcurrent session are to be done using a singleton "select" in its own txn—i.e. outside of an explicitly started txn(whether this is started by hand or using a client's implementation of "autocommit off"). Thanks, too, for the xref tothe Cybertec post by Laurenz Albe. And thanks, David, for your separate tip about using « psql -c ». I tried it and watched the server log. Sure enough, I sawthis: 2023-02-20 12:42:44.993 PST [2540504] d0$u0@d0 LOG: 00000: statement: insert into s.t(v) values(17); insert into s.t(v)values(42); 2023-02-20 12:42:44.993 PST [2540504] d0$u0@d0 LOCATION: exec_simple_query, postgres.c:971 It seems a bit odd that psql has no syntax to ask for this in its interactive mode. But, yes, it doesn't actually matterbecause I can get the same semantics by starting a txn myself.
Bryn Llewellyn wrote: > 2023-02-20 12:42:44.993 PST [2540504] d0$u0@d0 LOG: 00000: statement: > insert into s.t(v) values(17); insert into s.t(v) values(42); > 2023-02-20 12:42:44.993 PST [2540504] d0$u0@d0 LOCATION: exec_simple_query, > postgres.c:971 > > It seems a bit odd that psql has no syntax to ask for this in its > interactive mode. Backslash-semicolon is the syntax. Quoted from the doc: <quote> \; Backslash-semicolon is not a meta-command in the same way as the preceding commands; rather, it simply causes a semicolon to be added to the query buffer without any further processing. Normally, psql will dispatch an SQL command to the server as soon as it reaches the command-ending semicolon, even if more input remains on the current line. Thus for example entering select 1; select 2; select 3; will result in the three SQL commands being individually sent to the server, with each one's results being displayed before continuing to the next command. However, a semicolon entered as \; will not trigger command processing, so that the command before it and the one after are effectively combined and sent to the server in one request. So for example select 1\; select 2\; select 3; results in sending the three SQL commands to the server in a single request, when the non-backslashed semicolon is reached. The server executes such a request as a single transaction, unless there are explicit BEGIN/COMMIT commands included in the string to divide it into multiple transactions. (See Section 55.2.2.1 for more details about how the server handles multi-query strings.) </quote> Best regards, -- Daniel Vérité https://postgresql.verite.pro/ Twitter: @DanielVerite
>> bryn@yugabyte.com wrote: >> >> It seems a bit odd that psql has no syntax to ask for this in its interactive mode. > > daniel@manitou-mail.org wrote: > > Backslash-semicolon is the syntax. Thanks, Daniel. Yes, that works. And the server’s SQL statement log confirms this. I’ve no idea how I might have found this without human help. (Neither generic Google search nor using the PG docs own searchgot me anywhere.) But now I know that what I asked about is possible and I’ve seen it work, I trust that I’ll rememberthe trick.
> On Feb 20, 2023, at 17:54, Bryn Llewellyn <bryn@yugabyte.com> wrote: > > > I’ve no idea how I might have found this without human help. That sounds like an excellent documentation patch!
>> bryn@yugabyte.com wrote: >> >> I’ve no idea how I might have found this without human help. > > xof@thebuild.com wrote: > > That sounds like an excellent documentation patch! Well, it’s already documented clearly enough. The question is how to find it—especially if you don’t know that the featurethat you’re looking for exists or not. The psql doc would print out at about thirty pages with a normal font size.So reading it from top to bottom would be quite a task.
> >> bryn@yugabyte.com wrote: > >> > >> I’ve no idea how I might have found this without human help. > > > > xof@thebuild.com wrote: > > > > That sounds like an excellent documentation patch! > > Well, it’s already documented clearly enough. The question is how to find it—especially if you don’t know that the featurethat you’re looking > for exists or not.The psql doc would print out at about thirty pages with a normal font size. So reading it from top tobottom would be quite a task. But, then, documentation is there to be read. And it tends to be the longer the more details it is expected to cover, isn't it ? Searching for generic terms on typical search engines can be quite a task, agreed. Karsten