Re: UUID v7

On Mon, 9 Oct 2023 at 18:46, Nick Babadzhanian <pgnickb@gmail.com> wrote:
>
> On Thu, 31 Aug 2023 at 23:10, Andrey M. Borodin <x4mmm@yandex-team.ru> wrote:
> > Well, as far as I know, RFC discourages extracting timestamps from UUIDs. But we still can have such
functions...maybeas an extension? 
>
> Do you know of any reason for that?

No reasons are given but the RFC states this:

> UUIDs SHOULD be treated as opaque values and implementations SHOULD NOT examine the bits in a UUID to whatever extent
ispossible. However, where necessary, inspectors should refer to Section 4 for more information on determining UUID
versionand variant. 

> > However, so far I haven't figured out how to implement optional arguments for catalog functions. I'd appreciate any
pointershere. 
>
> I'd argue that the time argument shouldn't be optional. Asking the
> user to supply time would force them to think whether they want to go
> with `now()` or `clock_timestamp()` or something else.

I think using `now()` is quite prone to sequence rollover. With the
current patch inserting more than 2^18~=0.26M rows into a table with
`gen_uuid_v7()` as the default in a single transaction would already
cause sequence rollover. I think using a monotonic clock source is the
only reasonable thing to do. From the RFC:

> Implementations SHOULD use the current timestamp from a reliable source to provide values that are time-ordered and
continuallyincreasing. Care SHOULD be taken to ensure that timestamp changes from the environment or operating system
arehandled in a way that is consistent with implementation requirements. For example, if it is possible for the system
clockto move backward due to either manual adjustment or corrections from a time synchronization protocol,
implementationsmust decide how to handle such cases. (See Altering, Fuzzing, or Smearing bullet below.)