public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Klaver <[email protected]>
To: [email protected]
Subject: Re: Repeatable Read Isolation Level "transaction start time"
Date: Sat, 5 Oct 2024 15:40:06 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <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]>
	<[email protected]>
	<[email protected]>
	<[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]

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]
  Subject: Re: Repeatable Read Isolation Level "transaction start time"
  In-Reply-To: <[email protected]>

* 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