MIME-Version: 1.0
References: <9CEBFAC7-4372-4FF0-8124-FFFE834B03C6@gmail.com>
 <3346993.1727188126@sss.pgh.pa.us> <CAKAnmmLt2ezcf7-LgJkO_oeqK+Ei+XB6F3pHk2mnyadRq3corQ@mail.gmail.com>
 <e9dba83f-0fba-4113-a13a-58aa2a98f092@aklaver.com> <CANzqJaBjwtzLg_bZHkokUJbtA8gy6RpHjirfdBH9OJGfn5=tmw@mail.gmail.com>
 <28109.1727286817@sss.pgh.pa.us> <CAKAnmmLDpwgqu071t_Q7Fq349dYAgQim1x46U1DCAiiTcUmU2A@mail.gmail.com>
 <CANzqJaAAQFD_JvK=ChmfdGCRNP0G5gMtVBgF52bZiFjQxH8N_Q@mail.gmail.com>
 <CAKAnmmL05Lt89EOnMW111qdUOau+dYG7u8ff5o+ckKKohnNwGg@mail.gmail.com> <DD2C10F3-2A3C-44AB-AAC5-10BB15B15498@thebuild.com>
In-Reply-To: <DD2C10F3-2A3C-44AB-AAC5-10BB15B15498@thebuild.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Wed, 25 Sep 2024 17:50:06 -0400
Message-ID: <CAKAnmm+pudxS+6P8QWH-hf9sUcUN8LuY2tQpc+8_-Gnwi2WNRQ@mail.gmail.com>
Subject: Re: Repeatable Read Isolation Level "transaction start time"
To: Christophe Pettus <xof@thebuild.com>
Cc: Ron Johnson <ronljohnsonjr@gmail.com>, 
	"pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000001866890622f89d41"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmm%2BpudxS%2B6P8QWH-hf9sUcUN8LuY2tQpc%2B8_-Gnwi2WNRQ%40mail.gmail.com>
Precedence: bulk

--0000000000001866890622f89d41
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Wed, Sep 25, 2024 at 4:54=E2=80=AFPM Christophe Pettus <xof@thebuild.com=
> wrote:

> On Sep 25, 2024, at 13:49, Greg Sabino Mullane <htamfids@gmail.com> wrote=
:
> > BEGIN   ISOLATION MODE REPEATABLE READ   SNAPSHOT NOW;
>
> This might well be a failure of imagination on my part, but when would it
> pragmatically matter that the snapshot is taken at the first statement as
> opposed to at BEGIN?


I could imagine lots of cases where you know something is about to happen
(say, a major delete), and you want to get a snapshot of the database as it
existed just before that point. Many people will (quite understandably)
assume that a BEGIN ISOLATION MODE <non read committed>; command would do
just that, and be quite surprised to find that when they actually query the
table in that first process, the rows are not there.

It's certainly a non-intuitive behavior. I understand why we do it this
way, but perhaps this warrants a stronger warning in the docs at least?
It's too late in the day for me to tackle that now, but I'll throw it out
there.

Cheers,
Greg

--0000000000001866890622f89d41
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Wed, Sep 25, 2024 at 4:54=E2=80=AFPM C=
hristophe Pettus &lt;<a href=3D"mailto:xof@thebuild.com">xof@thebuild.com</=
a>&gt; wrote:</div><div dir=3D"ltr"><br></div><div class=3D"gmail_quote"><b=
lockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-le=
ft:1px solid rgb(204,204,204);padding-left:1ex">
&gt; On Sep 25, 2024, at 13:49, Greg Sabino Mullane &lt;<a href=3D"mailto:h=
tamfids@gmail.com" target=3D"_blank">htamfids@gmail.com</a>&gt; wrote:<br>
&gt; BEGIN=C2=A0 =C2=A0ISOLATION MODE REPEATABLE READ=C2=A0 =C2=A0SNAPSHOT =
NOW;<br>
<br>
This might well be a failure of imagination on my part, but when would it p=
ragmatically matter that the snapshot is taken at the first statement as op=
posed to at BEGIN?</blockquote><div><br></div><div>I could imagine lots of =
cases where you know something is about to happen (say, a major delete), an=
d you want to get a snapshot of the database as it existed just before that=
 point. Many people will (quite understandably) assume that a BEGIN ISOLATI=
ON MODE &lt;non read committed&gt;; command would do just that, and be quit=
e surprised to find that when they actually query the table in that first p=
rocess, the rows are not there.</div><div><br></div><div>It&#39;s certainly=
 a non-intuitive behavior. I understand why we do it this way, but perhaps =
this warrants a stronger warning in the docs at least? It&#39;s too late in=
 the day for me to tackle that now, but I&#39;ll throw it out there.</div><=
div><br></div><div>Cheers,</div><div>Greg</div><div><br></div></div></div>

--0000000000001866890622f89d41--