Re: UUID v7 - Mailing list pgsql-hackers

On Sat, Nov 30, 2024 at 7:01 AM Daniel Verite <daniel@manitou-mail.org> wrote:
>
>         Andrey M. Borodin wrote:
>
>
> > I'm sending amendments addressing your review as a separate step in patch
> > set. Step 1 of this patch set is identical to v39.
>
> Some comments about the implementation of monotonicity:
>
> +/*
> + * Get the current timestamp with nanosecond precision for UUID generation.
> + * The returned timestamp is ensured to be at least SUBMS_MINIMAL_STEP
> greater
> + * than the previous returned timestamp (on this backend).
> + */
> +static inline int64
> +get_real_time_ns_ascending()
> +{
> +       static int64 previous_ns = 0;
>
> [...]
>
> +       /* Guarantee the minimal step advancement of the timestamp */
> +       if (previous_ns + SUBMS_MINIMAL_STEP_NS >= ns)
> +               ns = previous_ns + SUBMS_MINIMAL_STEP_NS;
> +       previous_ns = ns;
>
> In the case of parallel execution (uuidv7() being parallel-safe), if
> there have been previous calls to uuidv7() in that backend,
> previous_ns will be set in the backend process,
> but zero in a newly spawned worker process.
> If (previous_ns + SUBMS_MINIMAL_STEP_NS >= ns) ever happens
> to be true in the main process, it will start at false in the workers,
> leading to non-monotonic results within the same query.

The monotonicity of generated UUIDv7 is guaranteed only within a
single backend. I think your point is that UUIDs in parallel query
results might not be ordered. But is it guaranteed that without ORDER
BY clause, the results returned by parallel queries are in the same
order as the results from non-parallel queries in the first place?

>
> Also in the case of a backward clock change, we can end up with some
> backends sticking to the "old time" plus increment per invocation
> until they die, while some other backends spawned after the clock
> change are on the "new time". These backends may produce series of
> UUIDv7 that would be completely out of sync with each others.
> A backward clock change is an abnormality, but if it occurs, what's
> the best choice? Take the bullet and switch to the new time , or
> stick to a time that is permanently decorrelated from the OS
> clock? I would think that the latter is worse.

IIUC after generating a UUIDv7 with the correct time, even if the
system time goes back, the time in the next UUIDv7 will be
SUBMS_MINIMAL_STEP_NS nanoseconds ahead of the last correct time.
Also, in case where the backend generates its first UUIDv7 with an
incorrect (e.g. an old) time, it generates UUIDv7 based on the
incorrect timestamp. However, it starts generating UUIDv7 with the
correct timestamp as soon as the system time goes back to the correct
time. So I think that it doesn't happen that one backend is sticking
to an old time while another backend is using the correct timestamp to
generate UUIDv7. Note that we use (the previous timestamp +
SUBMS_MINIMAL_STEP_NS) only if the system clock didn't move forward by
SUBMS_MINIMAL_STEP_NS.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com