Feedback-ID: i76984098:Fastmail
Message-ID: <19575239-9d4d-4876-b9b2-df2e7059e698@aklaver.com>
Date: Sat, 5 Oct 2024 15:40:06 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Repeatable Read Isolation Level "transaction start time"
To: pgsql-general@lists.postgresql.org
References: <CAKAnmmLDpwgqu071t_Q7Fq349dYAgQim1x46U1DCAiiTcUmU2A@mail.gmail.com>
 <CANzqJaAAQFD_JvK=ChmfdGCRNP0G5gMtVBgF52bZiFjQxH8N_Q@mail.gmail.com>
 <CAKAnmmL05Lt89EOnMW111qdUOau+dYG7u8ff5o+ckKKohnNwGg@mail.gmail.com>
 <DD2C10F3-2A3C-44AB-AAC5-10BB15B15498@thebuild.com>
 <20240925215554.gfg24h5sp5aqesxv@hjp.at> <152525.1727302184@sss.pgh.pa.us>
 <20241005091424.34il2ss4noazgegx@hjp.at>
 <368259fb-fd2e-4a05-89e9-a733fae6d964@aklaver.com>
 <20241005203327.nb52nfuopdsjilvd@hjp.at> <1166698.1728162188@sss.pgh.pa.us>
 <20241005222555.wm53cmxnwfam4vtz@hjp.at>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <20241005222555.wm53cmxnwfam4vtz@hjp.at>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/19575239-9d4d-4876-b9b2-df2e7059e698%40aklaver.com>
Precedence: bulk

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@hjp.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.klaver@aklaver.com