Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pUCId-0006mU-Kv for pgsql-general@arkaria.postgresql.org; Mon, 20 Feb 2023 19:58:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pUCIb-0004wa-Mm for pgsql-general@arkaria.postgresql.org; Mon, 20 Feb 2023 19:58:09 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pUCIa-0004uV-0g for pgsql-general@lists.postgresql.org; Mon, 20 Feb 2023 19:58:09 +0000 Received: from mail-pl1-x62b.google.com ([2607:f8b0:4864:20::62b]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pUCIS-00015j-9N for pgsql-general@lists.postgresql.org; Mon, 20 Feb 2023 19:58:06 +0000 Received: by mail-pl1-x62b.google.com with SMTP id bh1so2695878plb.11 for ; Mon, 20 Feb 2023 11:57:59 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yugabyte.com; s=google; h=to:references:message-id:content-transfer-encoding:cc:date :in-reply-to:from:subject:mime-version:from:to:cc:subject:date :message-id:reply-to; bh=zWgFIeiQmTiAfwEdQwTIdazkMaEStke05oiJqZHA5Dc=; b=ZKG1XwEFIstoFG9OnM1eCJf9JJpEqGuMyRtn8etiaAVLH6LTj7OyUrrcmjfGIWM4PK JXEGIUdjKDiSYf1GlP9mCzeDcyXAHsnlhjiyW1whRS3w4sRbSzj5b2/nz6l0pHP7+r5D 0/6f9BxkZ5LAkEOSqUgJgbcoQGdOOwl2fsVuY= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=to:references:message-id:content-transfer-encoding:cc:date :in-reply-to:from:subject:mime-version:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to; bh=zWgFIeiQmTiAfwEdQwTIdazkMaEStke05oiJqZHA5Dc=; b=NuSjumNetXwdXsk42GtnLt2GqphishyYg4ekWqpA1mdm4kHqPyEqNeLnUVs5n+2lu8 n2uED0JDBSNKptuJswBBb3J13fAZCn6YJCGItdZuo9wdOPBKU7teWvEJXISF053tGpwW 3oeLlrw6UokE5kHKWJBJSqwyD+vIwjYXrVml3f7M3vbrMki//Nuss/kYee4Lp7mhGKAG Q6N4fjhnf4hYFQZr3dIcIkOVi0Qz/gsunNXx05UzKWR/BgmXPNTemLsV9wU018iOwD16 8VMjNiAVlYRs6zNz9dukyvi6l4YtHj66w02gA2JWSWd1FwEy8G2emnLLNZCH7r1guidb VI0w== X-Gm-Message-State: AO0yUKV7WTeXkqueEUfCmk81zzg71IEWCry8xFQqxBkKulwKkD6/sbgC UYqGjvaO0KcsHRoa5piMQ2g1gg== X-Google-Smtp-Source: AK7set/VFvxg5dE83OeS0Kinu1vRd4OJ5wLGNSeM1AYQdNP0AUmueCV2FjhYnPqYjDAZLmN+WlaXEQ== X-Received: by 2002:a17:903:244c:b0:198:9e29:cc4f with SMTP id l12-20020a170903244c00b001989e29cc4fmr1847115pls.43.1676923078791; Mon, 20 Feb 2023 11:57:58 -0800 (PST) Received: from smtpclient.apple ([2601:646:101:7fda:bcf3:4f51:60cd:fa2a]) by smtp.gmail.com with ESMTPSA id d5-20020a170902c18500b00198e12c499dsm1378581pld.282.2023.02.20.11.57.58 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Mon, 20 Feb 2023 11:57:58 -0800 (PST) Content-Type: text/plain; charset=utf-8 Mime-Version: 1.0 (Mac OS X Mail 14.0 \(3654.120.0.1.14\)) Subject: Thanks! Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode? From: Bryn Llewellyn In-Reply-To: Date: Mon, 20 Feb 2023 11:57:56 -0800 Cc: pgsql-general list Content-Transfer-Encoding: quoted-printable Message-Id: References: <954ABE4C-C17B-4269-ACAD-DE47E696515E@yugabyte.com> To: "David G. Johnston" , rjuju123@gmail.com, xof@thebuild.com X-Mailer: Apple Mail (2.3654.120.0.1.14) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk >>>>> bryn@yugabyte.com wrote: >>>>>=20 >>>>> ...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? >>>>=20 >>>> rjuju123@gmail.com wrote >>>>=20 >>>> It's always done on the client side, postgres itself doesn't know = about this feature. >>>=20 >>> xof@thebuild.com wrote: >>>=20 >>> 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. >>=20 >> xof@thebuild.com wrote: >>=20 >> 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 transaction semantics, they might inject = BEGIN and COMMIT statements, but that's not something PostgreSQL sees. >>=20 >> (And I have never liked the term "autocommit mode"; it really doesn't = reveal much about what is going on. >=20 > david.g.johnston@gmail.com wrote: >=20 > 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. >=20 > 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. 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 =E2=80=9CI may have misinterpreted your = question...=E2=80=9D No worries. I didn=E2=80=99t have the proper = vocabulary to ask a clear question. I might just as well have said = =E2=80=9CTell me about autocommit=E2=80=94in all of the different = domains where there's something to say. Anyway... it seems clear from = the fact that nobody mentioned a doc reference that there is no useful = account 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 reference to 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 PostgreSQL server. 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 in a 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 =C2=AB log_statement =3D 'all' =C2=BB 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 statement are to start a = transaction under the covers, to execute the statement, and then to = commit it under the covers. It's tempting to say that the effect of my = statement is automatically committed=E2=80=94or to say that PostgreSQL = natively implements automatic 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 effect of my SQL's. It sees = this only when I send over "commit". (If I send over "rollback" instead = of "commit", then other sessions 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 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). 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't have one, then = it sends "begin;" before whatever SQL statement I enter=E2=80=94leaving = it then up to me to commit or rollback. 5. I see what David means by saying =C2=AB [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) waits for a COMMIT or ROLLBACK before ending = the transaction that it implicitly started. =C2=BB Finally, as far as my own practice is concerned, I can't see that a = client-side "autocommit off" mode like psql supports brings 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-defined subprogram or, on the fly, in an anonymous block (having = first set the session's default isolation level).