Subject: Re: UUID v7

From: Sergey Prokhorenko <[email-masked]>
Date: Fri, 5 Jan 2024 10:57:47 +0000 (UTC)

Hello Przemysław and Andrey,
When naming functions, I would advise using the shorter abbreviation uuidv7 from the new version of the RFC instead of uuid_v7. When people search Google for new versions of UUIDs, they enter the abbreviation uuidv7 into the search bar. The name generate_uuidv7() looks good, as well as uuidv1_to_uuidv6() and timestamp_to_uuidv7().
Best regards,

Sergey [email-masked] 

    On Friday, 5 January 2024 at 11:53:04 am GMT+3, Przemysław Sztoch <[email-masked]> wrote:  
 
 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 <[email-masked]> 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/

-- 
Przemysław Sztoch | Mobile +48 509 99 00 66

---

From: Andrey Borodin <[email-masked]>
Date: Tue, 16 Jan 2024 17:15:07 +0500

> On 5 Jan 2024, at 15:57, Sergey Prokhorenko <[email-masked]> wrote:

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?

---

From: Aleksander Alekseev <[email-masked]>
Date: Tue, 16 Jan 2024 16:00:57 +0300

Hi Andrey,

> 3. Added  uuid_ver() and uuid_var().

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

---

From: "Andrey M. Borodin" <[email-masked]>
Date: Tue, 16 Jan 2024 19:44:33 +0500

Thanks for your review, Aleksander!

> On 16 Jan 2024, at 18:00, Aleksander Alekseev <[email-masked]> wrote:
> 
> 
> ```
> +Datum
Nope, it’s necessary there. Without these changes catalog functions cannot have defaults for arguments. These defaults have type pg_node_tree which has no-op in function.

> select gen_uuid_v7() :: text ~ '^[0-9a-f]{8}-([0-9a-f]{4}-){3}[0-9a-f]{12}$';
Any 8 bytes which have ver and var bits (6 bits total) are correct UUID.
This is checked by tests when uuid_var() and uuid_ver() functions are exercised.

> default. Other values also are worth checking.
Makes sense, I’ll drop this values in next version.
BTW I’m in doubt if provided functions are leakproof. They ERROR-out with messages that can give a clue about several bits of UUID. Does this break leakproofness? I think yest, but I’m not sure.
gen_uuid_v7() seems leakproof to me.

> this part of TimestampTz confusing.

I mean, this argument is expected to be used to implement K-way sorted identifiers. In this context, it seems to me, it’s good to remember to developer that time shift also depend on timezones.
But this is too vague.
Do you have any reasons that apply to UUID generation?

> gen_uuid_v7() and its volatility doesn't permit it.
I’m strongly opposed to doing this cast. I was not adding this function to extract timestamp from UUID, because standard does not recommend it. But a lot of people asked for this.
But supporting easy way to do unrecommended thing seem bad.

---

From: Sergey Prokhorenko <[email-masked]>
Date: Tue, 16 Jan 2024 16:49:13 +0000 (UTC)

Andrey,

It is not clear how to interpret uuid_v7_time():   
   - uuid_v7 to time() (extracting the timestamp)
   - time() to uuid_v7  (generation of the uuid_v7)
 It is worth improving the naming, for example, adding prepositions.

    On Tuesday, 16 January 2024 at 05:44:51 pm GMT+3, Andrey M. Borodin <[email-masked]> wrote:

---

From: Jelte Fennema-Nio <[email-masked]>
Date: Tue, 16 Jan 2024 21:10:37 +0100

> Nope, it’s necessary there. Without these changes catalog functions cannot have defaults for arguments. These defaults have type pg_node_tree which has no-op in function.

That seems like the wrong way to make that work then. How about
instead we define the same function name twice, once with and once
without a timestamp argument. That's how this is done for other
functions that are overloaded in pg_catalog.

---

From: Przemysław Sztoch <[email-masked]>
Date: Tue, 16 Jan 2024 21:20:58 +0100

> 1. Swapped type of the argument to timestamptz in gen_uuid_v7()
Please update docs part about optional timestamp argument.
> 2. Renamed get_uuid_v7_time() to uuid_v7_time()
Pleaserename uuid_v7_time to uuid_time() and add support for v1 and v6.
If version is incompatible then return NULL.
> 3. Added  uuid_ver() and uuid_var().
Looks good.
But for me, throwing an error is problematic. Wouldn't it be better to 
return -1.

---

From: Przemysław Sztoch <[email-masked]>
Date: Tue, 16 Jan 2024 22:09:56 +0100

> this part of TimestampTz confusing.
timstamptz internally always store UTC.
I believe that in SQL, when operating with time in UTC, you should 
always use timestamptz.
timestamp is theoretically the same thing. But internally it does not 
convert time to UTC and will lead to incorrect use.
-- 
Przemysław Sztoch | Mobile +48 509 99 00 66

---

From: Aleksander Alekseev <[email-masked]>
Date: Thu, 18 Jan 2024 17:20:49 +0300

Hi,

> I believe that in SQL, when operating with time in UTC, you should always use timestamptz.
> timestamp is theoretically the same thing. But internally it does not convert time to UTC and will lead to incorrect use.

No.

Timestamp and TimestampTz are absolutely the same thing. The only
difference is how they are shown to the user. TimestampTz uses session
context in order to be displayed in the TZ chosen by the user. Thus
typically it is somewhat more confusing to the users and thus I asked
whether there was a good reason to choose TimestampTz over Timestamp.

-- 
Best regards,
Aleksander Alekseev

---

From: Andrey Borodin <[email-masked]>
Date: Thu, 18 Jan 2024 20:39:43 +0500

> On 18 Jan 2024, at 19:20, Aleksander Alekseev <[email-masked]> wrote:
> 
> Timestamp and TimestampTz are absolutely the same thing.
My question is not about Postgres data types. I'm asking about examples in the standard.

There's an example 017F22E2-79B0-7CC3-98C4-DC0C0C07398F. It is expected to be generated on "Tuesday, February 22, 2022 2:22:22.00 PM GMT-05:00".
It's exaplained to be 164555774200000ns after 1582-10-15 00:00:00 UTC.

But 164555774200000ns after 1582-10-15 00:00:00 UTC  was  2022-02-22 19:22:22 UTC. And that was 2022-02-23 00:22:22 in UTC-05.

---

From: Aleksander Alekseev <[email-masked]>
Date: Thu, 18 Jan 2024 19:21:52 +0300

Hi Andrey,

> But 164555774200000ns after 1582-10-15 00:00:00 UTC  was  2022-02-22 19:22:22 UTC. And that was 2022-02-23 00:22:22 in UTC-05.

Not 100% sure which text you are referring to exactly, but I'm
guessing it's section B.2 of [1]

"""
This example UUIDv7 test vector utilizes a well-known 32 bit Unix
epoch with additional millisecond precision to fill the first 48 bits
[...]
The timestamp is Tuesday, February 22, 2022 2:22:22.00 PM GMT-05:00
represented as 0x17F22E279B0 or 1645557742000
"""

If this is the case, I think the example is indeed wrong:

```
=# select extract(epoch from 'Tuesday, February 22, 2022 2:22:22.00 PM
GMT-05:00' :: timestamptz)*1000;
       ?column?
----------------------
 1645521742000.000000
(1 row)
```

And the difference between the value in the text and the actual value
is 10 hours as you pointed out.

Also you named the date 1582-10-15 00:00:00 UTC. Maybe you actually
meant 1970-01-01 00:00:00 UTC?

[1]: https://www.ietf.org/archive/id/draft-peabody-dispatch-new-uuid-format-04.html

-- 
Best regards,
Aleksander Alekseev

---

From: Sergey Prokhorenko <[email-masked]>
Date: Thu, 18 Jan 2024 18:28:10 +0000 (UTC)

Hi Andrey,

Aleksander Alekseev wrote: "If this is the case, I think the example is indeed wrong". 

This is one of the reasons why I was categorically against any examples of implementation in the new RFC. The examples have been very poorly studied and discussed, and therefore it is better not to use them at all. But the text of the RFC itself clearly refers to UTC, and not at all about local time: "UUID version 7 features a time-ordered value field derived from the widely implemented and well known Unix Epoch timestamp source, the number of milliseconds since midnight 1 Jan 1970 UTC, leap seconds excluded". The main reason for using UTC is so that UUIDv7's, generated approximately simultaneously in different time zones, are correctly ordered in time when they get into one database.

Sergey Prokhorenko
[email-masked] 

    On Thursday, 18 January 2024 at 07:22:05 pm GMT+3, Aleksander Alekseev <[email-masked]> wrote:  
 
 Hi Andrey,

"""

```

-- 
Best regards,
Aleksander Alekseev

---

From: Andrey Borodin <[email-masked]>
Date: Thu, 18 Jan 2024 13:31:10 -0500

> On 18 Jan 2024, at 20:39, Andrey Borodin <[email-masked]> wrote:
> 

'2022-02-22 19:22:22 UTC' is exactly that moment which was encoded into example UUIDs. It's not '2022-02-23 00:22:22 in UTC-05' as I thought.
I got confused by "at timezone" changes which in fact removes timezone information. And that's per SQL standard...

Now I'm completely lost in time... I've set local time to NY (UTC-5).

postgres=# select TIMESTAMP WITH TIME ZONE '2022-02-22 14:22:22-05' - TIMESTAMP WITH TIME ZONE 'Tuesday, February 22, 2022 2:22:22.00 PM GMT-05:00';
 ?column? 
----------
 10:00:00
(1 row)

postgres=# select TIMESTAMP WITH TIME ZONE 'Tuesday, February 22, 2022 2:22:22.00 PM GMT-05:00';
      timestamptz       
------------------------
 2022-02-22 04:22:22-05
(1 row)

I cannot wrap my mind around it... Any pointers would be appreciated.
I'm certain that code extracted UTC time correctly, I just want a reliable test that verifies timestamp constant (+ I understand what is going on).

---

From: Sergey Prokhorenko <[email-masked]>
Date: Thu, 18 Jan 2024 18:59:38 +0000 (UTC)

Hi Andrey,

You'd better generate a test UUIDv7 for midnight 1 Jan 1970 UTC. In this case, the timestamp in UUIDv7 according to the new RFC must be filled with zeros. By extracting the timestamp from this test UUIDv7, you should get exactly midnight 1 Jan 1970 UTC.
I also recommend this article: https://habr.com/ru/articles/772954/

Sergey Prokhorenko

---

From: Przemysław Sztoch <[email-masked]>
Date: Thu, 18 Jan 2024 21:39:41 +0100

> whether there was a good reason to choose TimestampTz over Timestamp.

Theoretically, you're right. But look at this example:

SET timezone TO 'Europe/Warsaw';
SELECT extract(epoch from '2024-01-18 9:27:30'::timestamp), 
extract(epoch from '2024-01-18 9:27:30'::timestamptz);

  date_part  | date_part
------------+------------
  1705570050 | 1705566450
(1 row)

In my opinion, timestamptz gives greater guarantees that the time 
internally is in UTC and the user gets the time in his/her time zone.

In the case of timestamp, it is never certain whether it keeps time in 
UTC or in the local zone.

In the case of argument's type, there would be no problem because we 
could create two functions.
Of course timestamp would be treated the same as timestamptz.
But here we have a problem with the function return type, which can only 
be one. And since the time returned is in UTC, it should be timestamptz.

---

From: Przemysław Sztoch <[email-masked]>
Date: Thu, 18 Jan 2024 21:49:26 +0100

We are not allowed to consider any time other than UTC.

You need to write to the authors of the standard. I suppose this is a 
mistake.

I know from experience that errors in such standards most often appear 
in examples.
Nobody detects them at first.
Everyone reads and checks ideas, not calculations.
Then developers during implementation tears out their hair.

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 18 Jan 2024 20:24:23 -0700

On Thu, Jan 18, 2024 at 11:31 AM Andrey Borodin <[email-masked]>
wrote:

> postgres=# select TIMESTAMP WITH TIME ZONE '2022-02-22 14:22:22-05' -
> TIMESTAMP WITH TIME ZONE 'Tuesday, February 22, 2022 2:22:22.00 PM
> GMT-05:00';
>  ?column?
> ----------
>  10:00:00
> (1 row)
>
>
You are mixing POSIX and ISO-8601 conventions and, as noted in our
appendix, they disagree on the direction that is positive.

https://www.postgresql.org/docs/current/datetime-posix-timezone-specs.html

The offset fields specify the hours, and optionally minutes and seconds,
difference from UTC. They have the format hh[:mm[:ss]] optionally with a
leading sign (+ or -). The positive sign is used for zones west of
Greenwich. (Note that this is the opposite of the ISO-8601 sign convention
used elsewhere in PostgreSQL.)

David J.

---

From: Andrey Borodin <[email-masked]>
Date: Fri, 19 Jan 2024 13:25:51 +0500

> On 19 Jan 2024, at 08:24, David G. Johnston <[email-masked]> wrote:
> 
> 
> You are mixing POSIX and ISO-8601 conventions and, as noted in our appendix, they disagree on the direction that is positive.

Thanks! Now everything seems on its place.

I want to include in the patch following tests:
-- extract UUID v1, v6 and v7 timestamp
SELECT uuid_extract_time('C232AB00-9414-11EC-B3C8-9F6BDECED846') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00';
SELECT uuid_extract_time('1EC9414C-232A-6B00-B3C8-9F6BDECED846') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00';
SELECT uuid_extract_time('017F22E2-79B0-7CC3-98C4-DC0C0C07398F') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00';

How do you think, will it be stable all across buildfarm? Or should we change anything to avoid false positives inferred from different timestamp parsing?

> On 19 Jan 2024, at 07:58, Lukas Fittl <[email-masked]> wrote:
> 
> Note how calling the uuidv7 function again after having called it with a fixed future timestamp, returns the future timestamp, even though it should return the current time.

Thanks for the review.
Well, that was intentional. But now I see it's kind of confusing behaviour. I've changed it to more expected version.

Also, I've added some documentation on all functions.

---

From: Aleksander Alekseev <[email-masked]>
Date: Fri, 19 Jan 2024 14:07:31 +0300

Hi,

> SELECT extract(epoch from '2024-01-18 9:27:30'::timestamp), extract(epoch from '2024-01-18 9:27:30'::timestamptz);
>
>  date_part  | date_part
> In my opinion, timestamptz gives greater guarantees that the time internally is in UTC and the user gets the time in his/her time zone.

I believe you didn't notice, but this example just proves my point.

In this case you have two timestamps that are different _internally_,
but the way they are _shown_ is the same because the first one is in
UTC and the second one in your local session timezone, Europe/Warsaw.
extract(epoch ...) extract UNIX epoch, i.e. relies on the _internal_
representation. This is why you got different results.

This demonstrates that TimestampTz is a permanent source of confusion
for the users and the reason why personally I would prefer if UUIDv7
always used Timestamp (no Tz). TimestampTz can be converted to
TimestampTz by users who need them and have experience using them.

-- 
Best regards,
Aleksander Alekseev

---

From: Andrey Borodin <[email-masked]>
Date: Fri, 19 Jan 2024 23:07:35 +0500

> On 19 Jan 2024, at 13:25, Andrey Borodin <[email-masked]> wrote:
> 

Here's v12. Changes:
1. Documentation improvements
2. Code comments
3. Better commit message and reviews list

---

From: Nikolay Samokhvalov <[email-masked]>
Date: Sun, 21 Jan 2024 20:22:19 -0800

> 3. Better commit message and reviews list

Thank you, Andrey! I have just checked v12 – cleanly applied to HEAD, and
functions work well. I especially like that fact that we keep
uuid_extract_time(..) here – this is a great thing to have for time-based
partitioning, and in many cases we will be able to decide not to have a
creation column timestamp (e.g., "created_at") at all, saving 8 bytes.

The docs and comments look great too.

Overall, the patch looks mature enough. It would be great to have it in
pg17. Yes, the RFC is not fully finalized yet, but it's very close. And
many libraries are already including implementation of UUIDv7 – here are
some examples:

- https://www.npmjs.com/package/uuidv7
- https://crates.io/crates/uuidv7
- https://github.com/google/uuid/pull/139

Nik

---

From: Nikolay Samokhvalov <[email-masked]>
Date: Mon, 22 Jan 2024 04:24:31 +0000

The following review has been posted through the commitfest application:
make installcheck-world:  not tested
Implements feature:       tested, passed
Spec compliant:           not tested
Documentation:            tested, passed

Manually tested uuidv7(), uuid_extract_time() – they work as expected. The basic docs provided look clear.

I haven't checked the tests though and possible edge cases, so leaving it as "needs review" waiting for more reviewers

---

From: Aleksander Alekseev <[email-masked]>
Date: Mon, 22 Jan 2024 18:02:34 +0300

Hi,

> But now (after big timeseries project with multiple time zones and DST problems) I think differently.
> Even though timestamp and timestamptz are practically the same, timestamptz should be used to store the time in UTC.
> Choosing timestamp is more likely to lead to problems and misunderstandings than timestamptz.

As somebody who contributed TZ support to TimescaleDB I'm more or less
aware about the pros and cons of Timestamp and TimestampTz :)
Engineering is all about compromises. I can imagine a project where it
makes sense to use only TimestampTz for the entire database, and the
opposite - when it's better to use only UTC and Timestamp. In this
particular case I was merely concerned that the particular choice
could be confusing for the users but I think I changed my mind by now,
see below.

> Thank you, Andrey! I have just checked v12 – cleanly applied to HEAD, and functions work well. I especially like that fact that we keep uuid_extract_time(..) here – this is a great thing to have for time-based partitioning, and in many cases we will be able to decide not to have a creation column timestamp (e.g., "created_at") at all, saving 8 bytes.
>
> Overall, the patch looks mature enough. It would be great to have it in pg17. Yes, the RFC is not fully finalized yet, but it's very close. And many libraries are already including implementation of UUIDv7 – here are some examples:
>

Thanks!

After playing with v12 I'm inclined to agree that it's RfC.

I only have a couple of silly nitpicks:

- It could make sense to decompose the C implementation of uuidv7() in
two functions, for readability.
- It could make sense to get rid of curly braces in SQL tests when
calling uuid_extract_ver() and uuid_extract_ver(), for consistency.

I'm not going to insist on these changes though and prefer leaving it
to the author and the committer to decide.

Also I take back what I said above about using Timestamp instead of
TimestampTz. I forgot that Timestamps are implicitly casted to
TimestampTz's, so users preferring Timestamps can do this:

```
=# select uuidv7('2024-01-22 12:34:56' :: timestamp);
                uuidv7
--------------------------------------
 018d3085-de00-77c1-9e7b-7b04ddb9ebb9
```

Cfbot also seems to be happy with the patch so I'm changing the CF
entry status to RfC.

-- 
Best regards,
Aleksander Alekseev

---

From: Aleksander Alekseev <[email-masked]>
Date: Wed, 24 Jan 2024 15:31:28 +0300

Hi,

> Cfbot also seems to be happy with the patch so I'm changing the CF

I've found a bug:

```
=# select now() - interval '5000 years';
                ?column?
----------------------------------------
 2977-01-24 15:29:01.779462+02:30:17 BC

Time: 0.957 ms

=# select uuidv7(now() - interval '5000 years');
                uuidv7
--------------------------------------
 720c1868-0764-7677-99cd-265b84ea08b9

=# select uuid_extract_time('720c1868-0764-7677-99cd-265b84ea08b9');
     uuid_extract_time
----------------------------
 5943-08-26 21:30:44.836+03
```

-- 
Best regards,
Aleksander Alekseev

---

From: "Andrey M. Borodin" <[email-masked]>
Date: Wed, 24 Jan 2024 17:40:36 +0500

> On 24 Jan 2024, at 17:31, Aleksander Alekseev <[email-masked]> wrote:
> 
> Hi,
> 

UUIDv7 range does not correspond to timestamp range. But it’s purpose is not in storing timestamp, but in being unique identifier. So I don’t think it worth throwing an error when overflowing value is given. BTW if you will subtract some nanoseconds - you will not get back timestamp you put into UUID too.
UUID does not store timpestamp, it only uses it to generate an identifier. Some value can be extracted back, but with limited precision, limited range and only if UUID was generated precisely by the specification in standard (and standard allows deviation! Most of implementation try to tradeoff something).

---

From: Aleksander Alekseev <[email-masked]>
Date: Wed, 24 Jan 2024 16:02:13 +0300

Hi,

> UUID does not store timpestamp, it only uses it to generate an identifier. Some value can be extracted back, but with limited precision, limited range and only if UUID was generated precisely by th…

I don't claim that UUIDv7 purpose is storing timestamps, but I think
the invariant:

```
uuid_extract_time(uidv7(X)) == X
```

and (!) even more importantly:

```
if X > Y then uuidv7(X) > uuidv7(Y)
```

... should hold. Otherwise you can calculate crc64(X) or sha256(X)
internally in order to generate an unique ID and claim that it's fine.

Values that violate named invariants should be rejected with an error.

-- 
Best regards,
Aleksander Alekseev

---

From: "Andrey M. Borodin" <[email-masked]>
Date: Wed, 24 Jan 2024 18:16:09 +0500

> On 24 Jan 2024, at 18:02, Aleksander Alekseev <[email-masked]> wrote:
> 
> Hi,
> 
> ... should hold.
Function to extract timestamp does not provide any guarantees at all. Standard states this, see Kyzer answers upthread.
Moreover, standard urges against relying on that if uuidX was generated before uuidY, then uuidX<uuid. The standard is doing a lot to make this happen, but does not guaranty that.
All what is guaranteed is the uniqueness at certain conditions.

> Otherwise you can calculate crc64(X) or sha256(X)

Think about the value that you pass to uuid generation function as an entropy. It’s there to ensure uniqueness and promote ordering (but not guarantee).

---

From: Aleksander Alekseev <[email-masked]>
Date: Wed, 24 Jan 2024 16:16:23 +0300

Hi,

> Values that violate named invariants should be rejected with an error.

To clarify, I don't think we should bother about the precision part.
"Equals" in the example above means "equal within UUIDv7 precision",
same for "more" and "less". However, years 2977 BC and 5943 AC are
clearly not equal, thus 2977 BC should be rejected as an invalid value
for UUIDv7.

-- 
Best regards,
Aleksander Alekseev

---

From: Aleksander Alekseev <[email-masked]>
Date: Wed, 24 Jan 2024 16:29:50 +0300

Hi,

> Think about the value that you pass to uuid generation function as an entropy. It’s there to ensure uniqueness and promote ordering (but not guarantee).

If the standard doesn't guarantee something it doesn't mean it forbids
us to give stronger guarantees. I'm convinced that these guarantees
will be useful in real-world applications, at least the ones acting
exclusively within Postgres.

This being said, I understand your point of view too. Let's see what
other people think.

-- 
Best regards,
Aleksander Alekseev

---

From: "Andrey M. Borodin" <[email-masked]>
Date: Wed, 24 Jan 2024 20:29:36 +0500

> On 24 Jan 2024, at 18:29, Aleksander Alekseev <[email-masked]> wrote:
> 
> Hi,
> 
> us to give stronger guarantees.
No, the standard makes these guarantees impossible.
If we insist that uuid_extract_time(uuidv7(time))==time, we won't be able to generate uuidv7 most of the time. uuidv7(now()) will always ERROR-out.
Standard implies more coarse-grained timestamp that we have.

Also, please not that uuidv7(time+1us) and uuidv7(time) will have the same internal timestamp, so despite time+1us > time, still second uuid will be greater.

Both invariants you proposed cannot be reasonably guaranteed. Upholding any of them greatly reduces usability of UUID v7.

---

From: Aleksander Alekseev <[email-masked]>
Date: Wed, 24 Jan 2024 18:46:14 +0300

Hi,

> Both invariants you proposed cannot be reasonably guaranteed. Upholding any of them greatly reduces usability of UUID v7.

Again, personally I don't insist on the 1us precision [1]. Only the
fact that timestamp from the far past generates UUID from the future
bothers me.

[1]: https://postgr.es/m/CAJ7c6TPCSprWwVNdOB%3D%3DpgKZPqO5q%3DHRgmU7zmYqz9Dz5ffVYw%40mail.gmail.com

-- 
Best regards,
Aleksander Alekseev

---

From: "Andrey M. Borodin" <[email-masked]>
Date: Wed, 24 Jan 2024 21:54:37 +0500

> On 24 Jan 2024, at 20:46, Aleksander Alekseev <[email-masked]> wrote:
> 
> Only the

PFA implementation of guard checks, but I'm afraid that this can cause failures in ID generation unexpected to the user...
See tests

+-- errors in edge cases of UUID v7
+SELECT 1 FROM uuidv7('1970-01-01 00:00:00+00'::timestamptz - interval '0ms');
+SELECT uuidv7('1970-01-01 00:00:00+00'::timestamptz - interval '1ms'); -- ERROR expected
+SELECT 1 FROM uuidv7(uuid_extract_time('FFFFFFFF-FFFF-7FFF-B000-000000000000'));
+SELECT uuidv7(uuid_extract_time('FFFFFFFF-FFFF-7FFF-B000-000000000000')+'1ms'); -- ERROR expected

Range is from 1970-01-01 00:00:00 to 10889-08-02 05:31:50.655. I'm not sure we should give this information in error message...
Thanks!

---

From: Marcos Pegoraro <[email-masked]>
Date: Wed, 24 Jan 2024 14:00:52 -0300

Is enough from 1970 ?
How about if user wants to have an UUID of his birth date ?

regards
Marcos

Em qua., 24 de jan. de 2024 às 13:54, Andrey M. Borodin <
[email-masked]> escreveu:

> PFA implementation of guard checks, but I'm afraid that this can cause
> failures in ID generation unexpected to the user...
> See tests
>
> +SELECT 1 FROM
> uuidv7(uuid_extract_time('FFFFFFFF-FFFF-7FFF-B000-000000000000'));
> +SELECT
> uuidv7(uuid_extract_time('FFFFFFFF-FFFF-7FFF-B000-000000000000')+'1ms'); --
> ERROR expected
>
> Range is from 1970-01-01 00:00:00 to 10889-08-02 05:31:50.655. I'm not
> sure we should give this information in error message...
> Thanks!
>
>

---

From: Andrey Borodin <[email-masked]>
Date: Wed, 24 Jan 2024 22:51:49 +0500

> On 24 Jan 2024, at 22:00, Marcos Pegoraro <[email-masked]> wrote:
> 
> Is enough from 1970 ?
Per standard unix_ts_ms field is a number of milliseconds from UNIX start date 1970-01-01.

> How about if user wants to have an UUID of his birth date ?

I've claimed my
0078c135-bd00-70b1-865a-63c3741922a5

But again, UUIDs are not designed to store timestamp. They are unique and v7 promote data locality via time-ordering.

---

From: Marcos Pegoraro <[email-masked]>
Date: Wed, 24 Jan 2024 17:47:07 -0300

I understand your point, but
'2000-01-01' :: timestamp and '1900-01-01' :: timestamp are both valid
timestamps.

So looks strange if user can do
select uuidv7(TIMESTAMP '2000-01-01')
but cannot do
select uuidv7(TIMESTAMP '1900-01-01')

Regards
Marcos

Em qua., 24 de jan. de 2024 às 14:51, Andrey Borodin <[email-masked]>
escreveu:

> But again, UUIDs are not designed to store timestamp. They are unique and
> v7 promote data locality via time-ordering.
>
>

---

From: Jelte Fennema-Nio <[email-masked]>
Date: Wed, 24 Jan 2024 22:15:43 +0100

On Wed, 24 Jan 2024 at 21:47, Marcos Pegoraro <[email-masked]> wrote:
> '2000-01-01' :: timestamp and '1900-01-01' :: timestamp are both valid timestamps.
>

I think that would be okay honestly. I don't think there's any
reasonable value for the uuid when a timestamp is given outside of the
date range that the uuid7 "algorithm" supports.

So +1 for erroring when you provide a timestamp outside of that range
(either too far in the past or too far in the future).

---

From: Sergey Prokhorenko <[email-masked]>
Date: Wed, 24 Jan 2024 21:30:33 +0000 (UTC)

"Other people" think that extracting the timestamp from UUIDv7 in violation of the new RFC, and generating UUIDv7 from the timestamp were both terrible and poorly thought out ideas. The authors of the new RFC had very good reasons to prohibit this. And the problems you face are the best confirmation of the correctness of the new RFC. It’s better to throw all this gag out of the official patch. Don't tempt developers to break the new RFC with these error-producing functions.

    On Wednesday, 24 January 2024 at 04:30:02 pm GMT+3, Aleksander Alekseev <[email-masked]> wrote:  
 
 Hi,

-- 
Best regards,
Aleksander Alekseev

---

From: Sergey Prokhorenko <[email-masked]>
Date: Wed, 24 Jan 2024 21:49:45 +0000 (UTC)

That's right! There is no point in waiting for the official approval of the new RFC, which obviously will not change anything. I have been a contributor to this RFC for several years, and I can testify that every aspect imaginable has been thoroughly researched and agreed upon. Nothing new will definitely appear in the new RFC.

    On Monday, 22 January 2024 at 07:22:32 am GMT+3, Nikolay Samokhvalov <[email-masked]> wrote:  

Here's v12. Changes:

---

From: Nikolay Samokhvalov <[email-masked]>
Date: Wed, 24 Jan 2024 20:40:30 -0800

On Wed, Jan 24, 2024 at 1:52 PM Sergey Prokhorenko <
[email-masked]> wrote:

> That's right! There is no point in waiting for the official approval of
> the new RFC, which obviously will not change anything. I have been a
> contributor to this RFC
> <https://www.ietf.org/archive/id/draft-ietf-uuidrev-rfc4122bis-14.html#name-acknowledgements>
> for several years, and I can testify that every aspect imaginable has been
> thoroughly researched and agreed upon. Nothing new will definitely appear
> in the new RFC.
>

---

From: Nikolay Samokhvalov <[email-masked]>
Date: Wed, 24 Jan 2024 20:41:43 -0800

On Wed, Jan 24, 2024 at 8:40 PM Nikolay Samokhvalov <[email-masked]> wrote:

>> thoroughly researched and agreed upon. Nothing new will definitely
>> appear in the new RFC.
>>
>
> From a practical point of view, these two things are extremely important
> to have to support partitioning. It is better to implement limitations than
> throw them away.
>
> Without them, this functionality will be of a very limited use in
> databases. We need to think about large tables – which means partitioning.
>

apologies -- this was a response to another email from you:

> "Other people" think that extracting the timestamp from UUIDv7 in
violation of the new RFC, and generating UUIDv7 from the timestamp were
both terrible and poorly thought out ideas. The authors of the new RFC had
very good reasons to prohibit this. And the problems you face are the best
confirmation of the correctness of the new RFC. It’s better to throw all
this gag out of the official patch. Don't tempt developers to break the new
RFC with these error-producing functions.

---

From: "Andrey M. Borodin" <[email-masked]>
Date: Thu, 25 Jan 2024 11:51:40 +0500

> On 25 Jan 2024, at 09:40, Nikolay Samokhvalov <[email-masked]> wrote:
> 
> From a practical point of view, these two things are extremely important to have to support partitioning. It is better to implement limitations than throw them away.

Postgres always was a bit hackerish, allowing slightly more then is safe. I.e. you can define immutable function that is not really immutable, turn off autovacuum or fsync. Why bother with safety guards here?
My opinion is that we should have this function to extract timestamp. Even if it can return strange values for imprecise RFC implementation.

> On 25 Jan 2024, at 02:15, Jelte Fennema-Nio <[email-masked]> wrote:
> 

OK, it seems like we have some consensus on ERRORing..

Do we have any other open items? Does v13 address all open items? Maybe let’s compose better error message?

---

From: Sergey Prokhorenko <[email-masked]>
Date: Thu, 25 Jan 2024 08:09:18 +0000 (UTC)

I am against turning the DBMS into another C++, in which they do not so much design something new as fix bugs in production after a crash.
As for partitioning, I already wrote to Andrey Borodin that we need a special function to generate a partition id using the UUIDv7 timestamp or even simultaneously with the generation of the timestamp. For example, every month (or so, since precision is not needed here) a new partition is created. Here's a good example: https://elixirforum.com/t/partitioning-postgres-tables-by-timestamp-based-uuids/60916
But without a separate function for extracting the entire timestamp from the UUID! Let's solve this specific problem, and not give the developers a grenade with the safety removed. Many developers have already decided to store the timestamp in UUIDv7, so as not to create a separate created_at field. Then they will delete table records with the old timestamp, etc. Horrible mistakes are simply guaranteed.

Sergey Prokhorenko [email-masked]

---

From: Przemysław Sztoch <[email-masked]>
Date: Thu, 25 Jan 2024 12:14:38 +0100

> Do we have any other open items? Does v13 address all open items? Maybe let’s compose better error message?
+1 for erroring when ts is outside range.

v13 looks good for me. I think we have reached a optimal compromise.

---

From: Aleksander Alekseev <[email-masked]>
Date: Thu, 25 Jan 2024 15:06:38 +0300

Hi,

> My opinion is that we should have this function to extract timestamp. Even if it can return strange values for imprecise RFC implementation.

Completely agree.

Users that don't like or don't need it can pretend there are no
uuid_extract_time() and uuidv7(T) in Postgres. If we don't provide
them however, users that need them will end up writing their own
probably buggy and not compatible implementations. That would be much
worse.

> v13 looks good for me. I think we have reached a optimal compromise.

Andrey, many thanks for the updated patch.

LGTM, cfbot is happy and I don't think we have any open items left. So
changing CF entry status back to RfC.

-- 
Best regards,
Aleksander Alekseev

---

From: Aleksander Alekseev <[email-masked]>
Date: Thu, 25 Jan 2024 15:31:44 +0300

Hi,

> changing CF entry status back to RfC.

PFA v14. I changed:

```
elog(ERROR, "Time argument of UUID v7 cannot exceed 6 bytes");
```

... to:

```
ereport(ERROR,
    (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
    errmsg("Time argument of UUID v7 is outside of the valid range")));
```

Which IMO tells a bit more to the average user and is translatable.

> At a quick glance, the patch needs improving English, IMO.

Agree. We could use some help from a native English speaker for this.

-- 
Best regards,
Aleksander Alekseev

---

From: Sergey Prokhorenko <[email-masked]>
Date: Thu, 25 Jan 2024 17:04:05 +0000 (UTC)

Aleksander,

In this case the documentation must state that the functions uuid_extract_time() and uuidv7(T) are against the RFC requirements, and that developers may use these functions with caution at their own risk, and these functions are not recommended for production environment.

The function uuidv7(T) is not better than uuid_extract_time(). Careless developers may well pass any business date into this function: document date, registration date, payment date, reporting date, start date of the current month, data download date, and even a constant. This would be a profanation of UUIDv7 with very negative consequences.

    On Thursday, 25 January 2024 at 03:06:50 pm GMT+3, Aleksander Alekseev <[email-masked]> wrote:  
 
 Hi,

Completely agree.

-- 
Best regards,
Aleksander Alekseev

---

From: Sergey Prokhorenko <[email-masked]>
Date: Sun, 28 Jan 2024 12:42:11 +0000 (UTC)

By the way, the Go language has also already implemented a function for UUIDv7: https://pkg.go.dev/github.com/gofrs/uuid#NewV7

    On Thursday, 25 January 2024 at 12:49:46 am GMT+3, Sergey Prokhorenko <[email-masked]> wrote:  

 

Here's v12. Changes:

---

From: Jelte Fennema-Nio <[email-masked]>
Date: Mon, 29 Jan 2024 12:38:24 +0100

tl;dr I believe we should remove the uuidv7(timestamp) function from
this patchset.

> The function uuidv7(T) is not better than uuid_extract_time(). Careless developers may well pass any business date into this function: document date, registration date, payment date, reporting date…

After re-reading the RFC more diligently, I'm inclined to agree with
Sergey that uuidv7(timestamp) is quite problematic. And I would even
say that we should not provide uuidv7(timestamp) at all, and instead
should only provide uuidv7(). Providing an explicit timestamp for
UUIDv7 is explicitly against the spec (in my reading):

> Implementations acquire the current timestamp from a reliable
> source to provide values that are time-ordered and continually
> increasing.  Care must be taken to ensure that timestamp changes
> from the environment or operating system are handled in a way that
> is consistent with implementation requirements.  For example, if
> it is possible for the system clock to move backward due to either
> manual adjustment or corrections from a time synchronization
> protocol, implementations need to determine how to handle such
> cases.  (See Altering, Fuzzing, or Smearing below.)
>
> ...
>
> UUID version 1 and 6 both utilize a Gregorian epoch timestamp
> while UUIDv7 utilizes a Unix Epoch timestamp.  If other timestamp
> sources or a custom timestamp epoch are required, UUIDv8 MUST be
> used.
>
> ...
>
> Monotonicity (each subsequent value being greater than the last) is
> the backbone of time-based sortable UUIDs.

By allowing users to provide a timestamp we're not using a continually
increasing timestamp for our UUIDv7 generation, and thus it would not
be a valid UUIDv7 implementation.

I do agree with others however, that being able to pass in an
arbitrary timestamp for UUID generation would be very useful. For
example to be able to partition by the timestamp in the UUID and then
being able to later load data for an older timestamp and have it be
added to to the older partition. But it's possible to do that while
still following the spec, by using a UUIDv8 instead of UUIDv7. So for
this usecase we could make a helper function that generates a UUIDv8
using the same format as a UUIDv7, but allows storing arbitrary
timestamps. You might say, why not sligthly change UUIDv7 then? Well
mainly because of this critical sentence in the RFC:

> UUIDv8's uniqueness will be implementation-specific and MUST NOT be assumed.

That would allow us to say that using this UUIDv8 helper requires
careful usage and checks if uniqueness is required.

So I believe we should remove the uuidv7(timestamp) function from this patchset.

I don't see a problem with including uuid_extract_time though. Afaict
the only thing the RFC says about extracting timestamps is that the
RFC does not give a requirement or guarantee about how close the
stored timestamp is to the actual time:

> Implementations MAY alter the actual timestamp.  Some examples
> include security considerations around providing a real clock
> value within a UUID, to correct inaccurate clocks, to handle leap
> seconds, or instead of dividing a number of microseconds by 1000
> to obtain a millisecond value; dividing by 1024 (or some other
> value) for performance reasons.  This specification makes no
> requirement or guarantee about how close the clock value needs to
> be to the actual time.

I see no reason why we cannot make stronger guarantees about the
timestamps that we use to generate UUIDs with our uuidv7() function.
And then we can update the documentation for
uuid_extract_time to something like this:

> This function extracts a timestamptz from UUID versions 1, 6 and 7. For other
> versions and variants this function returns NULL. The extracted timestamp
> does not necessarily equate to the time of UUID generation. How close it is
> to the actual time depends on the implementation that generated to UUID.
> The uuidv7() function provided PostgreSQL will normally store the actual time of
> generation to in the UUID, but if large batches of UUIDs are generated at the
> same time it's possible that some UUIDs will store a time that is slightly later
> than their actual generation time.

---

From: Jelte Fennema-Nio <[email-masked]>
Date: Mon, 29 Jan 2024 13:01:36 +0100

On Thu, 25 Jan 2024 at 13:31, Aleksander Alekseev
<[email-masked]> wrote:
> PFA v14.

+<function>uuidv4</function> () <returnvalue>uuid</returnvalue>
+</synopsis>
+   Both functions return a version 4 (random) UUID. This is the most commonly
+   used type of UUID and is appropriate when random distribution of keys does
+   not affect performance of an application.
+<synopsis>
+<function>uuidv7</function> () <returnvalue>uuid</returnvalue>
+</synopsis>
+   This function returns a version 7 (time-ordered + random) UUID. This UUID
+   version should be used when application prefers locality of identifiers.
+<synopsis>

I think it would be good to explain the tradeoffs between uuidv4 and
uuidv7 a bit better. How about changing the docs to something like
this:

<function>uuidv4</function> () <returnvalue>uuid</returnvalue>
</synopsis>
Both functions return a version 4 (random) UUID. UUIDv4 is one of the
most commonly used types of UUID. It is appropriate when random
distribution of keys does not affect performance of an application or
when exposing the generation time of a UUID has unacceptable security
or business intelligence implications.
<synopsis>
<function>uuidv7</function> () <returnvalue>uuid</returnvalue>
</synopsis>
This function returns a version 7 (time-ordered + random) UUID. It
provides much better data locality than UUIDv4, which can greatly
improve performance when UUID is used in a BTREE index (the default
index type in PostgreSQL). To achieve this data locality, UUIDv7
embeds its own generation time into the UUID. If exposing such a
timestamp has unacceptable security or business intelligence
implications, then uuidv4() should be used instead.
<synopsis>

---

From: Junwang Zhao <[email-masked]>
Date: Mon, 29 Jan 2024 21:58:55 +0800

> So I believe we should remove the uuidv7(timestamp) function from this patchset.

Agreed, the RFC section 6.1[1] has the following statements:

```
UUID version 1 and 6 both utilize a Gregorian epoch timestamp while
UUIDv7 utilizes a Unix Epoch timestamp. If other timestamp sources or
a custom timestamp epoch are required, UUIDv8 MUST be used.
```

In contrib/uuid-ossp, uuidv1 does not allow the user to supply a
custom timestamp,
so I think it should be the same for uuidv6 and uuidv7.

And I have the same feeling that we should not consider v6 and v8 in
this patch.

[1]: https://datatracker.ietf.org/doc/html/draft-ietf-uuidrev-rfc4122bis-14#section-6.1-2.4.1

-- 
Regards
Junwang Zhao

---

From: "Andrey M. Borodin" <[email-masked]>
Date: Mon, 29 Jan 2024 23:32:38 +0500

> On 25 Jan 2024, at 22:04, Sergey Prokhorenko <[email-masked]> wrote:
> 
> Aleksander,
> 

Refining documentation is good. However, saying that these functions are not recommended for production must be based on some real threats.

> The function uuidv7(T) is not better than uuid_extract_time(). Careless developers may well pass any business date into this function: document date, registration date, payment date, reporting date…

Even if the developer pass constant time to uuidv7(T) they will get what they asked for - unique identifier. Moreover - it still will be keeping locality. There will be no negative consequences at all.
On the contrary, experienced developer can leverage parameter when data locality should be reduced. If you have serveral streams of data, you might want to introduce some shift in reduce contention.
For example, you can generate uuidv7(now() + '1 day' * random(0,10)). This will split 1 contention point to 10 and increase ingestion performance 10x-fold.

> On 29 Jan 2024, at 18:58, Junwang Zhao <[email-masked]> wrote:
> 
> If other timestamp sources or

Well, yeah. RFC says this... in 4 capital letters :) I believe it's kind of a big deficiency that k-way sortable identifiers are not implementable on top of UUIDv7. Well, let's go without this function. UUIDv7 is still an improvement over previous versions.

Jelte, your documentation corrections looks good to me, I'll include them in next version.

Thanks!

---

From: Jelte Fennema-Nio <[email-masked]>
Date: Mon, 29 Jan 2024 21:38:27 +0100

> Even if the developer pass constant time to uuidv7(T) they will get what they asked for - unique identifier. Moreover - it still will be keeping locality. There will be no negative consequences at…

It will be significantly "less unique" than if they wouldn't pass a
constant time. Basically it would become a UUIDv4, but with 74 bits of
random data instead of 122. That might not be enough anymore to
"guarantee" uniqueness. I guess that's why it is required to use
UUIDv8 in these cases, because correct usage is now a requirement for

> Well, yeah. RFC says this... in 4 capital letters :)

As an FYI, there is an RFC that defines these keywords that's why they
are capital letters: https://www.ietf.org/rfc/rfc2119.txt

> I believe it's kind of a big deficiency that k-way sortable identifiers are not implementable on top of UUIDv7. Well, let's go without this function. UUIDv7 is still an improvement over previous versions.

Yeah, I liked the feature to generate UUIDv7 based on timestamp too.
But following the spec seems more important than a nice feature to me.

---

From: Sergey Prokhorenko <[email-masked]>
Date: Tue, 30 Jan 2024 00:27:21 +0000 (UTC)

Andrey,
 I understand and agree with your goals. But instead of dangerous universal functions, it is better to develop safe highly specialized functions that implement only these goals.
There should not be a function uuidv7(T) from an arbitrary timestamp, but there should be a special function that implements your algorithm: uuidv8(now() + '1 century' * random(0,10)).
I replaced 1 day with 1 century because the spread of 1 day is too small. Over time, records will be inserted between existing records, which is undesirable.
Similarly, if we need to calculate the partition id, then we do not need to use the uuid_extract_time() function to provide the extracted timestamp, the accuracy of which cannot be guaranteed. Instead, we need to give exactly the partition id, calculated using the uuidv7 timestamp. For example, partitions may have approximately a month interval between each other.
As for the documentation, it must be indicated that the UUIDv7 structure is not timestamp + random, but timestamp + randomly seeded counter + random, like in all advanced implementations.

Sergey Prokhorenko
[email-masked]
______________________________________________________________ 

Thanks!

---

From: "Andrey M. Borodin" <[email-masked]>
Date: Tue, 30 Jan 2024 11:54:48 +0500

> On 30 Jan 2024, at 01:38, Jelte Fennema-Nio <[email-masked]> wrote:
> 

PFA v15. Changes: removed timestamp argument, incorporated Jelte’s documentation addons.

Thanks!

---

From: Sergey Prokhorenko <[email-masked]>
Date: Tue, 30 Jan 2024 07:28:28 +0000 (UTC)

Andrey,

I think this phrase is outdated: "This function can optionally accept a timestamp used instead of current time.This allows implementation of k-way sotable identifiers."
This phrase is wrong: "Both functions return a version 4 (random) UUID."
For this phrase the reason is unclear and the phrase is most likely incorrect:
if large batches of UUIDs are generated at the+   same time it's possible that some UUIDs will store a time that is slightly later+   than their actual generation time

Sergey Prokhorenko

Thanks!

---

From: "Andrey M. Borodin" <[email-masked]>
Date: Tue, 30 Jan 2024 14:56:10 +0500

> On 30 Jan 2024, at 12:28, Sergey Prokhorenko <[email-masked]> wrote:
> 
> 
> I think this phrase is outdated: "This function can optionally accept a timestamp used instead of current time.
> This allows implementation of k-way sotable identifiers.”
Fixed.

> This phrase is wrong: "Both functions return a version 4 (random) UUID.”
This applies to functions gen_random_uuid() and uuidv4().
> if large batches of UUIDs are generated at the
> +   same time it's possible that some UUIDs will store a time that is slightly later
> +   than their actual generation time

I’ve rewritten this phrase, hope it’s more clear now.

---

From: Junwang Zhao <[email-masked]>
Date: Tue, 30 Jan 2024 18:33:21 +0800

Hi Andrey,

> Best regards, Andrey Borodin.

+Datum
+uuid_extract_var(PG_FUNCTION_ARGS)
+{
+ pg_uuid_t  *uuid = PG_GETARG_UUID_P(0);
+ uint16_t result;
+ result = uuid->data[8] >> 6;
+
+ PG_RETURN_UINT16(result);
+}
\ No newline at end of file

It's always good to add a newline at the end of a  source file, though
this might be nitpicky.

-- 
Regards
Junwang Zhao

---

From: "Andrey M. Borodin" <[email-masked]>
Date: Tue, 30 Jan 2024 18:35:28 +0500

> On 30 Jan 2024, at 15:33, Junwang Zhao <[email-masked]> wrote:
> 

Thanks, also fixed warning found by CFBot.

---

From: Sergey Prokhorenko <[email-masked]>
Date: Tue, 30 Jan 2024 18:37:56 +0000 (UTC)

typo:
being carried to time step

should be:being carried to timestemp