Date: Sun, 6 Oct 2024 14:18:34 +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: <20241006121834.xuam2gnmqd47a7of@hjp.at>
Mail-Followup-To: pgsql-general@lists.postgresql.org
References: <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>
 <20241005222555.wm53cmxnwfam4vtz@hjp.at>
 <19575239-9d4d-4876-b9b2-df2e7059e698@aklaver.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="l2in3xsfsenjoz32"
Content-Disposition: inline
In-Reply-To: <19575239-9d4d-4876-b9b2-df2e7059e698@aklaver.com>
Archived-At: <https://www.postgresql.org/message-id/20241006121834.xuam2gnmqd47a7of%40hjp.at>
Precedence: bulk


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

On 2024-10-05 15:40:06 -0700, Adrian Klaver wrote:
> On 10/5/24 15:25, Peter J. Holzer wrote:
> > 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 reco=
rding
> > > > transaction_timestamp just after the snapshot might be a safe chang=
e or
> > > > whether that might break some assumption that programmers can curre=
ntly
> > > > 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.)
> >=20
> > It's mentioned in
> > https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIO=
NS-DATETIME-CURRENT
> >=20
> > | statement_timestamp() and transaction_timestamp() return the same val=
ue during the first command of a transaction
> >=20
> > 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:
> >=20
> > hjp=3D> begin; select transaction_timestamp(), statement_timestamp(), c=
lock_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
> >=20
> > Time: 0.570 ms
> > ROLLBACK
> > Time: 0.285 ms
> >=20
> > The difference are only 0.5 ms, but it's not zero.
> >=20
> > I think the only case where transaction_timestamp() =3D statement_times=
tamp()
> > is outside of a transaction.
> >=20
>=20
> AFAIK that is still a transaction:
>=20
> https://www.postgresql.org/docs/current/sql-begin.html
>=20
> By default (without BEGIN), PostgreSQL executes transactions in =E2=80=9C=
autocommit=E2=80=9D
> mode, that is, each statement is executed in its own transaction and a
> commit is implicitly performed at the end of the statement (if execution =
was
> successful, otherwise a rollback is done).

Sort of. One difference is that in autocommit mode
pg_stat_activity.xact_start is sometimes (usually?) NULL.

But my main point here is that the documentation is more confusing than
helpful here. It's technically correct, but IMHO misleading.

        hp

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

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

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

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmcCgBQACgkQ8g5IURL+
KF11Bw//Yh25kj8Q76MyZkAwD5DQzwAC22DTEgafCMYW8eVvRYoOFxYM1KgyRfxC
1egCtOinDwu1fL+aTmsd/+NEE9/hxEdvCV0CJRGp71xrOEaRuDCZH10iczSBLfrr
krnP1Uch7fNnt/KAGJXXPFG45nhJSReL+chzHFgVZUSc+FZ9fMGlMemqMiZ/EBOk
bEbbwBhWNweqlSTuEwoSefjDRSuAeyhLugmdZ2jIPIe2ev8Oczma/NcNjLLawPV9
FYHwCtMf0Nuw+QcR1eixmBiCAichw/g4fdCUB8JdhTaxrQUVmwvNDhSALxb3G2+K
+TZ8GgmnU1SXhaq1+j+Wf2GiQLhhor8qUaWNf4fCDlMCl8zOCduyULrusgDr78qt
SlJIwmNpCtgjotdLcUtYG3Kb48fslmNnqez39xEaXXB4lsRiH3iDir1BLIVEHmdm
vPid4L43wk0Ip0co5+/sAsn2UN3mz+j6bITbbvK7EWM3rOSmlTLBDMPWldh28dR+
PPx5ELAb32YzVbpAcDCucxfTrCWLuvXN3RMoSFYs7Nrml0P9e2izNy2wDAA23wzB
wLH6/WGvuuqs2DSzRD15BS2TgW8AzwmqT40h3g5yDFUSnHMx4xQ+MfUkLsfh5xRh
fGrmhBpgwc/k8UWKebEfFNEt/r+z5XgxsY3L4a1rGVzH5tD+7ec=
=uHZB
-----END PGP SIGNATURE-----

--l2in3xsfsenjoz32--