> > Well, as far as I know, RFC discourages extracting timestamps from UUIDs. > > But we still can have such functions...maybe as an extension? > Do you know of any reason for that?
I guess some of the detail may have been edited out over time with all of the changes, but it’s basically this: https://github.com/ietf-wg-uuidrev/rfc4122bis/blob/main/draft-ietf-uuidrev-rfc4122bis.md#opacity-opacity. The rationale is that when you introspect a UUID you essentially add interoperability concerns. E.g. if we say that applications can rely on being able to parse the timestamp from the UUID then it means that other implementations must provide guarantees about what that timestamp is. And since the point of a UUID is to provide a unique value, not to transmit additional metadata, the decision was made early on that it’s more realistic and representative of the reality of the situation to say that applications should generate values, try not to parse them if they don’t have to, but if they do it’s only going to be as accurate as the original data put into it. So systems with no NTP enabled, or that fuzz part of the time so as not to leak the exact moment in time something was done, etc - those are things that are going to happen and so buyer beware when parsing. If the question is whether or not a function should exist to parse a timestamp from a UUID, I would say sure go ahead, just mention that the timestamp is only accurate as the input, and the spec doesn’t guarantee anything if your UUID came from another source. I imagine a common case would be UUIDs generated in within the same database, and someone wants to extract the timestamp, which would be as reliable as the timestamp on the database machine - seems like a perfectly good case where supporting timestamp extraction as practical value. > On Oct 9, 2023, at 11:11 AM, Jelte Fennema <postg...@jeltef.nl> wrote: > > On Mon, 9 Oct 2023 at 18:46, Nick Babadzhanian <pgni...@gmail.com> wrote: >> >> On Thu, 31 Aug 2023 at 23:10, Andrey M. Borodin <x4...@yandex-team.ru> wrote: >>> Well, as far as I know, RFC discourages extracting timestamps from UUIDs. >>> But we still can have such functions...maybe as 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 is possible. However, where >> necessary, inspectors should refer to Section 4 for more information on >> determining UUID version and variant. > >>> However, so far I haven't figured out how to implement optional arguments >>> for catalog functions. I'd appreciate any pointers here. >> >> 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 continually increasing. Care SHOULD >> 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 must decide how to handle such >> cases. (See Altering, Fuzzing, or Smearing bullet below.)
