BUG #19738: `uuidv7(interval)` rejects sub-millisecond timestamps within the final 48-bit millisecond
BUG #19738: `uuidv7(interval)` rejects sub-millisecond timestamps within the final 48-bit millisecond
От:
PG Bug reporting form <noreply@postgresql.org>
Дата:
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.
Re: BUG #19738: `uuidv7(interval)` rejects sub-millisecond timestamps within the final 48-bit millisecond
От:
Laurenz Albe <laurenz.albe@cybertec.at>
Дата:
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
Re: BUG #19738: `uuidv7(interval)` rejects sub-millisecond timestamps within the final 48-bit millisecond
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Laurenz Albe 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
Re: BUG #19738: `uuidv7(interval)` rejects sub-millisecond timestamps within the final 48-bit millisecond
От:
Andrey Borodin <x4mmm@yandex-team.ru>
Дата:
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