Date: Sat, 5 Oct 2024 11:14:24 +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: <20241005091424.34il2ss4noazgegx@hjp.at>
Mail-Followup-To: pgsql-general@lists.postgresql.org
References: <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>
 <20240925215554.gfg24h5sp5aqesxv@hjp.at>
 <152525.1727302184@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="qolgmag2w464xw3n"
Content-Disposition: inline
In-Reply-To: <152525.1727302184@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/20241005091424.34il2ss4noazgegx%40hjp.at>
Precedence: bulk


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

On 2024-09-25 18:09:44 -0400, Tom Lane wrote:
> "Peter J. Holzer" <hjp-pgsql@hjp.at> writes:
> > On 2024-09-25 13:53:30 -0700, Christophe Pettus wrote:
> >> 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?
>=20
> > It may make a difference if you're comparing timestamps.
>=20
> > 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.
>=20
> > 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.
>=20
> I don't find that hugely compelling, because there's always going
> to be some skew between the time we read the clock for the timestamp
> and the time we obtain the snapshot.

I wasn't arguing for a change in PostgreSQL's behaviour, just pointing
out a scenario where that would make a user-observable difference.

> 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.

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:

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 set
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).

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.

Within each transaction each timestamp t which could be visible outside
of the transaction must have been obtained before the commit,
so t(e) < t_C(e) < t_S(c).

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=20
transaction_timestamp(e) < transaction_timestamp(c)

Such a guarantee might be useful for some applications and it's not
(IMHO) an entirely unreasonable assumption, but it's not true for
PostgreSQL. So a programmer should be aware of that.

        hp


[1] Not sure of this is true for Linux. It's certainly possible to never
    step the clock backwards (not even during leap seconds), and the
    resolution should be fine enough that you won't get two equal
    timestamps on the same core, but with multiple cores I'm not
    sure that processes running on different cores are guaranteed to get
    strictly monotonically increasing timestamps. But as they say, while
    all models are wrong, some are useful, and I think this is close
    enough to reality to be useful.

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

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

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

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmcBA2kACgkQ8g5IURL+
KF1sTw/8CwQUngxFy7gatkgia3rM6LOxvH3t6BREd9HdhSjKU8SMJ8d1jxeQ3Dth
36N3Ji65tfzTmcYZwe+Uokz95ZCzZ9y3nWiEB6DMkawjyufO+krOnlEYamhNphDW
DItb67+im2aNSbFFQOyuXo6ymo9nynqjhBRlDw8gDvCbvewhLCnGL0CdSLMltmjt
H3nnO7zURozyHs/Pf0nuuq7blQIdkzfC+s7YN6SSJi9ku7iOFxqi+sX7hOpwULvj
08RLmQ+4fLSMYlF++jvDCA+V0gencGkQHsl5kde28QUX0ZVUMORM+BO2wEe3CSOD
ffU48nfXf0KeB6qyw97jd3mcD0N7GBqoQuUdJVEYddzV2gYiiaHTMgvM0rIJ9Jhe
tLblkGHuGdIraXMOjA3zOTgBpfOHQPl0vD1oEJVseKq5W5uiqC5p4d+cIXkeXMyl
KcOhsPZqBk+33tQ6CaccuTXo5tsbIA1vv3crTCitas3OMnbKmdYE0c87SiGNTB1u
hIQL6kZwzXt1yfDdYwskjIcWxJSezhF/YDjuQz2v+zVtXj7EcGMO2ovUi4aUMKjG
jScPJviOh4ZisrNIVhAub8tK43zFvKJh8D1kHva+gZx4ZXxWyK5kKSMyMRDAR1aD
N58F0HxF26WQBXga0VDsnCmVIaSr1RwOToZxz06u0KtOsbYu38I=
=eAZG
-----END PGP SIGNATURE-----

--qolgmag2w464xw3n--