From: Tom Lane <tgl@sss.pgh.pa.us>
To: Greg Sabino Mullane <htamfids@gmail.com>
cc: Adrian Klaver <adrian.klaver@aklaver.com>,
        pgsql-general@lists.postgresql.org
Subject: Re: Repeatable Read Isolation Level "transaction start time"
In-reply-to: <CAKAnmm+u3MH9vmgDrE6etJR_+TGYdJ8d-GjhhXAC2J51JUxTjg@mail.gmail.com>
References: <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> <152525.1727302184@sss.pgh.pa.us> <20241005091424.34il2ss4noazgegx@hjp.at> <368259fb-fd2e-4a05-89e9-a733fae6d964@aklaver.com> <CAKAnmm+u3MH9vmgDrE6etJR_+TGYdJ8d-GjhhXAC2J51JUxTjg@mail.gmail.com>
Comments: In-reply-to Greg Sabino Mullane <htamfids@gmail.com>
	message dated "Sat, 05 Oct 2024 13:21:54 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1067323.1728155379.1@sss.pgh.pa.us>
Date: Sat, 05 Oct 2024 15:09:39 -0400
Message-ID: <1067324.1728155379@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/1067324.1728155379%40sss.pgh.pa.us>
Precedence: bulk

Greg Sabino Mullane <htamfids@gmail.com> writes:
> All we can guarantee
> via pg_stat_activity is that if xact_start and query_start *are* identical,
> no snapshot has been granted yet,

Surely that's not true either.  xact_start = query_start implies that
the current statement is the first in its transaction (assuming
sufficiently fine-grained clock timestamps, something I'm not sure is
an entirely safe assumption).  But if that statement is not simply
a BEGIN, it's likely obtained its own transaction snapshot after a
few microseconds.

As long as "read the system clock" is a distinct operation from
"read a snapshot", there are going to be skew issues here.  We
could maybe eliminate that by reading the clock while holding the
lock that prevents commits while reading a snapshot, but I doubt
that anybody is going to accept that on performance grounds.
Adding a not-guaranteed-cheap syscall inside that extremely hot
code path seems unsatisfactory.

Also, we currently do guarantee that xact_start matches query_start
for the first statement of the transaction (the converse of what
I said above).  Removing that guarantee in order to add some other
one wouldn't necessarily please everybody.

			regards, tom lane