Re: UUID v7

On Wed, Mar 26, 2025 at 6:00 AM Andrey Borodin <x4mmm@yandex-team.ru> wrote:
>
>
>
> > On 26 Mar 2025, at 08:32, Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> >
> > Please review it.
>
> The patch looks good to me except one nit.
> +WITH uuidts AS (
> +     SELECT y, ts as ts, lag(ts) OVER (ORDER BY y) AS prev_ts
> +     FROM (SELECT y, uuid_extract_timestamp(uuidv7((y || ' years')::interval)) AS ts FROM generate_series(-50, 10889 - extract(year from now())::int) y)
> +)
> +SELECT y, ts, prev_ts FROM uuidts WHERE ts < prev_ts;
>
> if "extract(year from now())::int)" runs slightly before new year and the rest of the test after - the test will fail. How about avoiding overflow by using 10888 instead of 10889?

Agreed. I've done this in the attached patch.

> If we are sure citizen time never will go back, IMO we can safely move other border back to -55.

Yes. Or I think we can verify the range from 1970 to 10888 like I did
in the updated patch.

> Also the test is not proof to NTP time drift during New Year's edge, but it's hardly a problem. The test, NTP clock sync and New Year millisecond must coincide for a false failure.

Agreed.

Regards,

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