Re: UUID v7

From: Andrey Borodin <x4mmm(at)yandex-team(dot)ru>
To: Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>
Cc: Daniel Verite <daniel(at)manitou-mail(dot)org>, Peter Eisentraut <peter(at)eisentraut(dot)org>, Jelte Fennema-Nio <postgres(at)jeltef(dot)nl>, Sergey Prokhorenko <sergeyprokhorenko(at)yahoo(dot)com(dot)au>, Przemysล‚aw Sztoch <przemyslaw(at)sztoch(dot)pl>, Michael Paquier <michael(at)paquier(dot)xyz>, Aleksander Alekseev <aleksander(at)timescale(dot)com>, Pgsql-Hackers Mailing List <pgsql-hackers(at)postgresql(dot)org>, "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, Mat Arye <mat(at)timescaledb(dot)com>, Matthias van de Meent <boekewurm+postgres(at)gmail(dot)com>, Nikolay Samokhvalov <samokhvalov(at)gmail(dot)com>, Junwang Zhao <zhjwpku(at)gmail(dot)com>, Stepan Neretin <sncfmgg(at)gmail(dot)com>
Subject: Re: UUID v7
Date: 2025-01-30 08:59:43
Message-ID: 96DEC2D9-659A-40E8-B7BA-AF5D162A9E21@yandex-team.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

> On 12 Dec 2024, at 23:08, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>
> Pushed

Hi Masahiko!

Iโ€™ve found some inconsistency in handling of overflow. Iโ€™m not sure we should handle it, but anyway.

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.

Can we use int128 in code? Or, perhaps, carry this extra 10 bits in the extra argument of generate_uuidv7()? Or, perhaps, leave things as they stand now?

Thanks!

Best regards, Andrey Borodin.

In response to

  • Re: UUID v7 at 2024-12-12 18:08:06 from Masahiko Sawada

Responses

  • Re: UUID v7 at 2025-01-30 19:54:59 from Masahiko Sawada

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Kapila 2025-01-30 10:31:55 Re: Improve error handling for invalid slots and ensure a same 'inactive_since' time for inactive slots
Previous Message Alexander Pyhalov 2025-01-30 08:50:35 Re: SQLFunctionCache and generic plans