Horton

Re: UUID v7

x4mmm@yandex-team.ru

View thread View on postgr.es
From: Andrey Borodin <x4mmm@yandex-team.ru>
To: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: Daniel Verite <daniel@manitou-mail.org>, Peter Eisentraut <peter@eisentraut.org>, Jelte Fennema-Nio <postgres@jeltef.nl>, Sergey Prokhorenko <sergeyprokhorenko@yahoo.com.au>, Przemysław Sztoch <przemyslaw@sztoch.pl>, Michael Paquier <michael@paquier.xyz>, Aleksander Alekseev <aleksander@timescale.com>, Pgsql-Hackers Mailing List <pgsql-hackers@postgresql.org>, "David G. Johnston" <david.g.johnston@gmail.com>, Mat Arye <mat@timescaledb.com>, Matthias van de Meent <boekewurm+postgres@gmail.com>, Nikolay Samokhvalov <samokhvalov@gmail.com>, Junwang Zhao <zhjwpku@gmail.com>, Stepan Neretin <sncfmgg@gmail.com>
Date: 2025-01-30T08:59:43Z
Lists: pgsql-hackers 

> 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.

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.

Commits

  1. Fix timestamp overflow in UUIDv7 implementation.

  2. Add UUID version 7 generation function.

  3. Add some UUID support functions