public inbox for [email protected]
help / color / mirror / Atom feedRe: Repeatable Read Isolation Level "transaction start time"
11+ messages / 4 participants
[nested] [flat]
* Re: Repeatable Read Isolation Level "transaction start time"
@ 2024-09-25 22:09 Tom Lane <[email protected]>
2024-10-05 09:14 ` Re: Repeatable Read Isolation Level "transaction start time" Peter J. Holzer <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2024-09-25 22:09 UTC (permalink / raw)
To: Peter J. Holzer <[email protected]>; +Cc: [email protected]
"Peter J. Holzer" <[email protected]> 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Repeatable Read Isolation Level "transaction start time"
2024-09-25 22:09 Re: Repeatable Read Isolation Level "transaction start time" Tom Lane <[email protected]>
@ 2024-10-05 09:14 ` Peter J. Holzer <[email protected]>
2024-10-05 16:59 ` Re: Repeatable Read Isolation Level "transaction start time" Adrian Klaver <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Peter J. Holzer @ 2024-10-05 09:14 UTC (permalink / raw)
To: [email protected]
On 2024-09-25 18:09:44 -0400, Tom Lane wrote:
> "Peter J. Holzer" <[email protected]> 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.
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 protected] | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Repeatable Read Isolation Level "transaction start time"
2024-09-25 22:09 Re: Repeatable Read Isolation Level "transaction start time" Tom Lane <[email protected]>
2024-10-05 09:14 ` Re: Repeatable Read Isolation Level "transaction start time" Peter J. Holzer <[email protected]>
@ 2024-10-05 16:59 ` Adrian Klaver <[email protected]>
2024-10-05 17:21 ` Re: Repeatable Read Isolation Level "transaction start time" Greg Sabino Mullane <[email protected]>
2024-10-05 20:33 ` Re: Repeatable Read Isolation Level "transaction start time" Peter J. Holzer <[email protected]>
0 siblings, 2 replies; 11+ messages in thread
From: Adrian Klaver @ 2024-10-05 16:59 UTC (permalink / raw)
To: [email protected]
On 10/5/24 02:14, Peter J. Holzer wrote:
> On 2024-09-25 18:09:44 -0400, Tom Lane wrote:
>> "Peter J. Holzer" <[email protected]> writes:
>> 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.
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?
>
> 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
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Repeatable Read Isolation Level "transaction start time"
2024-09-25 22:09 Re: Repeatable Read Isolation Level "transaction start time" Tom Lane <[email protected]>
2024-10-05 09:14 ` Re: Repeatable Read Isolation Level "transaction start time" Peter J. Holzer <[email protected]>
2024-10-05 16:59 ` Re: Repeatable Read Isolation Level "transaction start time" Adrian Klaver <[email protected]>
@ 2024-10-05 17:21 ` Greg Sabino Mullane <[email protected]>
2024-10-05 19:09 ` Re: Repeatable Read Isolation Level "transaction start time" Tom Lane <[email protected]>
1 sibling, 1 reply; 11+ messages in thread
From: Greg Sabino Mullane @ 2024-10-05 17:21 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: [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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Repeatable Read Isolation Level "transaction start time"
2024-09-25 22:09 Re: Repeatable Read Isolation Level "transaction start time" Tom Lane <[email protected]>
2024-10-05 09:14 ` Re: Repeatable Read Isolation Level "transaction start time" Peter J. Holzer <[email protected]>
2024-10-05 16:59 ` Re: Repeatable Read Isolation Level "transaction start time" Adrian Klaver <[email protected]>
2024-10-05 17:21 ` Re: Repeatable Read Isolation Level "transaction start time" Greg Sabino Mullane <[email protected]>
@ 2024-10-05 19:09 ` Tom Lane <[email protected]>
0 siblings, 0 replies; 11+ messages in thread
From: Tom Lane @ 2024-10-05 19:09 UTC (permalink / raw)
To: Greg Sabino Mullane <[email protected]>; +Cc: Adrian Klaver <[email protected]>; [email protected]
Greg Sabino Mullane <[email protected]> writes:
> All we can guarantee
> via pg_stat_activity is that if xact_start and query_start *are* identical,
> 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Repeatable Read Isolation Level "transaction start time"
2024-09-25 22:09 Re: Repeatable Read Isolation Level "transaction start time" Tom Lane <[email protected]>
2024-10-05 09:14 ` Re: Repeatable Read Isolation Level "transaction start time" Peter J. Holzer <[email protected]>
2024-10-05 16:59 ` Re: Repeatable Read Isolation Level "transaction start time" Adrian Klaver <[email protected]>
@ 2024-10-05 20:33 ` Peter J. Holzer <[email protected]>
2024-10-05 21:03 ` Re: Repeatable Read Isolation Level "transaction start time" Tom Lane <[email protected]>
1 sibling, 1 reply; 11+ messages in thread
From: Peter J. Holzer @ 2024-10-05 20:33 UTC (permalink / raw)
To: [email protected]
On 2024-10-05 09:59:00 -0700, Adrian Klaver wrote:
> On 10/5/24 02:14, Peter J. Holzer wrote:
> > On 2024-09-25 18:09:44 -0400, Tom Lane wrote:
> > > "Peter J. Holzer" <[email protected]> writes:
> > > 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.
>
> 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?
The important part is in the last paragraph:
> > 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)
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
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | [email protected] | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Repeatable Read Isolation Level "transaction start time"
2024-09-25 22:09 Re: Repeatable Read Isolation Level "transaction start time" Tom Lane <[email protected]>
2024-10-05 09:14 ` Re: Repeatable Read Isolation Level "transaction start time" Peter J. Holzer <[email protected]>
2024-10-05 16:59 ` Re: Repeatable Read Isolation Level "transaction start time" Adrian Klaver <[email protected]>
2024-10-05 20:33 ` Re: Repeatable Read Isolation Level "transaction start time" Peter J. Holzer <[email protected]>
@ 2024-10-05 21:03 ` Tom Lane <[email protected]>
2024-10-05 22:25 ` Re: Repeatable Read Isolation Level "transaction start time" Peter J. Holzer <[email protected]>
2024-10-07 15:12 ` Re: Repeatable Read Isolation Level "transaction start time" Greg Sabino Mullane <[email protected]>
0 siblings, 2 replies; 11+ messages in thread
From: Tom Lane @ 2024-10-05 21:03 UTC (permalink / raw)
To: Peter J. Holzer <[email protected]>; +Cc: [email protected]
"Peter J. Holzer" <[email protected]> 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.
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Repeatable Read Isolation Level "transaction start time"
2024-09-25 22:09 Re: Repeatable Read Isolation Level "transaction start time" Tom Lane <[email protected]>
2024-10-05 09:14 ` Re: Repeatable Read Isolation Level "transaction start time" Peter J. Holzer <[email protected]>
2024-10-05 16:59 ` Re: Repeatable Read Isolation Level "transaction start time" Adrian Klaver <[email protected]>
2024-10-05 20:33 ` Re: Repeatable Read Isolation Level "transaction start time" Peter J. Holzer <[email protected]>
2024-10-05 21:03 ` Re: Repeatable Read Isolation Level "transaction start time" Tom Lane <[email protected]>
@ 2024-10-05 22:25 ` Peter J. Holzer <[email protected]>
2024-10-05 22:40 ` Re: Repeatable Read Isolation Level "transaction start time" Adrian Klaver <[email protected]>
1 sibling, 1 reply; 11+ messages in thread
From: Peter J. Holzer @ 2024-10-05 22:25 UTC (permalink / raw)
To: [email protected]
On 2024-10-05 17:03:08 -0400, Tom Lane wrote:
> "Peter J. Holzer" <[email protected]> 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.
>
> 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
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.
> 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
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | [email protected] | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Repeatable Read Isolation Level "transaction start time"
2024-09-25 22:09 Re: Repeatable Read Isolation Level "transaction start time" Tom Lane <[email protected]>
2024-10-05 09:14 ` Re: Repeatable Read Isolation Level "transaction start time" Peter J. Holzer <[email protected]>
2024-10-05 16:59 ` Re: Repeatable Read Isolation Level "transaction start time" Adrian Klaver <[email protected]>
2024-10-05 20:33 ` Re: Repeatable Read Isolation Level "transaction start time" Peter J. Holzer <[email protected]>
2024-10-05 21:03 ` Re: Repeatable Read Isolation Level "transaction start time" Tom Lane <[email protected]>
2024-10-05 22:25 ` Re: Repeatable Read Isolation Level "transaction start time" Peter J. Holzer <[email protected]>
@ 2024-10-05 22:40 ` Adrian Klaver <[email protected]>
2024-10-06 12:18 ` Re: Repeatable Read Isolation Level "transaction start time" Peter J. Holzer <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Adrian Klaver @ 2024-10-05 22:40 UTC (permalink / raw)
To: [email protected]
On 10/5/24 15:25, Peter J. Holzer wrote:
> On 2024-10-05 17:03:08 -0400, Tom Lane wrote:
>> "Peter J. Holzer" <[email protected]> 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.
>>
>> 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
> 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.
>
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).
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Repeatable Read Isolation Level "transaction start time"
2024-09-25 22:09 Re: Repeatable Read Isolation Level "transaction start time" Tom Lane <[email protected]>
2024-10-05 09:14 ` Re: Repeatable Read Isolation Level "transaction start time" Peter J. Holzer <[email protected]>
2024-10-05 16:59 ` Re: Repeatable Read Isolation Level "transaction start time" Adrian Klaver <[email protected]>
2024-10-05 20:33 ` Re: Repeatable Read Isolation Level "transaction start time" Peter J. Holzer <[email protected]>
2024-10-05 21:03 ` Re: Repeatable Read Isolation Level "transaction start time" Tom Lane <[email protected]>
2024-10-05 22:25 ` Re: Repeatable Read Isolation Level "transaction start time" Peter J. Holzer <[email protected]>
2024-10-05 22:40 ` Re: Repeatable Read Isolation Level "transaction start time" Adrian Klaver <[email protected]>
@ 2024-10-06 12:18 ` Peter J. Holzer <[email protected]>
0 siblings, 0 replies; 11+ messages in thread
From: Peter J. Holzer @ 2024-10-06 12:18 UTC (permalink / raw)
To: [email protected]
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" <[email protected]> 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.
> > >
> > > 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
> > 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.
> >
>
> 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).
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
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | [email protected] | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Repeatable Read Isolation Level "transaction start time"
2024-09-25 22:09 Re: Repeatable Read Isolation Level "transaction start time" Tom Lane <[email protected]>
2024-10-05 09:14 ` Re: Repeatable Read Isolation Level "transaction start time" Peter J. Holzer <[email protected]>
2024-10-05 16:59 ` Re: Repeatable Read Isolation Level "transaction start time" Adrian Klaver <[email protected]>
2024-10-05 20:33 ` Re: Repeatable Read Isolation Level "transaction start time" Peter J. Holzer <[email protected]>
2024-10-05 21:03 ` Re: Repeatable Read Isolation Level "transaction start time" Tom Lane <[email protected]>
@ 2024-10-07 15:12 ` Greg Sabino Mullane <[email protected]>
1 sibling, 0 replies; 11+ messages in thread
From: Greg Sabino Mullane @ 2024-10-07 15:12 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Peter J. Holzer <[email protected]>; [email protected]
On Sat, Oct 5, 2024 at 5:03 PM Tom Lane <[email protected]> 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
> snapshot_timestamp or something like that.
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
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2024-10-07 15:12 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-09-25 22:09 Re: Repeatable Read Isolation Level "transaction start time" Tom Lane <[email protected]>
2024-10-05 09:14 ` Peter J. Holzer <[email protected]>
2024-10-05 16:59 ` Adrian Klaver <[email protected]>
2024-10-05 17:21 ` Greg Sabino Mullane <[email protected]>
2024-10-05 19:09 ` Tom Lane <[email protected]>
2024-10-05 20:33 ` Peter J. Holzer <[email protected]>
2024-10-05 21:03 ` Tom Lane <[email protected]>
2024-10-05 22:25 ` Peter J. Holzer <[email protected]>
2024-10-05 22:40 ` Adrian Klaver <[email protected]>
2024-10-06 12:18 ` Peter J. Holzer <[email protected]>
2024-10-07 15:12 ` Greg Sabino Mullane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox