Re: Repeatable Read Isolation Level "transaction start time"

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(at)hjp(dot)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).

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(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"