pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19738: `uuidv7(interval)` rejects sub-millisecond timestamps within the final 48-bit millisecond
4+ messages / 4 participants
[nested] [flat]
* BUG #19738: `uuidv7(interval)` rejects sub-millisecond timestamps within the final 48-bit millisecond
@ 2026-10-02 22:36 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 4+ messages in thread
From: PG Bug reporting form @ 2026-10-02 22:36 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: theshallow27@gmail.com
The following bug has been logged on the website:
Bug reference: 19738
Logged by: Shallow
Email address: theshallow27@gmail.com
PostgreSQL version: 18.6
Operating system: Linux
Description:
UUIDv7 stores a 48-bit Unix-millisecond timestamp, and PostgreSQL's
documentation says its timestamp also includes sub-millisecond precision. A
shifted timestamp 250 microseconds into the final representable millisecond
is
rejected as out of range, although its millisecond field is still the
maximum
valid value.
**Reproduction:**
```sql
SELECT uuidv7(
(timestamptz '1970-01-01 UTC'
+ interval '281474976710655 milliseconds'
+ interval '250 microseconds')
- clock_timestamp()
);
```
**Actual result:** SQLSTATE `22008`, `timestamp out of range for UUID
version 7`.
**Expected result:** Generate a UUIDv7 whose 48-bit millisecond timestamp is
`2^48 - 1`, preserving the permitted sub-millisecond component.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19738: `uuidv7(interval)` rejects sub-millisecond timestamps within the final 48-bit millisecond
@ 2026-10-04 13:54 Laurenz Albe <laurenz.albe@cybertec.at>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 4+ messages in thread
From: Laurenz Albe @ 2026-10-04 13:54 UTC (permalink / raw)
To: theshallow27@gmail.com; pgsql-bugs@lists.postgresql.org
On Fri, 2026-10-02 at 22:36 +0000, PG Bug reporting form wrote:
> PostgreSQL version: 18.6
>
> UUIDv7 stores a 48-bit Unix-millisecond timestamp, and PostgreSQL's
> documentation says its timestamp also includes sub-millisecond precision. A
> shifted timestamp 250 microseconds into the final representable millisecond
> is
> rejected as out of range, although its millisecond field is still the
> maximum
> valid value.
>
> **Reproduction:**
>
> ```sql
> SELECT uuidv7(
> (timestamptz '1970-01-01 UTC'
> + interval '281474976710655 milliseconds'
> + interval '250 microseconds')
> - clock_timestamp()
> );
> ```
>
> **Actual result:** SQLSTATE `22008`, `timestamp out of range for UUID
> version 7`.
>
> **Expected result:** Generate a UUIDv7 whose 48-bit millisecond timestamp is
> `2^48 - 1`, preserving the permitted sub-millisecond component.
I think that is a non-bug.
uuidv7(interval) gets the actual current timestamp (just like clock_timestamp
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 than the
timestamp you specified, so it might exceed the maximum possible timestamp.
And anyway, discussing the oddities of uuidv7() for a timestamp in the year
10889 is somewhat irrelevant...
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19738: `uuidv7(interval)` rejects sub-millisecond timestamps within the final 48-bit millisecond
@ 2026-10-04 16:03 Tom Lane <tgl@sss.pgh.pa.us>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2026-10-04 16:03 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: theshallow27@gmail.com; pgsql-bugs@lists.postgresql.org
Laurenz Albe <laurenz.albe@cybertec.at> writes:
> On Fri, 2026-10-02 at 22:36 +0000, PG Bug reporting form wrote:
>> SELECT uuidv7(
>> (timestamptz '1970-01-01 UTC'
>> + interval '281474976710655 milliseconds'
>> + interval '250 microseconds')
>> - clock_timestamp()
>> );
> I think that is a non-bug.
> uuidv7(interval) gets the actual current timestamp (just like clock_timestamp
> 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 than the
> timestamp you specified, so it might exceed the maximum possible timestamp.
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19738: `uuidv7(interval)` rejects sub-millisecond timestamps within the final 48-bit millisecond
@ 2026-10-05 09:47 Andrey Borodin <x4mmm@yandex-team.ru>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Andrey Borodin @ 2026-10-05 09:47 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; theshallow27@gmail.com; pgsql-bugs@lists.postgresql.org
On 4 Oct 2026, Tom Lane wrote:
> it's bandying around not two but three(!) different clock precisions,
The interval argument was intended to spread concurrent insert streams
over different index hot spots, keeping locality within each stream.
It shifts the generator's clock rather than taking an application
timestamp to encode. We discussed that distinction at some length
during development [0].
The precisions are deliberate: milliseconds for the UUID field,
microseconds for interval arithmetic, and finer clock precision for
the sub-millisecond bits. RFC 9562's Method 3 uses that extra precision
to reduce collisions [1], so we preserve the nanosecond remainder
across the interval calculation.
As for the original report, storing an arbitrary application timestamp
in a UUID is not the intended use. In our interpretation of the RFC,
these bits serve uniqueness and ordering, not timestamp storage. The
function deliberately uses its own clock, so
uuidv7(target - clock_timestamp()) does not promise to preserve the
exact microseconds of target.
Thank you!
Best regards, Andrey Borodin.
[0] https://www.postgresql.org/message-id/1012137874.340418.1721776188406@mail.yahoo.com
[1] https://www.rfc-editor.org/rfc/rfc9562.html#section-6.2
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-10-05 09:47 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-02 22:36 BUG #19738: `uuidv7(interval)` rejects sub-millisecond timestamps within the final 48-bit millisecond PG Bug reporting form <noreply@postgresql.org>
2026-10-04 13:54 ` Laurenz Albe <laurenz.albe@cybertec.at>
2026-10-04 16:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-10-05 09:47 ` Andrey Borodin <x4mmm@yandex-team.ru>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox