Subject: Re: uuidv7 improperly accepts dates before 1970-01-01

From: Christophe Pettus <[email-masked]>
Date: Mon, 27 Apr 2026 15:51:12 -0700

Hi, Andrey,

Thanks for the response!  I'm moving it to -hackers since it's not really a bug related conversation at this point.  (resending with the right list this time!)

> On Apr 25, 2026, at 05:26, Andrey Borodin <[email-masked]> wrote:

> We consulted with RFC authors
> about this feature, and they confirmed that shifting time is compliant with RFC wording.

Time shifting doesn't automatically imply allowing a pre-epoch input time to construct a UUIDv7, though, just that you can construct a UUIDv7 with something other than wall-clock time.

> We wrote the specific test that ensures vast space for shift, but not unlimited.

That's another problem: the API gives the impression of a much larger space than actually exists.

# select uuidv7('100000 years'::interval);   # ~11.2 x total time range in a UUID v7.
               uuidv7                
--------------------------------------
37b45c74-469d-7e1b-9397-1a971a99ab2b
(1 row)

At a minimum, it should reject a shift that creates a time later than a UUID v7 can represent.

> Time shifting would become a footgun if we throw an exception when overflown.

I don't understand why.  If the concern is that someone will pick a value that's close to the maximum, and get a surprising exception when the time overflows that, the right answer is to caution them not to do that rather than permit the wraparound.

And is anyone actually doing this?  Using a very large interval with a large enough number of shards that wraparound is a real possibility?  (In that case, I'd argue they should construct the 48 bit field directly rather than kind of dancing around it by using a time shift.)

---

From: Masahiko Sawada <[email-masked]>
Date: Wed, 27 May 2026 17:02:09 -0700

Hi,

> # select uuidv7('100000 years'::interval);   # ~11.2 x total time range in a UUID v7.

Fair point.

> At a minimum, it should reject a shift that creates a time later than a UUID v7 can represent.

I think that if we add a lower-bound check as the proposed patch does
an upper-bound check should also be added.

> I don't understand why.  If the concern is that someone will pick a value that's close to the maximum, and get a surprising exception when the time overflows that, the right answer is to caution th…

I guess that monotonicity could easily be violated depending on how
users shift the wall-clock. Taking Andrey's example, if they use
something like uuidv7('-10 years' * shard_id), the monotonicity would
be broken with just 6 shards.

I guess it would be safer to raise an error in such cases rather than
silently allowing wraparound. Otherwise, users might only realize that
their UUIDv7 values are no longer sortable years down the road, which
would be disastrous. Moreover, raising an error would be consistent
with how PostgreSQL natively handles timestamp + interval overflows.

That said, while I am leaning toward introducing boundary checks, we
should carefully consider this change since it could potentially break
existing applications that rely on the current behavior of
uuidv7(interval).

Regards,

-- 
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

---

From: Baji Shaik <[email-masked]>
Date: Wed, 27 May 2026 20:00:50 -0500

Hello Masahiko,

> with how PostgreSQL natively handles timestamp + interval overflows.

+1.  I ran into the same issue while testing, specifically,
uuidv7('infinity'::interval) overflows int64 during the epoch
conversion and produces a UUID with an incorrect timestamp.
There's no valid use case for infinity as a shift offset.

I have a small patch that adds a TIMESTAMP_NOT_FINITE check after
timestamptz_pl_interval(), which catches both infinity and
-infinity.  Happy to extend it to also cover the 48-bit upper/lower
bound checks if we agree on the direction.

Thanks,
Baji Shaik

---

From: Masahiko Sawada <[email-masked]>
Date: Thu, 11 Jun 2026 12:20:17 -0700

On Wed, May 27, 2026 at 6:01 PM Baji Shaik <[email-masked]> wrote:
>
> Hello Masahiko,
>
> On Wed, May 27, 2026 at 7:02 PM Masahiko Sawada <[email-masked]> wrote:
>>

Yeah, I think we should reject such cases at least.

> bound checks if we agree on the direction.

I think we should go ahead and add both upper and lower bound checks,
barring objections.

The current behavior silently produces UUIDs with nonsensical
timestamps when the interval causes the result to go before the Unix
epoch or beyond what 48 bits can represent. Regarding backward
compatibility: this change would affect applications using
uuidv7(interval) with values that cause the resulting timestamp to
fall outside the representable range. But I guess that in practice,
any such application is likely already getting incorrect results due
to wraparound, so the risk of breaking working use cases seems very
low.

Regards,

---

From: Baji Shaik <[email-masked]>
Date: Fri, 12 Jun 2026 17:34:59 -0500

> I think we should go ahead and add both upper and lower bound checks,

Thanks Masahiko. Here's a patch series that adds both boundary
checks along with the infinity check from my earlier patch:

  0001 - Reject timestamps before the Unix epoch (lower bound)
  0002 - Reject infinite intervals
  0003 - Reject timestamps beyond the 48-bit field limit (upper bound)

Christophe's original v1 covered the pre-epoch case; 0001 is
essentially the same fix with slightly different wording. I have
included it here so the series is self-contained and applies
cleanly on HEAD. Happy to drop it in favor of Christophe's
version if you prefer that.

The infinity check (0002) goes before the epoch conversion so
that uuidv7('infinity'::interval) gets a clear "infinite timestamps"
message rather than falling through to the pre-epoch check
with a confusing detail.

All three use ERRCODE_DATETIME_VALUE_OUT_OF_RANGE with errdetail.

Thanks,
Baji Shaik.

---

From: Baji Shaik <[email-masked]>
Date: Wed, 24 Jun 2026 10:25:11 -0500

>   0003 - Reject timestamps beyond the 48-bit field limit (upper bound)

Added to the commitfest: https://commitfest.postgresql.org/patch/6935/

---

From: Masahiko Sawada <[email-masked]>
Date: Wed, 24 Jun 2026 10:46:54 -0700

On Fri, Jun 12, 2026 at 3:35 PM Baji Shaik <[email-masked]> wrote:
>
> On Thu, Jun 11, 2026 at 2:20 PM Masahiko Sawada <[email-masked]> wrote:
>>

Thank you for creating the patches!

Here are some review comments:

+   /*
+    * Reject infinite intervals.  timestamptz_pl_interval() can produce an
+    * infinite timestamp when the input interval is infinite, and converting
+    * that to a Unix epoch value would overflow.
+    */
+   if (TIMESTAMP_NOT_FINITE(ts))
+       ereport(ERROR,
+               (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+                errmsg("timestamp out of range for UUID version 7"),
+                errdetail("UUID version 7 does not support infinite
timestamps.")));
+

I think we can do this check earlier, like before shifting the
timestamp, and we can mention in the doc that we don't accept
'infinity' and '-infinity' values.

---
+   /*
+    * The UUID version 7 timestamp field is 48 bits wide, storing
+    * milliseconds since the Unix epoch.  Reject timestamps that would
+    * overflow this field (dates beyond approximately year 10889).
+    */
+   if (us / US_PER_MS > (int64) 0xFFFFFFFFFFFF)
+       ereport(ERROR,
+               (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+                errmsg("timestamp out of range for UUID version 7"),
+                errdetail("UUID version 7 does not support timestamps
beyond approximately year 10889.")));

Please use INT64CONST() instead.

---
I think we need to mention in the doc that timestamp shifting beyond
the range UUIDv7 can support is not accepted.

Regards,

---

From: "Tristan Partin" <[email-masked]>
Date: Wed, 24 Jun 2026 17:58:02 +0000

> +-- uuidv7(interval) rejects timestamps before the Unix epoch
> +SELECT uuidv7('-1000 years'::interval);
> +ERROR:  timestamp out of range for UUID version 7
> +DETAIL:  UUID version 7 does not support timestamps before the Unix epoch.

You might want to steal the test in my patch. You never know if Postgres 
will be around in 3070, and then this test will start to fail. Slightly 
joking... lol

> +-- uuidv7(interval) rejects timestamps that overflow the 48-bit field
> +SELECT uuidv7('8920 years'::interval);
> +ERROR:  timestamp out of range for UUID version 7
> +DETAIL:  UUID version 7 does not support timestamps beyond approximately year 10889.

I think it might better to derive the interval similar to what I am 
suggesting in my first comment.

Neither comment should block merging, and feel free to completely ignore 
them. Just personal preference on my part.

Good work. Great minds think alike!

-- 
Tristan Partin
PostgreSQL Contributors Team
AWS (https://aws.amazon.com)

---

From: Christophe Pettus <[email-masked]>
Date: Wed, 24 Jun 2026 13:02:18 -0700

> On Jun 24, 2026, at 10:46, Masahiko Sawada <[email-masked]> wrote:

I believe the doc patch in my original patch included that admonition.  Please feel free to grab it if you'd like.

---

From: Zsolt Parragi <[email-masked]>
Date: Wed, 24 Jun 2026 13:31:53 -0700

Hello!

`us` can overflow and while it still results in an error, it is in the
wrong direction:

SELECT uuidv7('292230 years'::interval);
ERROR:  timestamp out of range for UUID version 7
DETAIL:  UUID version 7 does not support timestamps before the Unix epoch.