On 2024-10-05 15:40:06 -0700, Adrian Klaver wrote: > 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). Sort of. One difference is that in autocommit mode pg_stat_activity.xact_start is sometimes (usually?) NULL. But my main point here is that the documentation is more confusing than helpful here. It's technically correct, but IMHO misleading. 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
