MIME-Version: 1.0
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> <20241005091424.34il2ss4noazgegx@hjp.at> <368259fb-fd2e-4a05-89e9-a733fae6d964@aklaver.com>
In-Reply-To: <368259fb-fd2e-4a05-89e9-a733fae6d964@aklaver.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Sat, 5 Oct 2024 13:21:54 -0400
Message-ID: <CAKAnmm+u3MH9vmgDrE6etJR_+TGYdJ8d-GjhhXAC2J51JUxTjg@mail.gmail.com>
Subject: Re: Repeatable Read Isolation Level "transaction start time"
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/mixed; boundary="0000000000004cfd530623be086f"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmm%2Bu3MH9vmgDrE6etJR_%2BTGYdJ8d-GjhhXAC2J51JUxTjg%40mail.gmail.com>
Precedence: bulk

--0000000000004cfd530623be086f
Content-Type: multipart/alternative; boundary="0000000000004cfd500623be086d"

--0000000000004cfd500623be086d
Content-Type: text/plain; charset="UTF-8"

While working on a doc patch for this, I realized that the situation is
worse than I originally thought. This means that anyone relying on
pg_stat_activity.xact_start is not really seeing the time of the snapshot.
They are seeing the time that BEGIN was issued. Further, there is no way to
tell (AFAICT) when the snapshot was granted (i.e. when the transaction
actually started for purposes of MVCC comparisons). All we can guarantee
via pg_stat_activity is that if xact_start and query_start *are* identical,
no snapshot has been granted yet, and if they are not identical, then the
snapshot *might* have been granted, might not (depending on SHOW vs SELECT
for example). I suppose checking "query" could show that, but all you have
then is a general window saying that the snapshot was created sometime
after xact_start but no later than query_start (and could be a lot earlier
if this ain't query number one).

Maybe we doc patch pg_stat_activity too? Actually, let me just post my
quick work-in-progress patch here in the meantime for discussion.

Cheers,
Greg

--0000000000004cfd500623be086d
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">While working on a doc patch for this, I realized that the=
 situation is worse than I originally thought. This means that anyone relyi=
ng on pg_stat_activity.xact_start is not really seeing the time of the snap=
shot. They are seeing the time that BEGIN was issued. Further, there is no =
way to tell (AFAICT) when the snapshot was granted (i.e. when the transacti=
on actually started for purposes of MVCC comparisons). All we can guarantee=
 via pg_stat_activity is that if xact_start and query_start *are* identical=
, no snapshot has been granted yet, and if they are not identical, then the=
 snapshot *might* have been granted, might not (depending on SHOW vs SELECT=
 for example). I suppose checking &quot;query&quot; could show that, but al=
l you have then is a general window saying that the snapshot was created so=
metime after xact_start but no later than query_start (and could be a lot e=
arlier if this ain&#39;t query number one).<div><br></div><div>Maybe we doc=
 patch pg_stat_activity too? Actually, let me just post my quick work-in-pr=
ogress patch here in the meantime for discussion.</div><div><br></div><div>=
Cheers,</div><div>Greg</div><div><br></div><div><br></div></div>

--0000000000004cfd500623be086d--
--0000000000004cfd530623be086f
Content-Type: application/octet-stream; 
	name="0001-Clarify-READ-REPEATABLE-behavior-a-bit-more.patch"
Content-Disposition: attachment; 
	filename="0001-Clarify-READ-REPEATABLE-behavior-a-bit-more.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_m1wf6ws40>
X-Attachment-Id: f_m1wf6ws40

RnJvbSA3OWEzN2Q4ZWI0NDk1ODNlNTI2ZGU3ZjZhMTdiZGNjYWRkMjQzM2VlIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBHcmVnIFNhYmlubyBNdWxsYW5lIDxncmVnQHR1cm5zdGVwLmNv
bT4KRGF0ZTogU2F0LCA1IE9jdCAyMDI0IDEzOjE5OjI3IC0wNDAwClN1YmplY3Q6IFtQQVRDSF0g
Q2xhcmlmeSBSRUFEIFJFUEVBVEFCTEUgYmVoYXZpb3IgYSBiaXQgbW9yZQoKLS0tCiBkb2Mvc3Jj
L3NnbWwvbXZjYy5zZ21sIHwgMTIgKysrKysrKysrKysrCiAxIGZpbGUgY2hhbmdlZCwgMTIgaW5z
ZXJ0aW9ucygrKQoKZGlmZiAtLWdpdCBhL2RvYy9zcmMvc2dtbC9tdmNjLnNnbWwgYi9kb2Mvc3Jj
L3NnbWwvbXZjYy5zZ21sCmluZGV4IDM4MGQwYzllODAuLjI5NWMzMGUzZmUgMTAwNjQ0Ci0tLSBh
L2RvYy9zcmMvc2dtbC9tdmNjLnNnbWwKKysrIGIvZG9jL3NyYy9zZ21sL212Y2Muc2dtbApAQCAt
NTE2LDYgKzUxNiwxOCBAQCBDT01NSVQ7CiAgICAgb3RoZXIgdHJhbnNhY3Rpb25zIHRoYXQgY29t
bWl0dGVkIGFmdGVyIHRoZWlyIG93biB0cmFuc2FjdGlvbiBzdGFydGVkLgogICAgPC9wYXJhPgog
CisgICA8dGlwPgorICAgIDxwYXJhPgorICAgICBOb3RlIHRoYXQgdGhlIHNuYXBzaG90IGlzIG5v
dCBvYnRhaW5lZCBhdCB0aGUgPGNvbW1hbmQ+QkVHSU48L2NvbW1hbmQ+LAorICAgICBzbyB5b3Vy
IHZpZXcgb2YgdGhlIGRhdGEgaXMgbm90IGxvY2tlZCBpbnRvIHBsYWNlIHVudGlsIHRoZSBmaXJz
dAorICAgICBzdGF0ZW1lbnQuIE5vdGUgdGhhdCBhIDxjb21tYW5kPlNIT1c8L2NvbW1hbmQ+IHN0
YXRlbWVudCB3aWxsIG5vdAorICAgICBvYnRhaW4gYSBzbmFwc2hvdCwgYnV0IDxlbXBoYXNpcz5B
Tlk8L2VtcGhhc2lzPiA8Y29tbWFuZD5TRUxFQ1Q8L2NvbW1hbmQ+CisgICAgIHN0YXRlbWVudCB3
aWxsLiBJc3N1aW5nIGEgPGxpdGVyYWw+U0VMRUNUIHRpbWVvZmRheSgpOzwvbGl0ZXJhbD4gYWZ0
ZXIKKyAgICAgdGhlIDxjb21tYW5kPkJFR0lOPC9jb21tYW5kPiBpcyBhIGdvb2Qgd2F5IHRvIGJv
dGggc3RhcnQgdGhlIHNuYXBzaG90IGFuZAorICAgICByZXR1cm4gdGhlIHRpbWUgaXQgd2FzIGNy
ZWF0ZWQuCisgICAgPC9wYXJhPgorICAgPC90aXA+CisKICAgIDxwYXJhPgogICAgIEFwcGxpY2F0
aW9ucyB1c2luZyB0aGlzIGxldmVsIG11c3QgYmUgcHJlcGFyZWQgdG8gcmV0cnkgdHJhbnNhY3Rp
b25zCiAgICAgZHVlIHRvIHNlcmlhbGl6YXRpb24gZmFpbHVyZXMuCi0tIAoyLjMwLjIKCg==
--0000000000004cfd530623be086f--