Re: Repeatable Read Isolation Level "transaction start time"

On 2024-10-05 17:03:08 -0400, Tom Lane wrote:
> "Peter J. Holzer" <hjp-pgsql(at)hjp(dot)at> 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.
|_|_) | |
| | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"