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>
 <CAD21AoAJN-Yb1mP5W-95UWQNMPgcE6OzCyJLKJRqTRrU5WxH5Q@mail.gmail.com>
 <CA+fm-ROd4cNKM524n6EdgtZ9xOzOHJDNv8J_9Mvr2+2t1qWSDw@mail.gmail.com>
In-Reply-To: 
 <CA+fm-ROd4cNKM524n6EdgtZ9xOzOHJDNv8J_9Mvr2+2t1qWSDw@mail.gmail.com>
From: Masahiko Sawada <sawada.mshk@gmail.com>
Date: Thu, 11 Jun 2026 12:20:17 -0700
Message-ID: 
 <CAD21AoBpxjp3wjQ4dia0c76Bp8E=Q4UOgBTU4ZOn3Sj6k=-SsA@mail.gmail.com>
Subject: Re: uuidv7 improperly accepts dates before 1970-01-01
To: Baji Shaik <baji.pgdev@gmail.com>
Cc: Christophe Pettus <xof@thebuild.com>,
 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/CAD21AoBpxjp3wjQ4dia0c76Bp8E%3DQ4UOgBTU4ZOn3Sj6k%3D-SsA%40mail.gmail.com>
Precedence: bulk

On Wed, May 27, 2026 at 6:01=E2=80=AFPM Baji Shaik <baji.pgdev@gmail.com> w=
rote:
>
> Hello Masahiko,
>
> On Wed, May 27, 2026 at 7:02=E2=80=AFPM Masahiko Sawada <sawada.mshk@gmai=
l.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,

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