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. > 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@xxxxxx | -- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!"
Attachment:
signature.asc
Description: PGP signature
