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>
 <CAD21AoBpxjp3wjQ4dia0c76Bp8E=Q4UOgBTU4ZOn3Sj6k=-SsA@mail.gmail.com>
 <CA+fm-RN4eMEr2tzZU_mAV-=WfdmPXJ4Ea_GpmSS8=yStSy8onQ@mail.gmail.com>
In-Reply-To: 
 <CA+fm-RN4eMEr2tzZU_mAV-=WfdmPXJ4Ea_GpmSS8=yStSy8onQ@mail.gmail.com>
From: Masahiko Sawada <sawada.mshk@gmail.com>
Date: Wed, 24 Jun 2026 10:46:54 -0700
Message-ID: 
 <CAD21AoBpCg6u8wzQ3hGLbxsrn6sBkkdBXuZkgpEH+rZRSxLn6g@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/CAD21AoBpCg6u8wzQ3hGLbxsrn6sBkkdBXuZkgpEH%2BrZRSxLn6g%40mail.gmail.com>
Precedence: bulk

On Fri, Jun 12, 2026 at 3:35=E2=80=AFPM Baji Shaik <baji.pgdev@gmail.com> w=
rote:
>
> On Thu, Jun 11, 2026 at 2:20=E2=80=AFPM Masahiko Sawada <sawada.mshk@gmai=
l.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.

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 converti=
ng
+    * 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,

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