Support microsecond resolution

[msqr]

Thank you for writing this handy extension! I wondered if you would consider adding a function to create a v7 UUID variant with microsecond precision, by making use of the high 10 bits of bytes 6 & 7 for a 0-999 microseconds value instead of random bits. The reason for this is because I found the millisecond-level precision in a v7 UUID inadequate for an application I worked on, and using microseconds was a good compromise between time precision and randomness.

The UUID layout looks like this:

 0                   1                   2                   3
 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|                           unix_ts_ms                          |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|          unix_ts_ms           |  ver  |       micros      |rnd|
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|var|                        rand_b                             |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|                            rand_b                             |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

I thought this could be an additional function like uuid_generate_v7_micro() so it did not impact the existing uuid_generate_v7() function. I have been using a SQL function for this, but a C function would be much faster!

[fboulnois]

As I outline in my comments on the RFC, I don't really like the alternate pseudorandom constructions. I'd also like to keep this extension as simple as possible so that there is only one way to build v7 UUIDs. As a result it is unlikely I will implement this.

[msqr]

Understood, thanks. I also hadn't noticed that the v7 draft has advanced since I started working with it, so for me I'm glad to see an optional additional time precision provision has been added to the proposal.

[msqr]

I appreciate you said you were unlikely to add the optional extra time precision support into this extension, but I thought I'd have a go at trying to add this, and I thought the change could be quite focused with the addition of a default/optional argument to the generate_uuid_v7() function like generate_uuid_v7(extra_ts_precision int = 0). The extra time precision is genuinely useful in many use cases, and would allow users to opt-in as desired.

I started playing around and was able to get something started along these lines, just for testing purposes, but my Postgres extension experience is limited and thought you might have more thoughts on the matter.

[fboulnois]

I started playing around and was able to get something started along these lines, just for testing purposes, but my Postgres extension experience is limited and thought you might have more thoughts on the matter.

It might be easier to initially extract microseconds and then split it into millis and micros, but otherwise your approach seems reasonable to me.

[msqr]

I've flushed out the fractional millisecond support now, including updating the README. I also added some basic tests (run via make installcheck). I'm not sure the code is as efficient as it could be, or bug free, but it does seem to be performing well in my testing.

If you ever change your mind and would be interested in a PR, I'm happy to create one.

[fboulnois]

I am glad you were able to find a solution to your issue. With the addition of uuid_timestamptz_to_v7 I would describe my extension as feature complete. We can always revisit this decision if the circumstances change.