uuidv7 improperly accepts dates before 1970-01-01

On 25 Apr 2026, at 05:19, Christophe Pettus <xof@thebuild.com> wrote:

"Don't do that" is one answer, but for good hygiene, here's a patch that adds a < 0 check and a regression test.

Hi Christophe!

We intentionally left ability to overflow unix_ts_ms bits. In some cases one might want to
intentionally break time locality by using construction like SELECT uuidv7(INTERVAL '1000 years' * shard_id);
This will give time locality for UUIDs generated on each shard. We consulted with RFC authors
about this feature, and they confirmed that shifting time is compliant with RFC wording.
We wrote the specific test that ensures vast space for shift, but not unlimited.

Time shifting would become a footgun if we throw an exception when overflown.
If you use SELECT uuidv7(INTERVAL '-1000 years'); for generating identifiers, they will still be unique and
time-local, and more over - they will be ascending for a single backend. So no documented guarantees
are broken.

Thank you!

Best regards, Andrey Borodin.

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 <x4mmm@yandex-team.ru> 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.)

Hi,

On Mon, Apr 27, 2026 at 3:51 PM Christophe Pettus <xof@thebuild.com> wrote:

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)

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.

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.

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

Hello Masahiko,

On Wed, May 27, 2026 at 7:02 PM Masahiko Sawada <sawada.mshk@gmail.com>
wrote:

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.

+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

On Wed, May 27, 2026 at 6:01 PM Baji Shaik <baji.pgdev@gmail.com> wrote:

Hello Masahiko,

On Wed, May 27, 2026 at 7:02 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:

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.

+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.

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

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.

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,

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

On Thu, Jun 11, 2026 at 2:20 PM Masahiko Sawada <sawada.mshk@gmail.com>
wrote:

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

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.