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 1pUD7K-0001Mj-QE for pgsql-general@arkaria.postgresql.org; Mon, 20 Feb 2023 20:50:34 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pUD7J-0007r2-NZ for pgsql-general@arkaria.postgresql.org; Mon, 20 Feb 2023 20:50:33 +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 1pUD7J-0007qt-Bx for pgsql-general@lists.postgresql.org; Mon, 20 Feb 2023 20:50:33 +0000 Received: from mail-pl1-x62c.google.com ([2607:f8b0:4864:20::62c]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pUD7G-0001VY-EF for pgsql-general@lists.postgresql.org; Mon, 20 Feb 2023 20:50:31 +0000 Received: by mail-pl1-x62c.google.com with SMTP id q5so2813309plh.9 for ; Mon, 20 Feb 2023 12:50:30 -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=ko+35PGh5lI80qDQqxaUheDf5E47hneLWeervURiaw0=; b=gojvf+wNm5U9j2UeZnv9vuIYdX/V++Qduh7pvcGz9DbM4sCoKXrTp4743U6c5aimS3 p3v1gy2TwwggMqcLmQ56+CxDa0EpDlEZ3ePjaKZIZc0+3CYRH6FM0E0HWFV2HAQxSqfO pZGNbtPg2s/3jOHPgadV8GD5VNPiDA8yIJJDs= 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=ko+35PGh5lI80qDQqxaUheDf5E47hneLWeervURiaw0=; b=Jgx+TvF8TMk+dmAFEWxXk7w10GCObYdXZDN3RG1iwBN5SjQoh1QxFp+S4+I99lYizF npD1Cm64Hnj1VBMYHoQdkRvBFCD/2Yog9UOyV57Y8x6wCy+l6W1pvIDV0tmfDHYxVmPm 5uhrH+o/9jynDjiU+wOuqrJopjfwL3OfiuUNHUZ30ayz70rcEzqiQJ3OvfBy4KCafxRo XeQ7+6oG64rF8lQ3nWhjkUDYLMZTk22iRJd8PuP2AwdydrBKBGQGfnKokVRzHd+Qp//F mlknXyC1bE55TtvEJqk5Lejf9DsSljos/o8SSLeZ8gv/iJLbP4FcLugaxl2EKFHpdyVT adew== X-Gm-Message-State: AO0yUKU+rEnan1dxedOBSFRP9K7K1jRlEtXV5Q7mNYthH6ohfopVkOhG 38LCUPwHAcBI+0u9y1bklQdA5Q== X-Google-Smtp-Source: AK7set/nT/L/p4AA6KXfEQ3gWDqF2iW+TM9HLPnYhk86wDDL9eJ9jxPeeZefP6y+UgGjyQICb6+Sdg== X-Received: by 2002:a05:6a20:7f8c:b0:bf:58d1:ce89 with SMTP id d12-20020a056a207f8c00b000bf58d1ce89mr1864934pzj.8.1676926229239; Mon, 20 Feb 2023 12:50:29 -0800 (PST) Received: from smtpclient.apple ([2601:646:101:7fda:bcf3:4f51:60cd:fa2a]) by smtp.gmail.com with ESMTPSA id w21-20020a639355000000b004dff15fc121sm1674109pgm.36.2023.02.20.12.50.28 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Mon, 20 Feb 2023 12:50:28 -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: Re: Thanks! Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode? From: Bryn Llewellyn In-Reply-To: <931EFF56-1B39-4EA9-B9C1-3D3E2315401B@thebuild.com> Date: Mon, 20 Feb 2023 12:50:28 -0800 Cc: rjuju123@gmail.com, pgsql-general list Content-Transfer-Encoding: quoted-printable Message-Id: <3562781D-2180-492F-AF52-9AAA28514FFD@yugabyte.com> References: <954ABE4C-C17B-4269-ACAD-DE47E696515E@yugabyte.com> <931EFF56-1B39-4EA9-B9C1-3D3E2315401B@thebuild.com> To: Christophe Pettus , "David G. Johnston" 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 > xof@thebuild.com wrote: >=20 >> bryn@yugabyte.com wrote: >>=20 >> 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.) >=20 > 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 COMMITTED mode *will* see the results after the statement = completes. >=20 >> I can't see that a client-side "autocommit off" mode like psql = supports brings me anything of value. >=20 > There's general agreement on that point. >=20 > = 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 second concurrent session = are to be done using a singleton "select" in its own txn=E2=80=94i.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 to the Cybertec post by Laurenz Albe. And thanks, David, for your separate tip about using =C2=AB psql -c =C2=BB= . I tried it and watched the server log. Sure enough, I saw this: 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 matter because I can get = the same semantics by starting a txn myself.=