public inbox for [email protected]
help / color / mirror / Atom feedFrom: Greg Sabino Mullane <[email protected]>
To: Adrian Klaver <[email protected]>
Cc: [email protected]
Subject: Re: Repeatable Read Isolation Level "transaction start time"
Date: Sat, 5 Oct 2024 13:21:54 -0400
Message-ID: <CAKAnmm+u3MH9vmgDrE6etJR_+TGYdJ8d-GjhhXAC2J51JUxTjg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAKAnmmLt2ezcf7-LgJkO_oeqK+Ei+XB6F3pHk2mnyadRq3corQ@mail.gmail.com>
<[email protected]>
<CANzqJaBjwtzLg_bZHkokUJbtA8gy6RpHjirfdBH9OJGfn5=tmw@mail.gmail.com>
<[email protected]>
<CAKAnmmLDpwgqu071t_Q7Fq349dYAgQim1x46U1DCAiiTcUmU2A@mail.gmail.com>
<CANzqJaAAQFD_JvK=ChmfdGCRNP0G5gMtVBgF52bZiFjQxH8N_Q@mail.gmail.com>
<CAKAnmmL05Lt89EOnMW111qdUOau+dYG7u8ff5o+ckKKohnNwGg@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
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
Attachments:
[application/octet-stream] 0001-Clarify-READ-REPEATABLE-behavior-a-bit-more.patch (1.2K, 3-0001-Clarify-READ-REPEATABLE-behavior-a-bit-more.patch)
download | inline diff:
From 79a37d8eb449583e526de7f6a17bdccadd2433ee Mon Sep 17 00:00:00 2001
From: Greg Sabino Mullane <[email protected]>
Date: Sat, 5 Oct 2024 13:19:27 -0400
Subject: [PATCH] Clarify READ REPEATABLE behavior a bit more
---
doc/src/sgml/mvcc.sgml | 12 ++++++++++++
1 file changed, 12 insertions(+)
diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml
index 380d0c9e80..295c30e3fe 100644
--- a/doc/src/sgml/mvcc.sgml
+++ b/doc/src/sgml/mvcc.sgml
@@ -516,6 +516,18 @@ COMMIT;
other transactions that committed after their own transaction started.
</para>
+ <tip>
+ <para>
+ Note that the snapshot is not obtained at the <command>BEGIN</command>,
+ so your view of the data is not locked into place until the first
+ statement. Note that a <command>SHOW</command> statement will not
+ obtain a snapshot, but <emphasis>ANY</emphasis> <command>SELECT</command>
+ statement will. Issuing a <literal>SELECT timeofday();</literal> after
+ the <command>BEGIN</command> is a good way to both start the snapshot and
+ return the time it was created.
+ </para>
+ </tip>
+
<para>
Applications using this level must be prepared to retry transactions
due to serialization failures.
--
2.30.2
view thread (11+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Repeatable Read Isolation Level "transaction start time"
In-Reply-To: <CAKAnmm+u3MH9vmgDrE6etJR_+TGYdJ8d-GjhhXAC2J51JUxTjg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox