From: Tom Lane <tgl@sss.pgh.pa.us>
To: "Peter J. Holzer" <hjp-pgsql@hjp.at>
cc: pgsql-general@lists.postgresql.org
Subject: Re: Repeatable Read Isolation Level "transaction start time"
In-reply-to: <20240925215554.gfg24h5sp5aqesxv@hjp.at>
References: <9CEBFAC7-4372-4FF0-8124-FFFE834B03C6@gmail.com> <3346993.1727188126@sss.pgh.pa.us> <CAKAnmmLt2ezcf7-LgJkO_oeqK+Ei+XB6F3pHk2mnyadRq3corQ@mail.gmail.com> <e9dba83f-0fba-4113-a13a-58aa2a98f092@aklaver.com> <CANzqJaBjwtzLg_bZHkokUJbtA8gy6RpHjirfdBH9OJGfn5=tmw@mail.gmail.com> <28109.1727286817@sss.pgh.pa.us> <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>
Comments: In-reply-to "Peter J. Holzer" <hjp-pgsql@hjp.at>
	message dated "Wed, 25 Sep 2024 23:55:54 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <152524.1727302184.1@sss.pgh.pa.us>
Date: Wed, 25 Sep 2024 18:09:44 -0400
Message-ID: <152525.1727302184@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/152525.1727302184%40sss.pgh.pa.us>
Precedence: bulk

"Peter J. Holzer" <hjp-pgsql@hjp.at> writes:
> On 2024-09-25 13:53:30 -0700, Christophe Pettus wrote:
>> This might well be a failure of imagination on my part, but when would
>> it pragmatically matter that the snapshot is taken at the first
>> statement as opposed to at BEGIN?

> It may make a difference if you're comparing timestamps.

> For example, if you're using isolation level REPEATABLE READ and
> (mistakenly) assume that the snapshot is taken at BEGIN, you would
> expect any  transaction_timestamp() written by a different transaction
> and readable by this transaction to be earlier than the
> transaction_timestamp() of this transaction.

> But that's wrong because the other transaction could have happened
> entirely in the time between your BEGIN and the statement which actually
> triggers the snapshot.

I don't find that hugely compelling, because there's always going
to be some skew between the time we read the clock for the timestamp
and the time we obtain the snapshot.  Admittedly, that would normally
not be a very long interval if BEGIN did both things ... but on a
busy system you could lose the CPU for awhile in between.

			regards, tom lane