Re: UUID v7 - Mailing list pgsql-hackers

From Aleksander Alekseev
Subject Re: UUID v7
Date
Msg-id CAJ7c6TPcYQHWYa97MiETT6O8=3Yq=r0CMtyu0y3DEYmOcmnmPA@mail.gmail.com
Whole thread Raw
In response to Re: UUID v7  (Andrey Borodin <x4mmm@yandex-team.ru>)
Responses Re: UUID v7
Re: UUID v7
List pgsql-hackers
Hi Andrey,

> Sergey, Przemysław, Jelte, thanks for your feedback.
> Here's v9. Changes:
> 1. Swapped type of the argument to timestamptz in gen_uuid_v7()
> 2. Renamed get_uuid_v7_time() to uuid_v7_time()
> 3. Added  uuid_ver() and uuid_var().
>
> What do you think?

Many thanks for the updated patch. It's an important work and I very
much hope we will see this in the upcoming PG release.

```
+Datum
+pg_node_tree_in(PG_FUNCTION_ARGS)
+{
+    if (!IsBootstrapProcessingMode())
+        elog(ERROR, "cannot accept a value of type pg_node_tree_in");
+    return textin(fcinfo);
+}
```

Not 100% sure what this is for. Any chance this could be part of another patch?

One thing I don't particularly like about the tests is the fact that
they don't check if a correct UUID was actually generated. I realize
that's not quite trivial due to the random nature of the function, but
maybe we could use some substring/regex magic here? Something like:

```
select gen_uuid_v7() :: text ~ '^[0-9a-f]{8}-([0-9a-f]{4}-){3}[0-9a-f]{12}$';
 ?column?
----------
 t

select regexp_replace(gen_uuid_v7('2024-01-16 15:45:33 MSK') :: text,
'[0-9a-f]{4}-[0-9a-f]{12}$', 'XXXX-' || repeat('X', 12));
            regexp_replace
--------------------------------------
 018d124e-39c8-74c7-XXXX-XXXXXXXXXXXX
```


```
+  proname => 'uuid_v7_time', proleakproof => 't', provolatile => 'i',
```

I don't think we conventionally specify IMMUTABLE volatility, it's the
default. Other values also are worth checking.

Another question: how did you choose between using TimestampTz and
Timestamp types? I realize that internally it's all the same. Maybe
Timestamp will be slightly better since the way it is displayed
doesn't depend on the session settings. Many people I talked to find
this part of TimestampTz confusing.

Also I would like to point out that part of the documentation is
missing, but I guess at this stage of the game it's OK.

Last but not least: maybe we should support casting Timestamp[Tz] to
UUIDv7 and vice versa? Shouldn't be difficult to implement and I
suspect somebody will request this eventually. During the cast to UUID
we will always get the same value for the given Timestamp[Tz], which
probably can be useful in certain applications. It can't be done with
gen_uuid_v7() and its volatility doesn't permit it.

--
Best regards,
Aleksander Alekseev



pgsql-hackers by date:

Previous
From: Jelte Fennema-Nio
Date:
Subject: Re: [PATCH] Add additional extended protocol commands to psql: \parse and \bindx
Next
From: "David G. Johnston"
Date:
Subject: Re: Postgres Database Service Interruption