Feedback-ID: idd01497b:Fastmail
Mime-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset=UTF-8
Date: Wed, 24 Jun 2026 17:58:02 +0000
Message-Id: <DJHH346K16FS.38D834OT4ERQV@partin.io>
Subject: Re: uuidv7 improperly accepts dates before 1970-01-01
To: "Baji Shaik" <baji.pgdev@gmail.com>
From: "Tristan Partin" <tristan@partin.io>
Cc: "Christophe Pettus" <xof@thebuild.com>, "Andrey Borodin"
 <x4mmm@yandex-team.ru>, <pgsql-hackers@lists.postgresql.org>, "Masahiko
 Sawada" <sawada.mshk@gmail.com>
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>
Archived-At: 
 <https://www.postgresql.org/message-id/DJHH346K16FS.38D834OT4ERQV%40partin.io>
Precedence: bulk

On Fri Jun 12, 2026 at 10:35 PM UTC, Baji Shaik wrote:
> 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.
>
> Thanks,
> Baji Shaik.

> +-- uuidv7(interval) rejects timestamps before the Unix epoch
> +SELECT uuidv7('-1000 years'::interval);
> +ERROR:  timestamp out of range for UUID version 7
> +DETAIL:  UUID version 7 does not support timestamps before the Unix epoc=
h.

You might want to steal the test in my patch. You never know if Postgres=20
will be around in 3070, and then this test will start to fail. Slightly=20
joking... lol

> +-- uuidv7(interval) rejects timestamps that overflow the 48-bit field
> +SELECT uuidv7('8920 years'::interval);
> +ERROR:  timestamp out of range for UUID version 7
> +DETAIL:  UUID version 7 does not support timestamps beyond approximately=
 year 10889.

I think it might better to derive the interval similar to what I am=20
suggesting in my first comment.

Neither comment should block merging, and feel free to completely ignore=20
them. Just personal preference on my part.

Good work. Great minds think alike!

--=20
Tristan Partin
PostgreSQL Contributors Team
AWS (https://aws.amazon.com)