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

On Fri, Jul 3, 2026 at 4:54 PM Baji Shaik <baji(dot)pgdev(at)gmail(dot)com> wrote:
>
> On Fri, Jul 3, 2026 at 2:54 PM Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com> wrote:
>>
>> There's no overflow anymore with the current patch, so that comment is
>> somewhat stale. Maybe it could be simply "large future intervals are
>> rejected"?
>
>
> Thanks Zsolt! Good catch. Updated the test comment in v5 attached.
>

Thank you for updating the patch!

I've reviewed the patch and have some comments:

+#define UUIDV7_EPOCH_OFFSET \
+ ((int64) (POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY
* USECS_PER_SEC)

I guess we don't need to name it with the prefix UUIDV7 as the offset
value is actually not limited to UUIDv7. How about renaming it to
PG_UNIX_EPOCH_OFFSET_US?

---
+-- uuidv7: large future intervals are rejected
+SELECT uuidv7('292230 years'::interval);

Since the highest year of a timestamp value is 294276AD, this test
will fail in the year 2047. Given that the upper-bound rejection is
tested by uuidv7('9000 years'::interval), it seems redundant to me.
Instead, how about adding a test verifying that a large but in-range
forward shift is accepted?

+-- uuidv7: a large but in-range forward shift is accepted
+SELECT uuid_extract_timestamp(uuidv7('1000 years'::interval)) > now()
+ '999 years'::interval;

I've merged two patches into one and made some cosmetic changes and
the above suggestions. I'm going to push it, barring objections.

Regards,

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