Date: Wed, 25 Sep 2024 23:55:54 +0200
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: Repeatable Read Isolation Level "transaction start time"
Message-ID: <20240925215554.gfg24h5sp5aqesxv@hjp.at>
Mail-Followup-To: pgsql-general@lists.postgresql.org
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>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="mpjhcbvh3jkk5tdw"
Content-Disposition: inline
In-Reply-To: <DD2C10F3-2A3C-44AB-AAC5-10BB15B15498@thebuild.com>
Archived-At: <https://www.postgresql.org/message-id/20240925215554.gfg24h5sp5aqesxv%40hjp.at>
Precedence: bulk


--mpjhcbvh3jkk5tdw
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2024-09-25 13:53:30 -0700, Christophe Pettus wrote:
> > On Sep 25, 2024, at 13:49, Greg Sabino Mullane <htamfids@gmail.com> wro=
te:
> > BEGIN   ISOLATION MODE REPEATABLE READ   SNAPSHOT NOW;
>=20
> 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?

It may make a difference if you're comparing timestamps.

For example, if you're using isolation level REPEATABLE READ and
(mistakenly) assume that the snapshot is taken at BEGIN, you would
expect any  transaction_timestamp() written by a different transaction
and readable by this transaction to be earlier than the
transaction_timestamp() of this transaction.

But that's wrong because the other transaction could have happened
entirely in the time between your BEGIN and the statement which actually
triggers the snapshot.

        hp

--=20
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

--mpjhcbvh3jkk5tdw
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmb0huQACgkQ8g5IURL+
KF2PQRAAoAMtY15IomsbNVrylBU9Pzxb6FlAQpFnM2P1sFjNw5Lm3hTmicTZcR2m
AcE4VDpjm0y8RHss8xA5ay9dx/DcHgAPwPONbeMJATYT8wDI8Gef9w7SjJ9nRCU9
84BehnFVjaKZOJf1II8fHRYeulm0PtFHDPgjSAbAHbLTfemqbdr0ICPVZ0nY79y1
H5HRdVqKcI5CeLNL7N3KRzscs9Px5/n2cqbK91xTpk+2zfZVcaClZjMtRJdc5X1R
Da/dfROuNnSzL4atla1Jz2VfSRAJh3Z/wrYXiJj67wdyy8XsmBQVWq7gMpd07lz3
J6r14D4HHxsaEPK8Qm05Kxxo3KNhaS7Ac4jdyoNoGWriAXiH1H66b/DsfP20xO0f
jEN7hhK2chJeoV9GVVrBTJxt8yUVwviFphVoTRCzCKKqeFpXGHaLd0aGhfTQZXji
rJ96d8+MWWVkZQBiQkIhmxF6v9Gm8p3chbfFUu280XQAGR1JHMQ6Y+e5Vs7MEE8Z
aGpvEE6smys5sYodcchWx1x/zsucMGwrPMJ+mcGVKEkAu8mUrz994ij9Enac9A6j
xFOuls2MJPQSmNGLdX7qfG4v0761t75+1Q8ahHxZL9DQfmlkS/HSqawnqs8rrU7c
Qr8Bog4yavLnB2rO4LgSLfuPi+9F4j+FjlI3/LZOjv+LGywq1RY=
=Bebd
-----END PGP SIGNATURE-----

--mpjhcbvh3jkk5tdw--