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@xxxxxx> 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@xxxxxxxxxxx
