Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1st89I-00DndL-E1 for pgsql-general@arkaria.postgresql.org; Tue, 24 Sep 2024 16:12:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1st89H-00A2Qj-R9 for pgsql-general@arkaria.postgresql.org; Tue, 24 Sep 2024 16:12:23 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1st89H-00A2QW-Ef for pgsql-general@lists.postgresql.org; Tue, 24 Sep 2024 16:12:23 +0000 Received: from mail-ot1-x336.google.com ([2607:f8b0:4864:20::336]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1st89D-000rry-2R for pgsql-general@lists.postgresql.org; Tue, 24 Sep 2024 16:12:22 +0000 Received: by mail-ot1-x336.google.com with SMTP id 46e09a7af769-709346604a7so2771431a34.1 for ; Tue, 24 Sep 2024 09:12:20 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1727194338; x=1727799138; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=iaTCi5lekrFddCfXAyRJq+hEl7jCTeBQav8NMKdpHw0=; b=DaDIbwCk3CNnJDXnaVObXVb9K4ztZdirP/jxIi/4XiGTult74Q2/bTj/JmCbCMhEim Y88GyAhbaHbT8TYOaaLYwYhCJMFVPW0d4HOmDM+kcyX9TXbbIxNkPiLWMP7BMBLpBX8I crDoDOFuSd8qPntlI10l+2TR0xfx6Hl3MVt3d1mx0qBLdkSsDxikl+Or4osVL08Nceg4 HZKOdiDzPMA+OfhpiZqRvhU5rcdCY6WfjlJWM4pqTk+PEmqHZc1bSwL0wpLwaXqz4tEp 6HKbEBeHzWQqOsP13/0LTz8rMu31cE9HOkF7Ec6BK5hliNZeojd9A2COMxYJVud1VQ9T c2fg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1727194338; x=1727799138; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=iaTCi5lekrFddCfXAyRJq+hEl7jCTeBQav8NMKdpHw0=; b=o1Tf0L5GY2LEOrP902hBuznPp4BLbb1FKJenlTsYgcSCY/IdBOgNy/bCq3176+w7NL C03Ip5xYDjhydiYhFV94JwALUDbs1g2gTkoqe/ATnzbASkD5J+/GvB6cLsvhO4KUVp7r owj/4tlqJtXP1c4SG6K/hwWsmgKTvyU4wJ0zIwTkfkYc+gSsbL3JPlYezSCxa3nOlAq4 HaS8cF9ua2lNi5WoccYjGW5gPULeKNBLc2tmrAG8p/uDjULwS1LWSY0lhJLg09ivhQo7 shEdKSHQ21hVLpKmQK2NiKFVQgPA/JUGyeYf5IOQF+rc/SpfHpZESJKYVrXZRPgBgmE0 ISig== X-Gm-Message-State: AOJu0YyMVI1PRohGilgVLQUqjXs8cjut5EPMGq6/AkOu93q9RCMA4TAn uAGXQQy3xIcDuG9Ovg4yy3tJYYhsUzVAsB78lzFYtfMsGoXiKDYGGggb+RR4R2ohU1rtciUmX1D FJ13FXcMMXmgEhoUz3KXKfSAue0J0gw== X-Google-Smtp-Source: AGHT+IHtNPeeNSZAu+zeIgVK6wzUml90nj8rQMAD0M9Gv23oEyP7MTzi58pKOp8asALAlQoT6+IiWIHhpwOpZ4xZMpk= X-Received: by 2002:a05:6830:6703:b0:710:e5fc:27d0 with SMTP id 46e09a7af769-713923d0b13mr12406500a34.13.1727194338235; Tue, 24 Sep 2024 09:12:18 -0700 (PDT) MIME-Version: 1.0 References: <9CEBFAC7-4372-4FF0-8124-FFFE834B03C6@gmail.com> <0d074fba-3cd1-466e-96f3-d9b2c9c45774@aklaver.com> In-Reply-To: <0d074fba-3cd1-466e-96f3-d9b2c9c45774@aklaver.com> From: Ron Johnson Date: Tue, 24 Sep 2024 12:12:07 -0400 Message-ID: Subject: Re: Repeatable Read Isolation Level "transaction start time" To: "pgsql-general@lists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000fd22da0622dfc4d8" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000fd22da0622dfc4d8 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, Sep 24, 2024 at 12:06=E2=80=AFPM Adrian Klaver wrote: > On 9/24/24 05:59, Ron Johnson wrote: > > On Tue, Sep 24, 2024 at 8:29=E2=80=AFAM David G. Johnston > > > wrote: > > > > On Monday, September 23, 2024, Wizard Brony > > wrote: > > > > > https://www.postgresql.org/docs/16/transaction-iso.html#XACT-REPEATABLE-R= EAD > < > https://www.postgresql.org/docs/16/transaction-iso.html#XACT-REPEATABLE-R= EAD > > > > > > The PostgreSQL documentation for the Repeatable Read Isolation > > Level states the following: > > > > =E2=80=9CUPDATE, DELETE, MERGE, SELECT FOR UPDATE, and SELECT F= OR SHARE > > commands behave the same as SELECT in terms of searching for > > target rows: they will only find target rows that were committe= d > > as of the transaction start time.=E2=80=9D > > > > What is defined as the "transaction start time?" When I first > > read the statement, I interpreted it as the start of the > > transaction: > > > > BEGIN; > > SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; > > > > But in my testing, I find that according to that statement, the > > transaction start time is actually "the start of the first > > non-transaction-control statement in the transaction" (as > > mentioned earlier in the section). Is my conclusion correct, or > > am I misunderstanding the documentation? > > > > > > Probably, since indeed the transaction cannot start at begin becaus= e > > once it does start it cannot be modified. > > > > Huh? > > BEGIN; > SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; > > I read it as the transaction does not start at BEGIN because if it did > you could not SET TRANSACTION to change it's characteristics. > > The docs go into more detail: > > https://www.postgresql.org/docs/current/sql-set-transaction.html > > The transaction isolation level cannot be changed after the first query > or data-modification statement (SELECT, INSERT, DELETE, UPDATE, MERGE, > FETCH, or COPY) of a transaction has been executed. > > > So: > > begin ; > SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; > SET > SET TRANSACTION ISOLATION LEVEL READ COMMITTED; > SET > select * from csv_test ; > [...] > SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; > ERROR: SET TRANSACTION ISOLATION LEVEL must be called before any query > Makes sense. Never would have occurred to me to try and change the isolation level using a second SET TRANSACTION statement, though. --=20 Death to , and butter sauce. Don't boil me, I'm still alive. crustacean! --000000000000fd22da0622dfc4d8 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tue, Sep 24, 2024 at 12:06=E2=80=AFPM = Adrian Klaver <adrian.klave= r@aklaver.com> wrote:
On 9/24/24 05:59, Ron Johnson wrote= :
> On Tue, Sep 24, 2024 at 8:29=E2=80=AFAM David G. Johnston
> <da= vid.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:<= br> >
>=C2=A0 =C2=A0 =C2=A0On Monday, September 23, 2024, Wizard Brony <wizardbrony@gmail.c= om
>=C2=A0 =C2=A0 =C2=A0<mailto:wizardbrony@gmail.com>> wrote:
>
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0https://www.postgresql.org/docs/16/transaction-iso.html#XACT-= REPEATABLE-READ <= https://www.postgresql.org/docs/16/transaction-iso.html#XACT-REPEATABLE-REA= D>
>
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0The PostgreSQL documentation for the = Repeatable Read Isolation
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Level states the following:
>
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=E2=80=9CUPDATE, DELETE, MERGE, SELEC= T FOR UPDATE, and SELECT FOR SHARE
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0commands behave the same as SELECT in= terms of searching for
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0target rows: they will only find targ= et rows that were committed
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0as of the transaction start time.=E2= =80=9D
>
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0What is defined as the "transact= ion start time?" When I first
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0read the statement, I interpreted it = as the start of the
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0transaction:
>
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0BEGIN;
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0SET TRANSACTION ISOLATION LEVEL REPEA= TABLE READ;
>
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0But in my testing, I find that accord= ing to that statement, the
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0transaction start time is actually &q= uot;the start of the first
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0non-transaction-control statement in = the transaction" (as
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0mentioned earlier in the section). Is= my conclusion correct, or
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0am I misunderstanding the documentati= on?
>
>
>=C2=A0 =C2=A0 =C2=A0Probably, since indeed the transaction cannot start= at begin because
>=C2=A0 =C2=A0 =C2=A0once it does start it cannot be modified.
>
> Huh?

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

I read it as the transaction does not start at BEGIN because if it did
you could not SET TRANSACTION to change it's characteristics.

The docs go into more detail:

https://www.postgresql.org/docs/curr= ent/sql-set-transaction.html

The transaction isolation level cannot be changed after the first query or data-modification statement (SELECT, INSERT, DELETE, UPDATE, MERGE,
FETCH, or COPY) of a transaction has been executed.


So:

begin ;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET
select * from csv_test ;
[...]
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
ERROR:=C2=A0 SET TRANSACTION ISOLATION LEVEL must be called before any quer= y

Makes sense.=C2=A0 Never would = have occurred to me to try and change the isolation level using a second SE= T TRANSACTION statement, though.

--
<= div dir=3D"ltr">Death to <Redacted>, and butter sauce.
Don't = boil me, I'm still alive.
<Redacted> crustacean!
--000000000000fd22da0622dfc4d8--