Re: UUID v7

On Thu, Jan 30, 2025 at 12:59 AM Andrey Borodin <x4mmm@yandex-team.ru> wrote:
>
>
>
> > On 12 Dec 2024, at 23:08, Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> >
> > Pushed
>
> Hi Masahiko!
>
> I’ve found some inconsistency in handling of overflow. I’m not sure we should handle it, but anyway.

Thank you for the report!

>
> postgres=# select x,
>         uuid_extract_timestamp(uuidv7((x::text || ' year'::text)::interval)),
>         (x::text || ' year'::text)::interval
> from generate_series(237,238) x;;
>   x  |   uuid_extract_timestamp    | interval
> -----+-----------------------------+-----------
>  237 | 2262-01-30 13:43:23.737+05  | 237 years
>  238 | 10598-02-10 19:41:13.736+05 | 238 years
> (2 rows)
>
> The thing is per RFC we represent time as number of nanoseconds since UNIX epoch. And we use int64, which will overflow in year 2262. I sincerely wish us to see this great year.
> We can have a couple more centuries if we resort to unsigned int 64.
>
> But it would be great to make our code work until
>
> postgres=# select uuid_extract_timestamp('FFFFFFFF-FFFF-7FFF-bFFF-FFFFFFFFFFFF');
>    uuid_extract_timestamp
> -----------------------------
>  10889-08-02 10:31:50.655+05
> (1 row)
>
> And using uint64 won’t help us.

I don't think using uint64 instead of int64 for nanoseconds doesn't
resolve the problem. We will not be able to shift the timestamp for a
date before 1970/1/1.

> Or, perhaps, carry this extra 10 bits in the extra argument of generate_uuidv7()?

I like this idea. Would you like to write a patch, or shall I?

Regards,

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