Subject: Re: Repeatable Read Isolation Level "transaction start time"

From: Tom Lane <[email-masked]>
Date: Wed, 25 Sep 2024 18:09:44 -0400

"Peter J. Holzer" <[email-masked]> 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?

> It may make a difference if you're comparing timestamps.

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

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

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

			regards, tom lane

---

From: "Peter J. Holzer" <[email-masked]>
Date: Sat, 5 Oct 2024 11:14:24 +0200

On 2024-09-25 18:09:44 -0400, Tom Lane wrote:
> 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 >= t_S, then
transaction_timestamp(e) < t_C(e) < t_S(c) <= transaction_timestamp(c)
and therefore 
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.

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

---

From: Adrian Klaver <[email-masked]>
Date: Sat, 5 Oct 2024 09:59:00 -0700

> that t_C(e) < t_S(c) for each "earlier" transaction.

Assuming t_C is time of commit and t_S is time of snapshot, is the above 
not the crux of the matter? Namely when in the current transaction the 
snapshot is actually taken. That would determine what constitutes an 
earlier visible transaction relative to the current transaction. In 
other words I am not seeing how this changes anything?

-- 
Adrian Klaver
[email-masked]

---

From: Greg Sabino Mullane <[email-masked]>
Date: Sat, 5 Oct 2024 13:21:54 -0400

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

---

From: Tom Lane <[email-masked]>
Date: Sat, 05 Oct 2024 15:09:39 -0400

Greg Sabino Mullane <[email-masked]> writes:
> All we can guarantee
> no snapshot has been granted yet,

Surely that's not true either.  xact_start = query_start implies that
the current statement is the first in its transaction (assuming
sufficiently fine-grained clock timestamps, something I'm not sure is
an entirely safe assumption).  But if that statement is not simply
a BEGIN, it's likely obtained its own transaction snapshot after a
few microseconds.

As long as "read the system clock" is a distinct operation from
"read a snapshot", there are going to be skew issues here.  We
could maybe eliminate that by reading the clock while holding the
lock that prevents commits while reading a snapshot, but I doubt
that anybody is going to accept that on performance grounds.
Adding a not-guaranteed-cheap syscall inside that extremely hot
code path seems unsatisfactory.

Also, we currently do guarantee that xact_start matches query_start
for the first statement of the transaction (the converse of what
I said above).  Removing that guarantee in order to add some other
one wouldn't necessarily please everybody.

			regards, tom lane

---

From: "Peter J. Holzer" <[email-masked]>
Date: Sat, 5 Oct 2024 22:33:27 +0200

On 2024-10-05 09:59:00 -0700, Adrian Klaver wrote:
> Assuming t_C is time of commit and t_S is time of snapshot, is the
> above not the crux of the matter? Namely when in the current
> transaction the snapshot is actually taken. That would determine what
> constitutes an earlier visible transaction relative to the current
> transaction. In other words I am not seeing how this changes anything?

> > transaction_timestamp(e) < transaction_timestamp(c)

In PostgreSQL, transaction_timestamp is taken during BEGIN (as Greg
noted). If it was instead taken at the end of the snapshot, it would be
guaranteed to be later than any transaction_timestamp of an earlier
transaction.

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.

        hp

---

From: Tom Lane <[email-masked]>
Date: Sat, 05 Oct 2024 17:03:08 -0400

> whether that might break some assumption that programmers can currently

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

Another issue with redefining things like that is that presumably,
for a session that has issued BEGIN but not yet taken a snapshot,
xact_start would have to read as NULL, misleadingly implying that
the session doesn't have an open 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 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.  Then maybe we could have
some guarantees about what you get when comparing other sessions'
xact_start to your own snapshot_timestamp.  But I'm not convinced we
can really guarantee anything without reading the snapshot_timestamp
within the snapshot-collecting critical section, and I'm not for that.

			regards, tom lane

---

From: "Peter J. Holzer" <[email-masked]>
Date: Sun, 6 Oct 2024 00:25:55 +0200

> it so, so somebody thought it was important.)

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

| statement_timestamp() and transaction_timestamp() return the same value during 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=> begin; select transaction_timestamp(), statement_timestamp(), clock_timestamp(); rollback;
BEGIN
Time: 0.406 ms
╔═[ RECORD 1 ]══════════╤═══════════════════════════════╗
║ transaction_timestamp │ 2024-10-05 23:55:47.356582+02 ║
║ statement_timestamp   │ 2024-10-05 23:55:47.357106+02 ║
║ clock_timestamp       │ 2024-10-05 23:55:47.357397+02 ║
╚═══════════════════════╧═══════════════════════════════╝

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() = statement_timestamp()
is outside of a transaction.

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

> 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

---

From: Adrian Klaver <[email-masked]>
Date: Sat, 5 Oct 2024 15:40:06 -0700

> ║ clock_timestamp       │ 2024-10-05 23:55:47.357397+02 ║

AFAIK that is still a transaction:

https://www.postgresql.org/docs/current/sql-begin.html

By default (without BEGIN), PostgreSQL executes transactions in 
“autocommit” 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).

---

From: "Peter J. Holzer" <[email-masked]>
Date: Sun, 6 Oct 2024 14:18:34 +0200

On 2024-10-05 15:40:06 -0700, Adrian Klaver wrote:
> By default (without BEGIN), PostgreSQL executes transactions in “autocommit”
> 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

---

From: Greg Sabino Mullane <[email-masked]>
Date: Mon, 7 Oct 2024 11:12:21 -0400

On Sat, Oct 5, 2024 at 5:03 PM Tom Lane <[email-masked]> wrote:

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

I'm not convinced this is terribly useful in practice, but it is good to
know.

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

I agree; I've been thinking about something like this, as it is too hard to
try to shoehorn the information into the existing fields. Will throw this
onto my "possible patch idea" pile.

Then maybe we could have some guarantees about what you get when comparing
> other sessions'
> xact_start to your own snapshot_timestamp.  But I'm not convinced we can
> really guarantee anything without reading the snapshot_timestamp within the
> snapshot-collecting critical section, and I'm not for that.
>

Fair enough, but even a not-guaranteed 100% accurate value might be better
than the current situation, which is no (user) visibility at all about the
(MVCC) visibility. Heck, even a boolean "snapshot acquired" would be an
improvement (which becomes a subset of the info returned by a timestamp via
null/notnull).

Cheers,
Greg