From: Tom Lane <tgl@sss.pgh.pa.us>
To: Laurenz Albe <laurenz.albe@cybertec.at>
cc: theshallow27@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19738: `uuidv7(interval)` rejects sub-millisecond timestamps
 within the final 48-bit millisecond
In-reply-to: <33f0828a80f76e6adcefda8fd788113e69df755e.camel@cybertec.at>
References: <19738-659d03aaa312f3b8@postgresql.org>
 <33f0828a80f76e6adcefda8fd788113e69df755e.camel@cybertec.at>
Comments: In-reply-to Laurenz Albe <laurenz.albe@cybertec.at>
	message dated "Sun, 04 Oct 2026 15:54:39 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <342849.1791129804.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sun, 04 Oct 2026 12:03:24 -0400
Message-ID: <342850.1791129804@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/342850.1791129804%40sss.pgh.pa.us>
Precedence: bulk

Laurenz Albe <laurenz.albe@cybertec.at> writes:
> On Fri, 2026-10-02 at 22:36 +0000, PG Bug reporting form wrote:
>> SELECT uuidv7(
>> =C2=A0=C2=A0=C2=A0 (timestamptz '1970-01-01 UTC'
>> =C2=A0=C2=A0=C2=A0=C2=A0 + interval '281474976710655 milliseconds'
>> =C2=A0=C2=A0=C2=A0=C2=A0 + interval '250 microseconds')
>> =C2=A0=C2=A0=C2=A0 - clock_timestamp()
>> );

> I think that is a non-bug.
> uuidv7(interval) gets the actual current timestamp (just like clock_time=
stamp
> does) and adds that to the interval argument.  That call is a bit later =
than
> the clock_timestamp in your statement, so the result is maybe bigger tha=
n the
> timestamp you specified, so it might exceed the maximum possible timesta=
mp.

I poked at this by instrumenting uuidv7_interval, and verified that
(at least on my machine) we compute values that are two or three or so
microseconds larger than expected, due to the time elapsed between
clock_timestamp() and uuidv7_interval's own clock reading.  But this
example would fail even without that effect, because we're rejecting
values larger than UUIDV7_MAX_TIMESTAMP, and the computed result is
bigger than that by 250 microseconds plus that clock offset.

I do think that uuidv7_interval is rather poorly written: it's
bandying around not two but three(!) different clock precisions,
with absolutely no attention paid to the niceties of rounding
off properly when switching precisions.  So this bug report is
indeed pointing at something that could be done better.  But if
the worst consequence is that you can't reliably generate a
v7 UUID with the maximum clock field value, I doubt anybody is going
to spend time on it.  I can't see that that's an interesting use
case, so I think we have far more pressing problems to deal with.

			regards, tom lane