Re: uuidv7 improperly accepts dates before 1970-01-01

On Thu, Jun 25, 2026 at 6:47 AM Baji Shaik <baji(dot)pgdev(at)gmail(dot)com> wrote:
>
> On Wed, Jun 24, 2026 at 9:19 PM Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)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 boundary checks
> using a single shared errdetail:
>
> "UUID version 7 supports timestamps from 1970-01-01 to approximately year 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_JDATE) *
+ 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