Re: Repeatable Read Isolation Level "transaction start time"

On 10/5/24 15:25, Peter J. Holzer wrote:
> 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.
>

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
adrian(dot)klaver(at)aklaver(dot)com