MIME-Version: 1.0
References: 
 <CA+fm-RN4eMEr2tzZU_mAV-=WfdmPXJ4Ea_GpmSS8=yStSy8onQ@mail.gmail.com>
 <CAN4CZFP9XsTXijqxtK7v9Hgad3jQN-m+wBve0azdi3QkheJv7Q@mail.gmail.com>
 <CA+fm-RPjp8fC-w50PApyhrfMqY4SA0zT5s1Nq=+03+ALcf+3+A@mail.gmail.com>
 <20260625.111954.696627555349573162.horikyota.ntt@gmail.com>
 <CA+fm-RN9UgAG2ew7T0Gum8RtZ4ZbyYvOLyyqAiuTWhsGEn3_zw@mail.gmail.com>
In-Reply-To: 
 <CA+fm-RN9UgAG2ew7T0Gum8RtZ4ZbyYvOLyyqAiuTWhsGEn3_zw@mail.gmail.com>
From: Masahiko Sawada <sawada.mshk@gmail.com>
Date: Mon, 29 Jun 2026 16:18:08 -0700
Message-ID: 
 <CAD21AoDAx5ArZ_Fpp1u6VmPS26GsqBB_PEaf9yF4U9U+fd3yBQ@mail.gmail.com>
Subject: Re: uuidv7 improperly accepts dates before 1970-01-01
To: Baji Shaik <baji.pgdev@gmail.com>
Cc: Kyotaro Horiguchi <horikyota.ntt@gmail.com>, zsolt.parragi@percona.com,
	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/CAD21AoDAx5ArZ_Fpp1u6VmPS26GsqBB_PEaf9yF4U9U%2Bfd3yBQ%40mail.gmail.com>
Precedence: bulk

On Thu, Jun 25, 2026 at 6:47=E2=80=AFAM Baji Shaik <baji.pgdev@gmail.com> w=
rote:
>
> On Wed, Jun 24, 2026 at 9:19=E2=80=AFPM Kyotaro Horiguchi <horikyota.ntt@=
gmail.com> wrote:
>>
>> From a user's perspective, it seems sufficient to know that the
>> shifted timestamp falls outside the range supported by UUID v7. As a
>> translator, I'm not particularly enthusiastic about adding more
>> message variants when the distinction is not particularly useful to
>> users.
>
>
> Thanks for the feedback, Kyotaro. Good point. Attached v3 with all bounda=
ry checks
> using a single shared errdetail:
>
>  "UUID version 7 supports timestamps from 1970-01-01 to approximately yea=
r 10889."
>
> 0001 - Reject infinite intervals
> 0002 - Reject pre-epoch timestamps (with overflow-safe epoch conversion)
> 0003 - Reject timestamps beyond the 48-bit limit
>
> I prefer keeping them as 3 patches since each addresses a distinct
> failure mode and is easier to review/bisect independently. That said,
> since 0002 and 0003 now share the same errdetail and are logically
> the same validation (timestamp outside valid range), I'm happy to
> merge them into one patch for v4 if preferred.
>

Thank you for updating the patch! I have one comment:

+   /*
+    * Convert a TimestampTz value back to a UNIX epoch timestamp.  Use
+    * overflow-safe addition since large intervals can exceed int64 range.
+    */
+   if (pg_add_s64_overflow(ts,
+                           (int64) (POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDAT=
E) *
+                           SECS_PER_DAY * USECS_PER_SEC,
+                           &us))
+       ereport(ERROR,
+               (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+                errmsg("timestamp out of range for UUID version 7"),
+                errdetail("UUID version 7 supports timestamps from
1970-01-01 to approximately year 10889.")));

It does the range validation on the Unix-epoch value, after converting
back from the shifted PostgreSQL-epoch timestamp. Can we compare
pre-compute the Unix-epoch timestamp resentable window in
PostgreSQL-epoch units against the shifted PostgreSQL-epoch timestamp
before converting it back to the Unix epoch? That way, we don't need
to worry about the overflow.

---
+   /* UUID v7 uses an unsigned 48-bit millisecond field; reject pre-epoch =
*/
+   if (us < 0)
+       ereport(ERROR,
+               (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+                errmsg("timestamp out of range for UUID version 7"),
+                errdetail("UUID version 7 supports timestamps from
1970-01-01 to approximately year 10889.")));
+
+   /* Reject timestamps beyond the 48-bit millisecond field maximum */
+   if (us / US_PER_MS > (INT64CONST(1) << 48) - 1)
+       ereport(ERROR,
+               (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+                errmsg("timestamp out of range for UUID version 7"),
+                errdetail("UUID version 7 supports timestamps from
1970-01-01 to approximately year 10889.")));

Let's merge these two if statements as they use the same error message.

Regards,

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