Re: UUID v7

Andrey M. Borodin wrote on 1/4/2024 7:20 PM:

Hello Przemysław,

thanks for your interest in this patch!

On 3 Jan 2024, at 04:37, Przemysław Sztoch <przemyslaw@sztoch.pl> wrote:

1. Is it possible to add a function that returns the version of the generated uuid?
It will be very useful. 
I don't know if it's possible, but I think there are bits in the UUID that inform about the version.

What do you think if we have functions get_uuid_v7_ver(uuid) and get_uuid_v7_var(uuid) to extract bit fields according to [0] ? Or, perhaps, this should be one function with two return parameters?
It's not in a patch yet, I'm just considering how this functionality should look like.

uuid_ver(uuid) -> smallint/integer 1/3/4/5/6/7/8

Of course there is RFC 4122 Variant "bits: 10x". If it is other variant then uuid_ver should return -1 OR NULL.
For UUIDs generated by your patch this function should always return 7.

2. If there is any doubt about adding the function to the main sources (standard development in progress), in my opinion you can definitely add this function to the uuid-ossp extension.

From my POV we can just have this function in the core. OSSP support for UUID seems more or less dead [1]: "Newsflash: 04-Jul-2008: Released OSSP uuid 1.6.2". Or do I look into wrong place?

After two days of thinking about UUID v7, I consider it a very important functionality that should be included in PG17.

3. Wouldn't it be worth including UUID version 6 as well?

The standard in [0] says "Systems that do not involve legacy UUIDv1 SHOULD use UUIDv7 Section 5.7 instead." If there's a point in developing v6 - I'm OK to do so.

IETF standard should provide information about possibility of conversion from v1 to v6.
Then the usefulness of v6 is much greater and it would be worth implementing this version as well.

4. Sometimes you will need to generate a uuid for historical time. There should be an additional function gen_uuid_v7(timestamp).

Done, please see patch attached. But I changed signature to gen_uuid_v7(int8), to avoid messing with bytes from user who knows what they want. Or do you think gen_uuid_v7(timestamp) would be more convenient?

I talked to my colleagues and everyone chooses the timestamp version.
If timestamp is outside the allowed range, the function must return an error.

We also talked about uuid-ossp. Still, v5 is a great solution in some applications.
It is worth moving this function from extension to PG17. Many people don't use it because they don't know it and this uuid schema.

We think it would be quite reasonable to add:
uuid_generate_v5 (namespace uuid, name text) -> uuid
uuid_generate_v6 () -> uuid
uuid_generate_v6 (timestamptz) -> uuid
uuid_generate_v7() -> uuid
uuid_generate_v7(timestamptz) -> uuid
uuid_ver(uuid) -> smallint -1/1/2/3/4/5/6/7/8
uuid_ts(uuid) -> timestamptz (for 1/6/7 version, for other should return NULL, error is too heavy in our opinion)
uuid_v1_to_v6 (uuid) -> uuid

The naming of this family of functions needs to be rethought.
Do we adopt the naming standard from Postgres and the uuid-ossp extension?
Or should we continue with a slightly less accurate name for PG: get_random_uuid (get_random_uuid, get_uuid_v7)?

5. Please add in docs reference to RFC4122 (https://datatracker.ietf.org/doc/html/draft-ietf-uuidrev-rfc4122bis-14#uuid)
People should read standards. :-)

Thanks!

Best regards, Andrey Borodin.


[0] https://datatracker.ietf.org/doc/html/draft-ietf-uuidrev-rfc4122bis-14#uuidv7
[1] http://www.ossp.org/