Date: Sat, 5 Oct 2024 22:33:27 +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: <20241005203327.nb52nfuopdsjilvd@hjp.at>
Mail-Followup-To: pgsql-general@lists.postgresql.org
References: <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>
 <20240925215554.gfg24h5sp5aqesxv@hjp.at>
 <152525.1727302184@sss.pgh.pa.us>
 <20241005091424.34il2ss4noazgegx@hjp.at>
 <368259fb-fd2e-4a05-89e9-a733fae6d964@aklaver.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="u2usd6rv3dbagud4"
Content-Disposition: inline
In-Reply-To: <368259fb-fd2e-4a05-89e9-a733fae6d964@aklaver.com>
Archived-At: <https://www.postgresql.org/message-id/20241005203327.nb52nfuopdsjilvd%40hjp.at>
Precedence: bulk


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

On 2024-10-05 09:59:00 -0700, Adrian Klaver wrote:
> On 10/5/24 02:14, Peter J. Holzer wrote:
> > On 2024-09-25 18:09:44 -0400, Tom Lane wrote:
> > > "Peter J. Holzer" <hjp-pgsql@hjp.at> writes:
> > > Admittedly, that would normally not be a very long interval if BEGIN
> > > did both things ... but on a busy system you could lose the CPU for
> > > awhile in between.
> >=20
> > Assuming that the system does have a global clock of sufficiently
> > fine resolution which returns strictly monotonically increasing
> > timestamps[1], I think the following is true:
> >=20
> > Every snapshot divides the set of transactions into two non-overlapping
> > subsets: Those which have committed early enough that their effects are
> > visible in the snapshot and those which haven't. Let's call the first s=
et
> > the "earlier" transactions and the second the "later" transactions. Let=
's
> > call the current transaction c and any transaction in the earlier set e
> > (we ignore the later transactions for now).
> >=20
> > Performing a commit and taking a snapshot take some time, but there
> > should be a time t_C(e) in each commit and t_S(c) in the snapshot, such
> > that t_C(e) < t_S(c) for each "earlier" transaction.
>=20
> Assuming t_C is time of commit and t_S is time of snapshot, is the
> above not the crux of the matter? Namely when in the current
> transaction the snapshot is actually taken. That would determine what
> constitutes an earlier visible transaction relative to the current
> transaction. In other words I am not seeing how this changes anything?

The important part is in the last paragraph:

> > If we choose the transaction_timestamp to be >=3D t_S, then
> > transaction_timestamp(e) < t_C(e) < t_S(c) <=3D transaction_timestamp(c)
> > and therefore
> > transaction_timestamp(e) < transaction_timestamp(c)

In PostgreSQL, transaction_timestamp is taken during BEGIN (as Greg
noted). If it was instead taken at the end of the snapshot, it would be
guaranteed to be later than any transaction_timestamp of an earlier
transaction.

Again, I'm not arguing for such a change, but I'm wondering if recording
transaction_timestamp just after the snapshot might be a safe change or
whether that might break some assumption that programmers can currently
make.

        hp

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

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

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

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmcBopEACgkQ8g5IURL+
KF22qA//Zifi/fx8M2OUBno8kcbQ+KwUATbt9ySh1DpF9ao2Zaomh/1+UUDTdIVo
27SvUi3CDFbiCg3c2Hv/1MvsW/1+klAAtMbYOSevgwO26kMAbLzFJXwuugZcYMkD
XKS61ODqUiOz3pmA8FgjfafzLSC0dj1ehsTk8BzhGQj+LheXyeIOeAIxCrHQP9bR
/KB/wVJX/9OEPZEkZoAe3jqd0pGE52sFkj0t9IhwdewksKICKU7DmvKJEVw5g6rC
CDpIvCbPPue+5QYE+EecX9RSTY3xdFEyek8P1B4wo8PQId8I32SLWtYMJ8JTff05
Dusj86T4ottPA8zav4elnT1hKvD1oW95yoFCEavKMNm+Cv48tngLpI9iBSDjX8vA
Z6fWOlFV5WfCMsxwXdMtzRDk+/zMo6Gw6sTQQrcBcsJo9neYpxKi1uGa5SJRk5BD
kiGkA3ZpbvAmV3neCrXVQ68bSDwTvt7GjlZd4ca3LTjNdwF9Z4iA1TTJHVywc3B0
Vhzr6IdachSv2Za/TzWKGb/yCMD/7P1MMwmFvqa5ouPNx7kNcwrRMTSUd4D0X1eh
gce1c8dU/QVtIq20z2Jqz+xhcBxVJXfNGrDqXwmEZ/sRyOYPZ1Ed+7Wv3j3rCBdk
OfqS19+3+erh+RpGLBb5URvQA//Qbpp16Dzj5Obt0JrZrwTl2s4=
=TdvJ
-----END PGP SIGNATURE-----

--u2usd6rv3dbagud4--