Date: Sun, 6 Oct 2024 00:25:55 +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: <20241005222555.wm53cmxnwfam4vtz@hjp.at>
Mail-Followup-To: pgsql-general@lists.postgresql.org
References: <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>
 <20241005203327.nb52nfuopdsjilvd@hjp.at>
 <1166698.1728162188@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="e34zgmjgxwadp5ga"
Content-Disposition: inline
In-Reply-To: <1166698.1728162188@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/20241005222555.wm53cmxnwfam4vtz%40hjp.at>
Precedence: bulk


--e34zgmjgxwadp5ga
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2024-10-05 17:03:08 -0400, Tom Lane wrote:
> "Peter J. Holzer" <hjp-pgsql@hjp.at> writes:
> > 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.
>=20
> As I mentioned upthread, we currently promise that xact_start matches
> the query_start of the transaction's first statement.  (I'm not sure
> how well that's documented, but the code goes out of its way to make
> it so, so somebody thought it was important.)

It's mentioned in=20
https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-D=
ATETIME-CURRENT

| statement_timestamp() and transaction_timestamp() return the same value d=
uring the first command of a transaction

But that seems to be wrong in practice. The first statement of a
transaction is BEGIN. So whatever command calls statement_timestamp() is
already the second command, so statement_timestamp() is later than
transaction_timestamp(). This is even true if the BEGIN and SELECT are
on the same line:

hjp=3D> begin; select transaction_timestamp(), statement_timestamp(), clock=
_timestamp(); rollback;
BEGIN
Time: 0.406 ms
=E2=95=94=E2=95=90[ RECORD 1 ]=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=A4=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=
=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=97
=E2=95=91 transaction_timestamp =E2=94=82 2024-10-05 23:55:47.356582+02 =E2=
=95=91
=E2=95=91 statement_timestamp   =E2=94=82 2024-10-05 23:55:47.357106+02 =E2=
=95=91
=E2=95=91 clock_timestamp       =E2=94=82 2024-10-05 23:55:47.357397+02 =E2=
=95=91
=E2=95=9A=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=
=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=A7=
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=
=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=9D

Time: 0.570 ms
ROLLBACK
Time: 0.285 ms

The difference are only 0.5 ms, but it's not zero.

I think the only case where transaction_timestamp() =3D statement_timestamp=
()
is outside of a transaction.


> Lastly, wouldn't postponing the collection of the timestamp like
> that break the very property you want to promise, by making other
> transactions' timestamps nontrivially later than they are now?

I don't think so. To restate the property in SQL terms:

Given one or more processes performing

    begin;
    ...
    insert into t(ts) values(transaction_timestamp();
    ...
    commit;

and one which performs

    begin;
    set transaction isolation level repeatable read ;
    ...
    select max(ts) < transaction_timestamp() from t;

that select statement returns always true.

If transaction_timestamp() returns a later timestamp, it will still be
true.


> I think if we wanted to do something here, it'd make more sense to
> keep xact_start as it stands and introduce a new variable
> snapshot_timestamp or something like that.

Actually I think an application can easily get a timestamp with that
property by calling
    select clock_timestamp();
as the first (real) statement in the transaction. That will trigger the
snapshot and the timestamp will be made just after the snapshot (and
hence also after any commit seen by that snapshot).

(Using statement_timestamp() OTOH would not work because that timestamp
is from "the time of receipt of the latest command message from the
client", i.e. just before the snapshot, so there could still be commits
between that timestamp and 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!"

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

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

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmcBvOwACgkQ8g5IURL+
KF19Uw//Z2jPoRtF/SlEnMYgRps2Nwha4os7VK67z024gc0RW4On9SL2AmkH6WJW
XDkICq+T+Vex62BISnQjNkfomn7WR/7ZSl8pep/wvmh3UIHog8xhaRyyJjtvZfTD
quQ7o32QE268wUeQsn3f5ubndFgmO1q3jMTp/GJiB/vP2TP44DdNsAEccfmB4LC8
NeZ8FS95n7I9Wu/X/6FuKk++M4G+SXfUzDSgO4XN925CoBE1kKnxjY5n8QBeJlmV
UHyXBXjTsN7e97p6R+cpiuVvCEc0+GqNLd3HBBXod4kyeABi8LkEb6caluXQbzuf
5LtnMTlNc65ISZaR5KHx/OQR920/PtNqqfiAoGmbNZcREutAfU+9q9gzVPytYGyN
DhSCRp2ZSct71JBwPvJM8QkyAhmp8M+tN3Fwz9/LnEWC746O2KpewPMQWh5Oy7Qc
+P27RzexvQ3eHQYG/4ik8uF+KjTdZRvRJzq3/EnpJpokpd5W6xo0MKe97e3spVZH
NUDhVdtf5Oj2vB3AvMGaZDXw6muSn2irGt6Yew7Hw+ZX4x3p6ycFWXS3LNZGBluF
COzzehKqaNLjAntu4tuD31597fAaNTaNEPbDezTDBKbzvRNW3KNk/ysLEvoJiBG8
qIrQWXTUrsMz3ZD8WqnMj145D01oDlyiiPoatCF2tSEKS/zedVI=
=U4t8
-----END PGP SIGNATURE-----

--e34zgmjgxwadp5ga--