Re: UUID v7

Andrey M. Borodin wrote:

> I've addressed all items, except formatting a table...

Sorry for not following up sooner.

To illustrate my point upthread that was left unaddressed, let's say
I have a server with an incorrect date in the future.

A session generates an uuid

postgres=# select pg_backend_pid(), uuidv7();
pg_backend_pid | uuidv7
----------------+--------------------------------------
13545 | 019ad701-c798-7000-a0e4-7119e2c82446

Now somebody sets the clock backward to the correct date.

Then if that backend continues to generate uuids, here's
what it outputs:

postgres=# select pg_backend_pid(), uuidv7() from generate_series(1,10);
pg_backend_pid | uuidv7
----------------+--------------------------------------
13545 | 019ad701-c798-7001-8df7-d296dafd98fd
13545 | 019ad701-c798-7002-9995-bf103bbb56d7
13545 | 019ad701-c798-7003-88b3-5ea58c738ade
13545 | 019ad701-c798-7004-ba5e-e675fe103060
13545 | 019ad701-c798-7005-8608-59b9c852b4ce
13545 | 019ad701-c798-7006-832c-d06c15e2865a
13545 | 019ad701-c798-7007-8f45-360c0825c671
13545 | 019ad701-c798-7008-bb47-bcb7915503b2
13545 | 019ad701-c798-7009-9124-e6873b0265f6
13545 | 019ad701-c798-700a-8422-8d75c5ade9f7
(10 rows)

The timestamps are now just a sequence incrementing by 1
on each call, independently of the server's clock and
the actual time span between calls. It has become a counter
and will remain so until the backend terminates.

It does not have to be that way. In get_real_time_ns_ascending(),
it could switch immediately to the new time:

diff --git a/src/backend/utils/adt/uuid.c b/src/backend/utils/adt/uuid.c
index 2e32592f57..8df194daea 100644
--- a/src/backend/utils/adt/uuid.c
+++ b/src/backend/utils/adt/uuid.c
@@ -505,8 +505,11 @@ get_real_time_ns_ascending()
ns = tmp.tv_sec * NS_PER_S + tmp.tv_nsec;
#endif

- /* Guarantee the minimal step advancement of the timestamp */
- if (previous_ns + SUBMS_MINIMAL_STEP_NS >= ns)
+ /*
+ * Guarantee the minimal step advancement of the timestamp,
+ * unless the clock has moved backward.
+ */
+ if (previous_ns + SUBMS_MINIMAL_STEP_NS >= ns && previous_ns <= ns)
ns = previous_ns + SUBMS_MINIMAL_STEP_NS;
previous_ns = ns;

> Also PFA a prototype of making uuidv7() ordered across all backends via
> keeping previous_ns in shared memory. IMO it's overcomplicating and RFC
> does not require such guarantees

It does not have to be in core, but an extension might want to provide
a generator that guarantees monotonicity across backends.

Best regards,
--
Daniel Vérité
https://postgresql.verite.pro/
Twitter: @DanielVerite