MIME-Version: 1.0
References: <799A70FA-6E5C-4118-99EB-2FBBE1CBAC54@thebuild.com>
 <966B5430-8ECD-48FA-B56F-22452D9CDBBF@yandex-team.ru>
 <E3B0C0EF-E9F3-4DCE-9F36-0C048E1D46D6@thebuild.com>
In-Reply-To: <E3B0C0EF-E9F3-4DCE-9F36-0C048E1D46D6@thebuild.com>
From: Masahiko Sawada <sawada.mshk@gmail.com>
Date: Wed, 27 May 2026 17:02:09 -0700
Message-ID: 
 <CAD21AoAJN-Yb1mP5W-95UWQNMPgcE6OzCyJLKJRqTRrU5WxH5Q@mail.gmail.com>
Subject: Re: uuidv7 improperly accepts dates before 1970-01-01
To: Christophe Pettus <xof@thebuild.com>
Cc: Andrey Borodin <x4mmm@yandex-team.ru>, pgsql-hackers@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAD21AoAJN-Yb1mP5W-95UWQNMPgcE6OzCyJLKJRqTRrU5WxH5Q%40mail.gmail.com>
Precedence: bulk

Hi,

On Mon, Apr 27, 2026 at 3:51=E2=80=AFPM Christophe Pettus <xof@thebuild.com=
> wrote:
>
> > We wrote the specific test that ensures vast space for shift, but not u=
nlimited.
>
> That's another problem: the API gives the impression of a much larger spa=
ce than actually exists.
>
> # select uuidv7('100000 years'::interval);   # ~11.2 x total time range i=
n 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 U=
UID 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 over=
flown.
>
> 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 t=
han 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,

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