public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v31 06/11] pg_ls_logdir to ignore error if initial/top dir is missing..
19+ messages / 6 participants
[nested] [flat]
* [PATCH v31 06/11] pg_ls_logdir to ignore error if initial/top dir is missing..
@ 2020-03-06 23:23 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 19+ messages in thread
From: Justin Pryzby @ 2020-03-06 23:23 UTC (permalink / raw)
..since ./log is created dynamically and not by initdb
---
src/backend/utils/adt/genfile.c | 2 +-
src/test/regress/input/tablespace.source | 4 ++++
src/test/regress/output/tablespace.source | 7 +++++++
3 files changed, 12 insertions(+), 1 deletion(-)
diff --git a/src/backend/utils/adt/genfile.c b/src/backend/utils/adt/genfile.c
index eed71892bd..c381a170ad 100644
--- a/src/backend/utils/adt/genfile.c
+++ b/src/backend/utils/adt/genfile.c
@@ -680,7 +680,7 @@ pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, int flags)
Datum
pg_ls_logdir(PG_FUNCTION_ARGS)
{
- return pg_ls_dir_files(fcinfo, Log_directory, LS_DIR_COMMON);
+ return pg_ls_dir_files(fcinfo, Log_directory, LS_DIR_COMMON | LS_DIR_MISSING_OK);
}
/* Function to return the list of files in the WAL directory */
diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source
index e69fa17004..da624020b2 100644
--- a/src/test/regress/input/tablespace.source
+++ b/src/test/regress/input/tablespace.source
@@ -16,6 +16,10 @@ CREATE TABLESPACE regress_tblspace LOCATION '@testtablespace@';
-- The query is written to ERROR if the tablespace doesn't exist, rather than silently failing to call pg_ls_tmpdir()
SELECT c.* FROM (SELECT oid FROM pg_tablespace b WHERE b.spcname='regress_tblspace' UNION SELECT 0 ORDER BY 1 DESC LIMIT 1) AS b , pg_ls_tmpdir(oid) AS c WHERE c.name='Does not exist';
+-- This tests the missing_ok parameter. If that's not functioning, this would ERROR if the logdir doesn't exist yet.
+-- The name='' condition is never true, so the function runs to completion but returns zero rows.
+SELECT * FROM pg_ls_logdir() WHERE name='Does not exist';
+
-- try setting and resetting some properties for the new tablespace
ALTER TABLESPACE regress_tblspace SET (random_page_cost = 1.0, seq_page_cost = 1.1);
ALTER TABLESPACE regress_tblspace SET (some_nonexistent_parameter = true); -- fail
diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source
index c146a4c129..3689bfac6d 100644
--- a/src/test/regress/output/tablespace.source
+++ b/src/test/regress/output/tablespace.source
@@ -21,6 +21,13 @@ SELECT c.* FROM (SELECT oid FROM pg_tablespace b WHERE b.spcname='regress_tblspa
------+------+--------------+-------
(0 rows)
+-- This tests the missing_ok parameter. If that's not functioning, this would ERROR if the logdir doesn't exist yet.
+-- The name='' condition is never true, so the function runs to completion but returns zero rows.
+SELECT * FROM pg_ls_logdir() WHERE name='Does not exist';
+ name | size | modification | isdir
+------+------+--------------+-------
+(0 rows)
+
-- try setting and resetting some properties for the new tablespace
ALTER TABLESPACE regress_tblspace SET (random_page_cost = 1.0, seq_page_cost = 1.1);
ALTER TABLESPACE regress_tblspace SET (some_nonexistent_parameter = true); -- fail
--
2.17.0
--qZVVwWJgpX9Jzs7f
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v31-0007-pg_ls_-dir-to-return-all-the-metadata-from-pg_st.patch"
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: UUID v7
@ 2024-03-06 07:13 Peter Eisentraut <[email protected]>
0 siblings, 1 reply; 19+ messages in thread
From: Peter Eisentraut @ 2024-03-06 07:13 UTC (permalink / raw)
To: Andrey M. Borodin <[email protected]>; +Cc: Sergey Prokhorenko <[email protected]>; Jelte Fennema-Nio <[email protected]>; pgsql-hackers; Aleksander Alekseev <[email protected]>; Przemysław Sztoch <[email protected]>; David G. Johnston <[email protected]>; Mat Arye <[email protected]>; Matthias van de Meent <[email protected]>; Nikolay Samokhvalov <[email protected]>; Junwang Zhao <[email protected]>
On 30.01.24 14:35, Andrey M. Borodin wrote:
>> On 30 Jan 2024, at 15:33, Junwang Zhao <[email protected]> wrote:
>>
>> It's always good to add a newline at the end of a source file, though
>> this might be nitpicky.
>
> Thanks, also fixed warning found by CFBot.
I have various comments on this patch:
- doc/src/sgml/func.sgml
The documentation of the new functions should be broken up a bit.
It's all one paragraph now. At least make it several paragraphs, or
possibly tables or something else.
Avoid listing the functions twice: Once before the description and
then again in the description. That's just going to get out of date.
The first listing is not necessary, I think.
The return values in the documentation should use the public-facing
type names, like "timestamp with time zone" and "smallint".
The descriptions of the UUID generation functions use handwavy
language in their descriptions, like "It provides much better data
locality" or "unacceptable security or business intelligence
implications", which isn't useful. Either we cut that all out and
just say, it creates a UUIDv7, done, look elsewhere for more
information, or we provide some more concretely useful details.
We shouldn't label a link as "IETF standard" when it's actually a
draft.
- src/include/catalog/pg_proc.dat
The description of uuidv4 should be "generate UUID version 4", so that
it parallels uuidv7.
The description of uuid_extract_time says 'extract timestamp from UUID
version 7', the implementation is not limited to version 7.
I think uuid_extract_time should be named uuid_extract_timestamp,
because it extracts a timestamp, not a time.
The functions uuid_extract_ver and uuid_extract_var could be named
uuid_extract_version and uuid_extract_variant. Otherwise, it's hard
to tell them apart, with only one letter different.
- src/test/regress/sql/uuid.sql
Why are the tests using the input format '{...}', which is not the
standard one?
- src/backend/utils/adt/uuid.c
All this new code should have more comments. There is a lot of bit
twiddling going on, and I suppose one is expected to follow along in
the RFC? At least each function should have a header comment, so one
doesn't have to check in pg_proc.dat what it's supposed to do.
I'm suspicious that these functions all appear to return null for
erroneous input, rather than raising errors. I think at least some
explanation for this should be recorded somewhere.
I think the behavior of uuid_extract_var(iant) is wrong. The code
takes just two bits to return, but the draft document is quite clear
that the variant is 4 bits (see Table 1).
The uuidv7 function could really use a header comment that explains
the choices that were made. The RFC draft provides various options
that implementations could use; we should describe which ones we
chose.
I would have expected that, since gettimeofday() provides microsecond
precision, we'd put the extra precision into "rand_a" as per Section 6.2
method 3.
You use some kind of counter, but could you explain which method that
counter implements?
I don't see any acknowledgment of issues relating to concurrency or
restarts. Like, how do we prevent duplicates being generated by
concurrent sessions or between restarts? Maybe the counter or random
stuff does that, but it's not explained.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: UUID v7
@ 2024-03-10 12:59 Andrey M. Borodin <[email protected]>
parent: Peter Eisentraut <[email protected]>
0 siblings, 1 reply; 19+ messages in thread
From: Andrey M. Borodin @ 2024-03-10 12:59 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; +Cc: Sergey Prokhorenko <[email protected]>; Jelte Fennema-Nio <[email protected]>; pgsql-hackers; Aleksander Alekseev <[email protected]>; Przemysław Sztoch <[email protected]>; David G. Johnston <[email protected]>; Mat Arye <[email protected]>; Matthias van de Meent <[email protected]>; Nikolay Samokhvalov <[email protected]>; Junwang Zhao <[email protected]>
Hi Peter,
thank you for so thoughtful review.
> On 6 Mar 2024, at 12:13, Peter Eisentraut <[email protected]> wrote:
>
> I have various comments on this patch:
>
>
> - doc/src/sgml/func.sgml
>
> The documentation of the new functions should be broken up a bit.
> It's all one paragraph now. At least make it several paragraphs, or
> possibly tables or something else.
I've split functions to generate UUIDs from functions to extract stuff.
>
> Avoid listing the functions twice: Once before the description and
> then again in the description. That's just going to get out of date.
> The first listing is not necessary, I think.
Fixed.
> The return values in the documentation should use the public-facing
> type names, like "timestamp with time zone" and "smallint".
Fixed.
> The descriptions of the UUID generation functions use handwavy
> language in their descriptions, like "It provides much better data
> locality" or "unacceptable security or business intelligence
> implications", which isn't useful. Either we cut that all out and
> just say, it creates a UUIDv7, done, look elsewhere for more
> information, or we provide some more concretely useful details.
I've removed all that stuff entirely.
> We shouldn't label a link as "IETF standard" when it's actually a
> draft.
Fixed.
Well, all my modifications of documentation are kind of blind... I tried to "make docs", but it gives me gazilion of errors... Is there an easy way to see resulting HTML?
> - src/include/catalog/pg_proc.dat
>
> The description of uuidv4 should be "generate UUID version 4", so that
> it parallels uuidv7.
Fixed.
> The description of uuid_extract_time says 'extract timestamp from UUID
> version 7', the implementation is not limited to version 7.
Fixed.
> I think uuid_extract_time should be named uuid_extract_timestamp,
> because it extracts a timestamp, not a time.
Renamed.
> The functions uuid_extract_ver and uuid_extract_var could be named
> uuid_extract_version and uuid_extract_variant. Otherwise, it's hard
> to tell them apart, with only one letter different.
Renamed.
> - src/test/regress/sql/uuid.sql
>
> Why are the tests using the input format '{...}', which is not the
> standard one?
Fixed.
> - src/backend/utils/adt/uuid.c
>
> All this new code should have more comments. There is a lot of bit
> twiddling going on, and I suppose one is expected to follow along in
> the RFC? At least each function should have a header comment, so one
> doesn't have to check in pg_proc.dat what it's supposed to do.
I've added some header comment. One big comment is attached to v7, I tried to take parts mostly from RFC. Yet there are a lot of my additions that now need review...
> I'm suspicious that these functions all appear to return null for
> erroneous input, rather than raising errors. I think at least some
> explanation for this should be recorded somewhere.
The input is not erroneous per se.
But the fact that
# select 1/0;
ERROR: division by zero
makes me consider throwing an error. There was some argumentation upthread for not throwing error though, but now I cannot find it... maybe I accepted this behaviour as more user-friendly.
> I think the behavior of uuid_extract_var(iant) is wrong. The code
> takes just two bits to return, but the draft document is quite clear
> that the variant is 4 bits (see Table 1).
Well, it was correct only for implemented variant. I've made version that implements full table 1 from section 4.1.
> The uuidv7 function could really use a header comment that explains
> the choices that were made. The RFC draft provides various options
> that implementations could use; we should describe which ones we
> chose.
Done.
>
> I would have expected that, since gettimeofday() provides microsecond
> precision, we'd put the extra precision into "rand_a" as per Section 6.2 method 3.
I had chosen method 2 over method 3 as most portable. Can we be sure how many bits (after reading milliseconds) are there across different OSes? Even if we put extra 10 bits of timestamp, we cannot extract safely them.
These bits could promote inter-backend stortability. E.i. when many backends generate data fast - this data is still somewhat ordered even within 1ms. But I think benefits of this sortability are outweighed by portability(unknown real resolution), simplicity(we don't store microseconds, thus do not try to extract them).
All this arguments are weak, but if one method would be strictly better than another - there would be only one method.
>
> You use some kind of counter, but could you explain which method that
> counter implements?
I described counter in uuidv7() header.
>
> I don't see any acknowledgment of issues relating to concurrency or
> restarts. Like, how do we prevent duplicates being generated by
> concurrent sessions or between restarts? Maybe the counter or random
> stuff does that, but it's not explained.
I think restart takes more than 1ms, so this is covered with time tick.
I've added paragraph about frequency of generation in uuidv7() header.
Best regards, Andrey Borodin.
Attachments:
[application/octet-stream] v18-0001-Implement-UUID-v7.patch (20.3K, ../../[email protected]/2-v18-0001-Implement-UUID-v7.patch)
download | inline diff:
From 8d978d0b8fd07f94dd64f1c0fedb93d0555919bc Mon Sep 17 00:00:00 2001
From: "Andrey M. Borodin" <[email protected]>
Date: Sun, 20 Aug 2023 23:55:31 +0300
Subject: [PATCH v18] Implement UUID v7
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
This commit adds function for UUID generation. Most important function here
is uuidv7() which generates new UUID according to the new standard.
For code readability this commit adds alias uuidv4() to function gen_random_uuid().
Also we add a function to extract timestamp from UUID v1, v6 and v7.
To allow user to distinguish various UUID versions and variants
we add functions uuid_extract_version() and uuid_extract_variant().
Author: Andrey Borodin
Reviewers: Sergey Prokhorenko, Kirk Wolak, Przemysław Sztoch
Reviewers: Nikolay Samokhvalov, Jelte Fennema-Nio, Aleksander Alekseev
Reviewers: Peter Eisentraut, Chris Travers, Lukas Fittl
Discussion: https://postgr.es/m/CAAhFRxitJv%3DyoGnXUgeLB_O%2BM7J2BJAmb5jqAT9gZ3bij3uLDA%40mail.gmail.com
---
doc/src/sgml/func.sgml | 54 ++++-
src/backend/utils/adt/uuid.c | 278 +++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 15 ++
src/include/datatype/timestamp.h | 3 +-
src/test/regress/expected/opr_sanity.out | 5 +
src/test/regress/expected/uuid.out | 71 ++++++
src/test/regress/sql/uuid.sql | 26 +++
7 files changed, 448 insertions(+), 4 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 5030a1045f..afb9ca12ee 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14130,13 +14130,61 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
<primary>gen_random_uuid</primary>
</indexterm>
+ <indexterm>
+ <primary>uuidv4</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>uuidv7</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>uuid_extract_timestamp</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>uuid_extract_version</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>uuid_extract_variant</primary>
+ </indexterm>
+
<para>
- <productname>PostgreSQL</productname> includes one function to generate a UUID:
+ <productname>PostgreSQL</productname> includes several functions to generate a UUID.
<synopsis>
<function>gen_random_uuid</function> () <returnvalue>uuid</returnvalue>
+<function>uuidv4</function> () <returnvalue>uuid</returnvalue>
+</synopsis>
+ These functions return a version 4 (random) UUID.
+<synopsis>
+<function>uuidv7</function> () <returnvalue>uuid</returnvalue>
+</synopsis>
+ This function returns a version 7 UUID (UNIX timestamp with 1ms precision +
+ randomly seeded counter + random).
+<synopsis>
+ </para>
+
+ <para>
+<function>uuid_extract_timestamp</function> (uuid) <returnvalue>timestamptz</returnvalue>
+</synopsis>
+ This function extracts a <type>timestamp with time zone</type> from UUID versions 1, 6 and 7.
+ For other versions and variants this function returns NULL. The extracted timestamp
+ does not necessarily equate to the time of UUID generation. How close it is
+ to the actual time depends on the implementation that generated to UUID.
+ The uuidv7() function provided by PostgreSQL will normally store the actual time,
+ with some exceptions: prevention of time leaps backwards and counter overflow
+ being carried to time step.
+<synopsis>
+<function>uuid_extract_version</function> (uuid) <returnvalue>smallint</returnvalue>
+</synopsis>
+ This function extracts a version bits from UUID of variant described by
+ <ulink url="https://datatracker.ietf.org/doc/html/draft-ietf-uuidrev-rfc4122bis">IETF standard draft</ulink>
+ (b10xx variant). For other variants this function returns NULL.
+<synopsis>
+<function>uuid_extract_variant</function> (uuid) <returnvalue>smallint</returnvalue>
</synopsis>
- This function returns a version 4 (random) UUID. This is the most commonly
- used type of UUID and is appropriate for most applications.
+ This function extracts a vartiant bits from UUID.
</para>
<para>
diff --git a/src/backend/utils/adt/uuid.c b/src/backend/utils/adt/uuid.c
index 73dfd711c7..72faec3e66 100644
--- a/src/backend/utils/adt/uuid.c
+++ b/src/backend/utils/adt/uuid.c
@@ -13,13 +13,18 @@
#include "postgres.h"
+#include <sys/time.h>
+
+#include "access/xlog.h"
#include "common/hashfn.h"
#include "lib/hyperloglog.h"
#include "libpq/pqformat.h"
#include "port/pg_bswap.h"
#include "utils/builtins.h"
+#include "utils/datetime.h"
#include "utils/guc.h"
#include "utils/sortsupport.h"
+#include "utils/timestamp.h"
#include "utils/uuid.h"
/* sortsupport for uuid */
@@ -402,6 +407,11 @@ uuid_hash_extended(PG_FUNCTION_ARGS)
return hash_any_extended(key->data, UUID_LEN, PG_GETARG_INT64(1));
}
+/*
+ * Routine to generate UUID version 4.
+ * All UUID bytes are filled with strong random numbers except version and
+ * variant 0b10 bits.
+ */
Datum
gen_random_uuid(PG_FUNCTION_ARGS)
{
@@ -421,3 +431,271 @@ gen_random_uuid(PG_FUNCTION_ARGS)
PG_RETURN_UUID_P(uuid);
}
+
+static uint32_t sequence_counter;
+static uint64_t previous_timestamp = 0;
+
+/*
+ * Routine to generate UUID version 7.
+ * Following description is taken from RFC draft and slightly extended to
+ * reflect implementation specific choices.
+ *
+ * UUIDv7 Field and Bit Layout:
+ * ----------
+ * 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 | rand_a |
+ * +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
+ * |var| rand_b |
+ * +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
+ * | rand_b |
+ * +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
+ *
+ * unix_ts_ms:
+ * 48 bit big-endian unsigned number of Unix epoch timestamp in milliseconds
+ * as per Section 6.1. Occupies bits 0 through 47 (octets 0-5).
+ *
+ * ver:
+ * The 4 bit version field as defined by Section 4.2, set to 0b0111 (7).
+ * Occupies bits 48 through 51 of octet 6.
+ *
+ * rand_a:
+ * Most significant 12 bits of 18-bit counter. This counter is designed to
+ * guarantee additional monotonicity as per Section 6.2 (Method 2). rand_a
+ * occupies bits 52 through 63 (octets 6-7).
+ *
+ * var:
+ * The 2 bit variant field as defined by Section 4.1, set to 0b10. Occupies
+ * bits 64 and 65 of octet 8.
+ *
+ * rand_b:
+ * Starting 6 bits are least significant 6 bits of a counter. The final 56
+ * bits filled with pseudo-random data to provide uniqueness as per
+ * Section 6.9. rand_b Occupies bits 66 through 127 (octets 8-15).
+ * ----------
+ *
+ * Monotonic Random (Method 2) can be implemented with arbitrary size of a
+ * counter. We choose size 18 to reuse all space of bytes that are touched by
+ * ver and var fields + rand_a bytes between them.
+ * Whenever timestamp unix_ts_ms is moving forward, this counter bits are
+ * reinitialized. Rinilialization always sets most significant bit to 0, other
+ * bits are initialized with random numbers. This gives as approximately 192K
+ * UUIDs within one millisecond without overflow. Outh to be enough.
+ * Whenever counter overflow happens, this overflow is translated to increment
+ * of unix_ts_ms. So generation of UUIDs ate rate higher than 128MHz might lead
+ * to using timestamps ahead of time.
+ *
+ * All UUID generator state is backend-local. For UUIDs generated in one
+ * backend we guarantee monotonicity. UUIDs generated on different backends
+ * will be mostly monotonic if they are generated at frequences less than 1KHz,
+ * but this monotonicity is not strictly guaranteed. UUIDs generated on
+ * different nodes are mostly monotonic with regards to possible clock drift.
+ */
+Datum
+uuidv7(PG_FUNCTION_ARGS)
+{
+ pg_uuid_t *uuid = palloc(UUID_LEN);
+ uint64_t tms;
+ struct timeval tp;
+ bool increment_counter;
+
+ gettimeofday(&tp, NULL);
+ tms = ((uint64_t)tp.tv_sec) * 1000 + (tp.tv_usec) / 1000;
+ /* time from clock is protected from backward leaps */
+ increment_counter = (tms <= previous_timestamp);
+
+ if (increment_counter)
+ {
+ /* Time did not advance from the previous generation, we must increment counter */
+ ++sequence_counter;
+ if (sequence_counter > 0x3ffff)
+ {
+ /* We only have 18-bit counter */
+ sequence_counter = 0;
+ previous_timestamp++;
+ }
+
+ /* protection from leap backward */
+ tms = previous_timestamp;
+
+ /* fill everything after the timestamp and counter with random bytes */
+ if (!pg_strong_random(&uuid->data[8], UUID_LEN - 8))
+ ereport(ERROR,
+ (errcode(ERRCODE_INTERNAL_ERROR),
+ errmsg("could not generate random values")));
+
+ /* most significant 4 bits of 18-bit counter */
+ uuid->data[6] = (unsigned char)(sequence_counter >> 14);
+ /* next 8 bits */
+ uuid->data[7] = (unsigned char)(sequence_counter >> 6);
+ /* least significant 6 bits */
+ uuid->data[8] = (unsigned char)(sequence_counter);
+ }
+ else
+ {
+ /* fill everything after the timestamp with random bytes */
+ if (!pg_strong_random(&uuid->data[6], UUID_LEN - 6))
+ ereport(ERROR,
+ (errcode(ERRCODE_INTERNAL_ERROR),
+ errmsg("could not generate random values")));
+
+ /*
+ * Left-most counter bits are initialized as zero for the sole purpose
+ * of guarding against counter rollovers.
+ * See section "Fixed-Length Dedicated Counter Seeding"
+ * https://datatracker.ietf.org/doc/html/draft-ietf-uuidrev-rfc4122bis-09#monotonicity_counters
+ */
+ uuid->data[6] = (uuid->data[6] & 0xf7);
+
+ /* read randomly initialized bits of counter */
+ sequence_counter = ((uint32_t)uuid->data[8] & 0x3f) +
+ (((uint32_t)uuid->data[7]) << 6) +
+ (((uint32_t)uuid->data[6] & 0x0f) << 14);
+
+ previous_timestamp = tms;
+ }
+
+ /* Fill in time part */
+ uuid->data[0] = (unsigned char)(tms >> 40);
+ uuid->data[1] = (unsigned char)(tms >> 32);
+ uuid->data[2] = (unsigned char)(tms >> 24);
+ uuid->data[3] = (unsigned char)(tms >> 16);
+ uuid->data[4] = (unsigned char)(tms >> 8);
+ uuid->data[5] = (unsigned char)tms;
+
+ /*
+ * Set magic numbers for a "version 7" (pseudorandom) UUID, see
+ * https://datatracker.ietf.org/doc/html/draft-ietf-uuidrev-rfc4122bis
+ */
+ /* set version field, top four bits are 0, 1, 1, 1 */
+ uuid->data[6] = (uuid->data[6] & 0x0f) | 0x70;
+ /* set variant field, top two bits are 1, 0 */
+ uuid->data[8] = (uuid->data[8] & 0x3f) | 0x80;
+
+ PG_RETURN_UUID_P(uuid);
+}
+
+/*
+ * Routine to extract UUID version from variant 0b10
+ * Returns NULL if UUID is not 0b10 or version is not 1,6 or7.
+ */
+Datum
+uuid_extract_timestamp(PG_FUNCTION_ARGS)
+{
+ pg_uuid_t *uuid = PG_GETARG_UUID_P(0);
+ TimestampTz ts;
+ uint64_t tms;
+
+ if ((uuid->data[8] & 0xc0) != 0x80)
+ PG_RETURN_NULL();
+
+ if ((uuid->data[6] & 0xf0) == 0x70)
+ {
+ tms = uuid->data[5];
+ tms += ((uint64_t)uuid->data[4]) << 8;
+ tms += ((uint64_t)uuid->data[3]) << 16;
+ tms += ((uint64_t)uuid->data[2]) << 24;
+ tms += ((uint64_t)uuid->data[1]) << 32;
+ tms += ((uint64_t)uuid->data[0]) << 40;
+
+ ts = (TimestampTz) (tms * 1000) - /* convert ms to us, than adjust */
+ (POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY * USECS_PER_SEC;
+
+ PG_RETURN_TIMESTAMPTZ(ts);
+ }
+
+ if ((uuid->data[6] & 0xf0) == 0x10)
+ {
+ tms = ((uint64_t)uuid->data[0]) << 24;
+ tms += ((uint64_t)uuid->data[1]) << 16;
+ tms += ((uint64_t)uuid->data[2]) << 8;
+ tms += ((uint64_t)uuid->data[3]);
+ tms += ((uint64_t)uuid->data[4]) << 40;
+ tms += ((uint64_t)uuid->data[5]) << 32;
+ tms += (((uint64_t)uuid->data[6])&0xf) << 56;
+ tms += ((uint64_t)uuid->data[7]) << 48;
+
+ ts = (TimestampTz) (tms / 10) - /* convert 100-ns intervals to us, than adjust */
+ ((uint64_t)POSTGRES_EPOCH_JDATE - GREGORIAN_EPOCH_JDATE) * SECS_PER_DAY * USECS_PER_SEC;
+
+ PG_RETURN_TIMESTAMPTZ(ts);
+ }
+
+ if ((uuid->data[6] & 0xf0) == 0x60)
+ {
+ tms = ((uint64_t)uuid->data[0]) << 52;
+ tms += ((uint64_t)uuid->data[1]) << 44;
+ tms += ((uint64_t)uuid->data[2]) << 36;
+ tms += ((uint64_t)uuid->data[3]) << 28;
+ tms += ((uint64_t)uuid->data[4]) << 20;
+ tms += ((uint64_t)uuid->data[5]) << 12;
+ tms += (((uint64_t)uuid->data[6])&0xf) << 8;
+ tms += ((uint64_t)uuid->data[7]);
+
+ ts = (TimestampTz) (tms / 10) - /* convert 100-ns intervals to us, than adjust */
+ ((uint64_t)POSTGRES_EPOCH_JDATE - GREGORIAN_EPOCH_JDATE) * SECS_PER_DAY * USECS_PER_SEC;
+
+ PG_RETURN_TIMESTAMPTZ(ts);
+ }
+
+ PG_RETURN_NULL();
+}
+
+/*
+ * Routine to extract UUID version from variant 0b10
+ * Returns NULL if UUID is not 0b10
+ */
+Datum
+uuid_extract_version(PG_FUNCTION_ARGS)
+{
+ pg_uuid_t *uuid = PG_GETARG_UUID_P(0);
+ uint16_t result;
+
+ if ((uuid->data[8] & 0xc0) != 0x80)
+ PG_RETURN_NULL();
+ result = uuid->data[6] >> 4;
+
+ PG_RETURN_UINT16(result);
+}
+
+/*
+ * Routine to extract UUID variant. Can return only 0, 0b10, 0b110 and 0b111.
+ */
+Datum
+uuid_extract_variant(PG_FUNCTION_ARGS)
+{
+ pg_uuid_t *uuid = PG_GETARG_UUID_P(0);
+ uint16_t result;
+
+ /*
+ * The contents of the variant field, where the letter "x" indicates a
+ * "don't-care" value.
+ * ----------
+ * Msb0 Msb1 Msb2 Msb3 Variant Description
+ * 0 x x x 1-7 Reserved, NCS backward
+ * compatibility and includes Nil
+ * UUID as per Section 5.9.
+ * 1 0 x x 8-9,A-B The variant specified in RFC.
+ * 1 1 0 x C-D Reserved, Microsoft Corporation
+ * backward compatibility.
+ * 1 1 1 x E-F Reserved for future definition
+ * and includes Max UUID as per
+ * Section 5.10 of RFC.
+ * ----------
+ */
+
+ uint8_t nibble = uuid->data[8] >> 4;
+ if (nibble < 8)
+ result = 0;
+ else if (nibble < 0xC)
+ result = 0b10;
+ else if (nibble < 0xE)
+ result = 0b110;
+ else
+ result = 0b111;
+
+ PG_RETURN_UINT16(result);
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index e4115cd084..eb1d075595 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -9174,6 +9174,21 @@
{ oid => '3432', descr => 'generate random UUID',
proname => 'gen_random_uuid', proleakproof => 't', provolatile => 'v',
prorettype => 'uuid', proargtypes => '', prosrc => 'gen_random_uuid' },
+{ oid => '9895', descr => 'generate UUID version 4',
+ proname => 'uuidv4', proleakproof => 't', provolatile => 'v',
+ prorettype => 'uuid', proargtypes => '', prosrc => 'gen_random_uuid' },
+{ oid => '9896', descr => 'generate UUID version 7',
+ proname => 'uuidv7', proleakproof => 't', provolatile => 'v',
+ prorettype => 'uuid', proargtypes => '', prosrc => 'uuidv7' },
+{ oid => '9897', descr => 'extract timestamp from UUID version 1, 6 or 7',
+ proname => 'uuid_extract_timestamp', proleakproof => 't',
+ prorettype => 'timestamptz', proargtypes => 'uuid', prosrc => 'uuid_extract_timestamp' },
+{ oid => '9898', descr => 'extract version from RFC 4122 UUID',
+ proname => 'uuid_extract_version', proleakproof => 't',
+ prorettype => 'int2', proargtypes => 'uuid', prosrc => 'uuid_extract_version' },
+{ oid => '9899', descr => 'extract variant from UUID',
+ proname => 'uuid_extract_variant', proleakproof => 't',
+ prorettype => 'int2', proargtypes => 'uuid', prosrc => 'uuid_extract_variant' },
# pg_lsn
{ oid => '3229', descr => 'I/O',
diff --git a/src/include/datatype/timestamp.h b/src/include/datatype/timestamp.h
index 3a37cb661e..652aeb428e 100644
--- a/src/include/datatype/timestamp.h
+++ b/src/include/datatype/timestamp.h
@@ -230,9 +230,10 @@ struct pg_itm_in
((y) < JULIAN_MAXYEAR || \
((y) == JULIAN_MAXYEAR && ((m) < JULIAN_MAXMONTH))))
-/* Julian-date equivalents of Day 0 in Unix and Postgres reckoning */
+/* Julian-date equivalents of Day 0 in Unix, Postgres and Gregorian epochs */
#define UNIX_EPOCH_JDATE 2440588 /* == date2j(1970, 1, 1) */
#define POSTGRES_EPOCH_JDATE 2451545 /* == date2j(2000, 1, 1) */
+#define GREGORIAN_EPOCH_JDATE 2299161 /* == date2j(1582,10,15) */
/*
* Range limits for dates and timestamps.
diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index 7610b011d6..44542c56ef 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -872,6 +872,11 @@ xid8ge(xid8,xid8)
xid8eq(xid8,xid8)
xid8ne(xid8,xid8)
xid8cmp(xid8,xid8)
+uuidv4()
+uuidv7()
+uuid_extract_timestamp(uuid)
+uuid_extract_version(uuid)
+uuid_extract_variant(uuid)
-- restore normal output mode
\a\t
-- List of functions used by libpq's fe-lobj.c
diff --git a/src/test/regress/expected/uuid.out b/src/test/regress/expected/uuid.out
index 8e7f21910d..2930bcc7f0 100644
--- a/src/test/regress/expected/uuid.out
+++ b/src/test/regress/expected/uuid.out
@@ -168,5 +168,76 @@ SELECT count(DISTINCT guid_field) FROM guid1;
2
(1 row)
+-- test of uuidv4() alias
+TRUNCATE guid1;
+INSERT INTO guid1 (guid_field) VALUES (uuidv4());
+INSERT INTO guid1 (guid_field) VALUES (uuidv4());
+SELECT count(DISTINCT guid_field) FROM guid1;
+ count
+-------
+ 2
+(1 row)
+
+-- generation test for v7
+TRUNCATE guid1;
+INSERT INTO guid1 (guid_field) VALUES (uuidv7());
+INSERT INTO guid1 (guid_field) VALUES (uuidv7());
+SELECT count(DISTINCT guid_field) FROM guid1;
+ count
+-------
+ 2
+(1 row)
+
+-- support functions for UUID versions and variants
+SELECT uuid_extract_version(uuidv7());
+ uuid_extract_version
+----------------------
+ 7
+(1 row)
+
+SELECT uuid_extract_version('11111111-1111-1111-1111-111111111111') IS NULL;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT uuid_extract_version('11111111-1111-5111-8111-111111111111');
+ uuid_extract_version
+----------------------
+ 5
+(1 row)
+
+SELECT uuid_extract_variant(uuidv7());
+ uuid_extract_variant
+----------------------
+ 2
+(1 row)
+
+-- uuid_extract_timestamp() must refuse to accept non-UUIDv7
+SELECT uuid_extract_timestamp(gen_random_uuid());
+ uuid_extract_timestamp
+------------------------
+
+(1 row)
+
+-- extract UUID v1, v6 and v7 timestamp
+SELECT uuid_extract_timestamp('C232AB00-9414-11EC-B3C8-9F6BDECED846') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT uuid_extract_timestamp('1EC9414C-232A-6B00-B3C8-9F6BDECED846') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT uuid_extract_timestamp('017F22E2-79B0-7CC3-98C4-DC0C0C07398F') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00';
+ ?column?
+----------
+ t
+(1 row)
+
-- clean up
DROP TABLE guid1, guid2 CASCADE;
diff --git a/src/test/regress/sql/uuid.sql b/src/test/regress/sql/uuid.sql
index 9a8f437c7d..0f9bd1a661 100644
--- a/src/test/regress/sql/uuid.sql
+++ b/src/test/regress/sql/uuid.sql
@@ -85,5 +85,31 @@ INSERT INTO guid1 (guid_field) VALUES (gen_random_uuid());
INSERT INTO guid1 (guid_field) VALUES (gen_random_uuid());
SELECT count(DISTINCT guid_field) FROM guid1;
+-- test of uuidv4() alias
+TRUNCATE guid1;
+INSERT INTO guid1 (guid_field) VALUES (uuidv4());
+INSERT INTO guid1 (guid_field) VALUES (uuidv4());
+SELECT count(DISTINCT guid_field) FROM guid1;
+
+-- generation test for v7
+TRUNCATE guid1;
+INSERT INTO guid1 (guid_field) VALUES (uuidv7());
+INSERT INTO guid1 (guid_field) VALUES (uuidv7());
+SELECT count(DISTINCT guid_field) FROM guid1;
+
+-- support functions for UUID versions and variants
+SELECT uuid_extract_version(uuidv7());
+SELECT uuid_extract_version('11111111-1111-1111-1111-111111111111') IS NULL;
+SELECT uuid_extract_version('11111111-1111-5111-8111-111111111111');
+SELECT uuid_extract_variant(uuidv7());
+
+-- uuid_extract_timestamp() must refuse to accept non-UUIDv7
+SELECT uuid_extract_timestamp(gen_random_uuid());
+
+-- extract UUID v1, v6 and v7 timestamp
+SELECT uuid_extract_timestamp('C232AB00-9414-11EC-B3C8-9F6BDECED846') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00';
+SELECT uuid_extract_timestamp('1EC9414C-232A-6B00-B3C8-9F6BDECED846') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00';
+SELECT uuid_extract_timestamp('017F22E2-79B0-7CC3-98C4-DC0C0C07398F') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00';
+
-- clean up
DROP TABLE guid1, guid2 CASCADE;
--
2.37.1 (Apple Git-137.1)
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: UUID v7
@ 2024-03-14 11:07 Peter Eisentraut <[email protected]>
parent: Andrey M. Borodin <[email protected]>
0 siblings, 1 reply; 19+ messages in thread
From: Peter Eisentraut @ 2024-03-14 11:07 UTC (permalink / raw)
To: Andrey M. Borodin <[email protected]>; +Cc: Sergey Prokhorenko <[email protected]>; Jelte Fennema-Nio <[email protected]>; pgsql-hackers; Aleksander Alekseev <[email protected]>; Przemysław Sztoch <[email protected]>; David G. Johnston <[email protected]>; Mat Arye <[email protected]>; Matthias van de Meent <[email protected]>; Nikolay Samokhvalov <[email protected]>; Junwang Zhao <[email protected]>
On 10.03.24 13:59, Andrey M. Borodin wrote:
>> The functions uuid_extract_ver and uuid_extract_var could be named
>> uuid_extract_version and uuid_extract_variant. Otherwise, it's hard
>> to tell them apart, with only one letter different.
>
> Renamed.
Another related comment: Throughout your patch, swap the order of
uuid_extract_variant and uuid_extract_version. First, this makes more
sense because version is subordinate to variant, and also it makes it
alphabetical.
>> I think the behavior of uuid_extract_var(iant) is wrong. The code
>> takes just two bits to return, but the draft document is quite clear
>> that the variant is 4 bits (see Table 1).
>
> Well, it was correct only for implemented variant. I've made version that implements full table 1 from section 4.1.
I think we are still interpreting this differently. I think
uuid_extract_variant should just return whatever is in those four bits.
Your function comment says "Can return only 0, 0b10, 0b110 and 0b111.",
which I don't think it is correct. It should return 0 through 15.
>> I would have expected that, since gettimeofday() provides microsecond
>> precision, we'd put the extra precision into "rand_a" as per Section 6.2 method 3.
>
> I had chosen method 2 over method 3 as most portable. Can we be sure how many bits (after reading milliseconds) are there across different OSes?
I think this should have been researched. If we don't know how many
bits we have, how do we know we have enough for milliseconds? I think
we should at least have some kind of idea, if we are going to have this
conversation.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: UUID v7
@ 2024-03-14 11:25 Andrey M. Borodin <[email protected]>
parent: Peter Eisentraut <[email protected]>
0 siblings, 1 reply; 19+ messages in thread
From: Andrey M. Borodin @ 2024-03-14 11:25 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; +Cc: Sergey Prokhorenko <[email protected]>; Jelte Fennema-Nio <[email protected]>; pgsql-hackers; Aleksander Alekseev <[email protected]>; Przemysław Sztoch <[email protected]>; David G. Johnston <[email protected]>; Mat Arye <[email protected]>; Matthias van de Meent <[email protected]>; Nikolay Samokhvalov <[email protected]>; Junwang Zhao <[email protected]>
> On 14 Mar 2024, at 16:07, Peter Eisentraut <[email protected]> wrote:
>
> On 10.03.24 13:59, Andrey M. Borodin wrote:
>>> The functions uuid_extract_ver and uuid_extract_var could be named
>>> uuid_extract_version and uuid_extract_variant. Otherwise, it's hard
>>> to tell them apart, with only one letter different.
>> Renamed.
>
> Another related comment: Throughout your patch, swap the order of uuid_extract_variant and uuid_extract_version. First, this makes more sense because version is subordinate to variant, and also it makes it alphabetical.
I will do it soon.
>
>>> I think the behavior of uuid_extract_var(iant) is wrong. The code
>>> takes just two bits to return, but the draft document is quite clear
>>> that the variant is 4 bits (see Table 1).
>> Well, it was correct only for implemented variant. I've made version that implements full table 1 from section 4.1.
>
> I think we are still interpreting this differently. I think uuid_extract_variant should just return whatever is in those four bits. Your function comment says "Can return only 0, 0b10, 0b110 and 0b111.", which I don't think it is correct. It should return 0 through 15.
We will return "do not care" bits. This bits can confuse someone. E.g. for varaint 0b10 we can return 8, 9, 10 and 11 randomly. Is it OK? BTW for some reason document lists number 1-15, but your are correct that range is 0-15.
>
>>> I would have expected that, since gettimeofday() provides microsecond
>>> precision, we'd put the extra precision into "rand_a" as per Section 6.2 method 3.
>> I had chosen method 2 over method 3 as most portable. Can we be sure how many bits (after reading milliseconds) are there across different OSes?
>
> I think this should have been researched. If we don't know how many bits we have, how do we know we have enough for milliseconds? I think we should at least have some kind of idea, if we are going to have this conversation.
Bits for milliseconds are strictly defined by the document: there are always 48 bits, independently from clock resolution.
But I don't think it's main problem for Method 3. Method 1 actually guarantees strictly increasing order of UUIDs generated by single backend. Method 3 can generate a lot of unsorted data in case of time leaping backward.
BTW Kyzer (in an off-list discussion) and Sergey confirmed that implemented method from the patch actually is Method 1.
Best regards, Andrey Borodin.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: UUID v7
@ 2024-03-14 15:10 Peter Eisentraut <[email protected]>
parent: Andrey M. Borodin <[email protected]>
0 siblings, 1 reply; 19+ messages in thread
From: Peter Eisentraut @ 2024-03-14 15:10 UTC (permalink / raw)
To: Andrey M. Borodin <[email protected]>; +Cc: Sergey Prokhorenko <[email protected]>; Jelte Fennema-Nio <[email protected]>; pgsql-hackers; Aleksander Alekseev <[email protected]>; Przemysław Sztoch <[email protected]>; David G. Johnston <[email protected]>; Mat Arye <[email protected]>; Matthias van de Meent <[email protected]>; Nikolay Samokhvalov <[email protected]>; Junwang Zhao <[email protected]>
On 14.03.24 12:25, Andrey M. Borodin wrote:
>>>> I think the behavior of uuid_extract_var(iant) is wrong. The code
>>>> takes just two bits to return, but the draft document is quite clear
>>>> that the variant is 4 bits (see Table 1).
>>> Well, it was correct only for implemented variant. I've made version that implements full table 1 from section 4.1.
>> I think we are still interpreting this differently. I think uuid_extract_variant should just return whatever is in those four bits. Your function comment says "Can return only 0, 0b10, 0b110 and 0b111.", which I don't think it is correct. It should return 0 through 15.
> We will return "do not care" bits. This bits can confuse someone. E.g. for varaint 0b10 we can return 8, 9, 10 and 11 randomly. Is it OK? BTW for some reason document lists number 1-15, but your are correct that range is 0-15.
I agree it's confusing. Before I studied the RFC 4122bis project, I
didn't even know about variant vs. version. I think overall people will
find this more confusing than useful. If you just want to know, "is
this UUID of the kind specified in RFC 4122", you can query it with
uuid_extract_version(x) IS NOT NULL. So maybe we don't need the
_extract_variant function?
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: UUID v7
@ 2024-03-14 18:06 Andrey M. Borodin <[email protected]>
parent: Peter Eisentraut <[email protected]>
0 siblings, 1 reply; 19+ messages in thread
From: Andrey M. Borodin @ 2024-03-14 18:06 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; +Cc: Sergey Prokhorenko <[email protected]>; Jelte Fennema-Nio <[email protected]>; pgsql-hackers; Aleksander Alekseev <[email protected]>; Przemysław Sztoch <[email protected]>; David G. Johnston <[email protected]>; Mat Arye <[email protected]>; Matthias van de Meent <[email protected]>; Nikolay Samokhvalov <[email protected]>; Junwang Zhao <[email protected]>
> On 14 Mar 2024, at 20:10, Peter Eisentraut <[email protected]> wrote:
>
>>>>> I think the behavior of uuid_extract_var(iant) is wrong. The code
>>>>> takes just two bits to return, but the draft document is quite clear
>>>>> that the variant is 4 bits (see Table 1).
>>>> Well, it was correct only for implemented variant. I've made version that implements full table 1 from section 4.1.
>>> I think we are still interpreting this differently. I think uuid_extract_variant should just return whatever is in those four bits. Your function comment says "Can return only 0, 0b10, 0b110 and 0b111.", which I don't think it is correct. It should return 0 through 15.
>> We will return "do not care" bits. This bits can confuse someone. E.g. for varaint 0b10 we can return 8, 9, 10 and 11 randomly. Is it OK? BTW for some reason document lists number 1-15, but your are correct that range is 0-15.
>
> I agree it's confusing. Before I studied the RFC 4122bis project, I didn't even know about variant vs. version. I think overall people will find this more confusing than useful. If you just want to know, "is this UUID of the kind specified in RFC 4122", you can query it with uuid_extract_version(x) IS NOT NULL. So maybe we don't need the _extract_variant function?
I think it's the best possible solution. The variant has no value besides detecting if a version can be extracted.
Best regards, Andrey Borodin.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: UUID v7
@ 2024-03-15 09:47 Aleksander Alekseev <[email protected]>
parent: Andrey M. Borodin <[email protected]>
0 siblings, 1 reply; 19+ messages in thread
From: Aleksander Alekseev @ 2024-03-15 09:47 UTC (permalink / raw)
To: pgsql-hackers; +Cc: Andrey M. Borodin <[email protected]>; Peter Eisentraut <[email protected]>; Sergey Prokhorenko <[email protected]>; Jelte Fennema-Nio <[email protected]>; Przemysław Sztoch <[email protected]>; David G. Johnston <[email protected]>; Mat Arye <[email protected]>; Matthias van de Meent <[email protected]>; Nikolay Samokhvalov <[email protected]>; Junwang Zhao <[email protected]>
Hi,
> > So maybe we don't need the _extract_variant function?
>
> I think it's the best possible solution. The variant has no value besides detecting if a version can be extracted.
+1 to the idea. I doubt that anyone will miss it.
--
Best regards,
Aleksander Alekseev
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: UUID v7
@ 2024-03-16 17:43 Andrey M. Borodin <[email protected]>
parent: Aleksander Alekseev <[email protected]>
0 siblings, 1 reply; 19+ messages in thread
From: Andrey M. Borodin @ 2024-03-16 17:43 UTC (permalink / raw)
To: Aleksander Alekseev <[email protected]>; +Cc: pgsql-hackers; Peter Eisentraut <[email protected]>; Sergey Prokhorenko <[email protected]>; Jelte Fennema-Nio <[email protected]>; Przemysław Sztoch <[email protected]>; David G. Johnston <[email protected]>; Mat Arye <[email protected]>; Matthias van de Meent <[email protected]>; Nikolay Samokhvalov <[email protected]>; Junwang Zhao <[email protected]>
> On 15 Mar 2024, at 14:47, Aleksander Alekseev <[email protected]> wrote:
>
> +1 to the idea. I doubt that anyone will miss it.
PFA v22.
Changes:
1. Squashed all editorialisation by Jelte
2. Fixed my erroneous comments on using Method 2 (we are using method 1 instead)
3. Remove all traces of uuid_extract_variant()
Thanks!
Best regards, Andrey Borodin.
Attachments:
[application/octet-stream] v22-0001-Implement-UUID-v7.patch (19.1K, ../../[email protected]/2-v22-0001-Implement-UUID-v7.patch)
download | inline diff:
From d74968fe65bf0760723347b420bc2dcf8938fbb9 Mon Sep 17 00:00:00 2001
From: "Andrey M. Borodin" <[email protected]>
Date: Sun, 20 Aug 2023 23:55:31 +0300
Subject: [PATCH v22] Implement UUID v7
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
This commit adds function for UUID generation. Most important function here
is uuidv7() which generates new UUID according to the new standard.
For code readability this commit adds alias uuidv4() to function
gen_random_uuid().
Also we add a function to extract timestamp from UUID v1, v6 and v7.
To allow user to distinguish various UUID versions we add function
uuid_extract_version().
Author: Andrey Borodin
Reviewed-by: Sergey Prokhorenko, Kirk Wolak, Przemysław Sztoch
Reviewed-by: Nikolay Samokhvalov, Jelte Fennema-Nio, Aleksander Alekseev
Reviewed-by: Peter Eisentraut, Chris Travers, Lukas Fittl
Discussion: https://postgr.es/m/CAAhFRxitJv%3DyoGnXUgeLB_O%2BM7J2BJAmb5jqAT9gZ3bij3uLDA%40mail.gmail.com
---
doc/src/sgml/func.sgml | 46 +++-
src/backend/utils/adt/uuid.c | 254 ++++++++++++++++++++++-
src/include/catalog/pg_proc.dat | 12 ++
src/include/datatype/timestamp.h | 3 +-
src/test/regress/expected/opr_sanity.out | 4 +
src/test/regress/expected/uuid.out | 65 ++++++
src/test/regress/sql/uuid.sql | 25 +++
7 files changed, 404 insertions(+), 5 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 72c5175e3b..06a8f3a0d6 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14127,13 +14127,53 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
<primary>gen_random_uuid</primary>
</indexterm>
+ <indexterm>
+ <primary>uuidv4</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>uuidv7</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>uuid_extract_timestamp</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>uuid_extract_version</primary>
+ </indexterm>
+
<para>
- <productname>PostgreSQL</productname> includes one function to generate a UUID:
+ <productname>PostgreSQL</productname> includes several functions to generate a UUID.
<synopsis>
<function>gen_random_uuid</function> () <returnvalue>uuid</returnvalue>
+<function>uuidv4</function> () <returnvalue>uuid</returnvalue>
+</synopsis>
+ These functions return a version 4 (random) UUID.
+<synopsis>
+<function>uuidv7</function> () <returnvalue>uuid</returnvalue>
+</synopsis>
+ This function returns a version 7 UUID (UNIX timestamp with 1ms precision +
+ randomly seeded counter + random).
+ </para>
+
+ <para>
+<synopsis>
+<function>uuid_extract_timestamp</function> (uuid) <returnvalue>timestamptz</returnvalue>
+</synopsis>
+ This function extracts a <type>timestamp with time zone</type> from UUID versions 1, 6 and 7.
+ For other versions and variants this function returns NULL. The extracted timestamp
+ does not necessarily equate to the time of UUID generation. How close it is
+ to the actual time depends on the implementation that generated UUID.
+ The uuidv7() function provided by PostgreSQL will normally store the actual time,
+ with some exceptions: prevention of time leaps backwards and counter overflow
+ being carried to time step.
+<synopsis>
+<function>uuid_extract_version</function> (uuid) <returnvalue>smallint</returnvalue>
</synopsis>
- This function returns a version 4 (random) UUID. This is the most commonly
- used type of UUID and is appropriate for most applications.
+ This function extracts a version bits from UUID of variant described by
+ <ulink url="https://datatracker.ietf.org/doc/html/draft-ietf-uuidrev-rfc4122bis">IETF standard draft</ulink>
+ (b10xx variant). For other variants this function returns NULL.
</para>
<para>
diff --git a/src/backend/utils/adt/uuid.c b/src/backend/utils/adt/uuid.c
index e9c1ec6153..80bce72276 100644
--- a/src/backend/utils/adt/uuid.c
+++ b/src/backend/utils/adt/uuid.c
@@ -13,13 +13,18 @@
#include "postgres.h"
+#include <sys/time.h>
+
+#include "access/xlog.h"
#include "common/hashfn.h"
#include "lib/hyperloglog.h"
#include "libpq/pqformat.h"
#include "port/pg_bswap.h"
-#include "utils/fmgrprotos.h"
+#include "utils/builtins.h"
+#include "utils/datetime.h"
#include "utils/guc.h"
#include "utils/sortsupport.h"
+#include "utils/timestamp.h"
#include "utils/uuid.h"
/* sortsupport for uuid */
@@ -406,6 +411,11 @@ uuid_hash_extended(PG_FUNCTION_ARGS)
return hash_any_extended(key->data, UUID_LEN, PG_GETARG_INT64(1));
}
+/*
+ * Routine to generate UUID version 4.
+ * All UUID bytes are filled with strong random numbers except version and
+ * variant 0b10 bits.
+ */
Datum
gen_random_uuid(PG_FUNCTION_ARGS)
{
@@ -425,3 +435,245 @@ gen_random_uuid(PG_FUNCTION_ARGS)
PG_RETURN_UUID_P(uuid);
}
+
+static uint32_t sequence_counter;
+static uint64_t previous_timestamp = 0;
+
+/*
+ * Routine to generate UUID version 7.
+ * Following description is taken from RFC draft and slightly extended to
+ * reflect implementation specific choices.
+ *
+ * UUIDv7 Field and Bit Layout:
+ * ----------
+ * 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 | rand_a |
+ * +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
+ * |var| rand_b |
+ * +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
+ * | rand_b |
+ * +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
+ *
+ * unix_ts_ms:
+ * 48 bit big-endian unsigned number of Unix epoch timestamp in milliseconds
+ * as per Section 6.1. Occupies bits 0 through 47 (octets 0-5).
+ *
+ * ver:
+ * The 4 bit version field as defined by Section 4.2, set to 0b0111 (7).
+ * Occupies bits 48 through 51 of octet 6.
+ *
+ * rand_a:
+ * Most significant 12 bits of 18-bit counter. This counter is designed to
+ * guarantee additional monotonicity as per Section 6.2 (Method 1). rand_a
+ * occupies bits 52 through 63 (octets 6-7).
+ *
+ * var:
+ * The 2 bit variant field as defined by Section 4.1, set to 0b10. Occupies
+ * bits 64 and 65 of octet 8.
+ *
+ * rand_b:
+ * Starting 6 bits are least significant 6 bits of a counter. The final 56
+ * bits filled with pseudo-random data to provide uniqueness as per
+ * Section 6.9. rand_b Occupies bits 66 through 127 (octets 8-15).
+ * ----------
+ *
+ * "Fixed-Length Dedicated Counter Bits" (Method 1) can be implemented with
+ * arbitrary size of a counter. We choose size 18 to reuse all space of bytes
+ * that are touched by ver and var fields + rand_a bytes between them.
+ * Whenever timestamp unix_ts_ms is moving forward, this counter bits are
+ * reinitialized. Reinilialization always sets most significant bit to 0, other
+ * bits are initialized with random numbers. This gives as approximately 192K
+ * UUIDs within one millisecond without overflow. This ougth to be enough for
+ * most practical purposes. Whenever counter overflow happens, this overflow is
+ * translated to increment of unix_ts_ms. So generation of UUIDs ate rate
+ * higher than 128MHz might lead to using timestamps ahead of time.
+ *
+ * We're not using the "Replace Left-Most Random Bits with Increased Clock
+ * Precision" method Section 6.2 (Method 3), because of portability concerns.
+ * It's unclear if all supported platforms can provide reliable microsocond
+ * precision time.
+ *
+ * All UUID generator state is backend-local. For UUIDs generated in one
+ * backend we guarantee monotonicity. UUIDs generated on different backends
+ * will be mostly monotonic if they are generated at frequences less than 1KHz,
+ * but this monotonicity is not strictly guaranteed. UUIDs generated on
+ * different nodes are mostly monotonic with regards to possible clock drift.
+ * Uniqueness of UUIDs generated at the same timestamp across different
+ * backends and/or nodes is guaranteed by using random bits.
+ */
+Datum
+uuidv7(PG_FUNCTION_ARGS)
+{
+ pg_uuid_t *uuid = palloc(UUID_LEN);
+ uint64_t tms;
+ struct timeval tp;
+ bool increment_counter;
+
+ gettimeofday(&tp, NULL);
+ tms = ((uint64_t) tp.tv_sec) * 1000 + (tp.tv_usec) / 1000;
+ /* time from clock is protected from backward leaps */
+ increment_counter = (tms <= previous_timestamp);
+
+ if (increment_counter)
+ {
+ /*
+ * Time did not advance from the previous generation, we must
+ * increment counter
+ */
+ ++sequence_counter;
+ if (sequence_counter > 0x3ffff)
+ {
+ /* We only have 18-bit counter */
+ sequence_counter = 0;
+ previous_timestamp++;
+ }
+
+ /* protection from leap backward */
+ tms = previous_timestamp;
+
+ /* fill everything after the timestamp and counter with random bytes */
+ if (!pg_strong_random(&uuid->data[8], UUID_LEN - 8))
+ ereport(ERROR,
+ (errcode(ERRCODE_INTERNAL_ERROR),
+ errmsg("could not generate random values")));
+
+ /* most significant 4 bits of 18-bit counter */
+ uuid->data[6] = (unsigned char) (sequence_counter >> 14);
+ /* next 8 bits */
+ uuid->data[7] = (unsigned char) (sequence_counter >> 6);
+ /* least significant 6 bits */
+ uuid->data[8] = (unsigned char) (sequence_counter);
+ }
+ else
+ {
+ /* fill everything after the timestamp with random bytes */
+ if (!pg_strong_random(&uuid->data[6], UUID_LEN - 6))
+ ereport(ERROR,
+ (errcode(ERRCODE_INTERNAL_ERROR),
+ errmsg("could not generate random values")));
+
+ /*
+ * Left-most counter bits are initialized as zero for the sole purpose
+ * of guarding against counter rollovers. See section "Fixed-Length
+ * Dedicated Counter Seeding"
+ * https://datatracker.ietf.org/doc/html/draft-ietf-uuidrev-rfc4122bis#monotonicity_counters
+ */
+ uuid->data[6] = (uuid->data[6] & 0xf7);
+
+ /* read randomly initialized bits of counter */
+ sequence_counter = ((uint32_t) uuid->data[8] & 0x3f) +
+ (((uint32_t) uuid->data[7]) << 6) +
+ (((uint32_t) uuid->data[6] & 0x0f) << 14);
+
+ previous_timestamp = tms;
+ }
+
+ /* Fill in time part */
+ uuid->data[0] = (unsigned char) (tms >> 40);
+ uuid->data[1] = (unsigned char) (tms >> 32);
+ uuid->data[2] = (unsigned char) (tms >> 24);
+ uuid->data[3] = (unsigned char) (tms >> 16);
+ uuid->data[4] = (unsigned char) (tms >> 8);
+ uuid->data[5] = (unsigned char) tms;
+
+ /*
+ * Set magic numbers for a "version 7" (pseudorandom) UUID, see
+ * https://datatracker.ietf.org/doc/html/draft-ietf-uuidrev-rfc4122bis
+ */
+ /* set version field, top four bits are 0, 1, 1, 1 */
+ uuid->data[6] = (uuid->data[6] & 0x0f) | 0x70;
+ /* set variant field, top two bits are 1, 0 */
+ uuid->data[8] = (uuid->data[8] & 0x3f) | 0x80;
+
+ PG_RETURN_UUID_P(uuid);
+}
+
+/*
+ * Routine to extract UUID version from variant 0b10
+ * Returns NULL if UUID is not 0b10 or version is not 1,6 or7.
+ */
+Datum
+uuid_extract_timestamp(PG_FUNCTION_ARGS)
+{
+ pg_uuid_t *uuid = PG_GETARG_UUID_P(0);
+ TimestampTz ts;
+ uint64_t tms;
+
+ if ((uuid->data[8] & 0xc0) != 0x80)
+ PG_RETURN_NULL();
+
+ if ((uuid->data[6] & 0xf0) == 0x70)
+ {
+ tms = uuid->data[5];
+ tms += ((uint64_t) uuid->data[4]) << 8;
+ tms += ((uint64_t) uuid->data[3]) << 16;
+ tms += ((uint64_t) uuid->data[2]) << 24;
+ tms += ((uint64_t) uuid->data[1]) << 32;
+ tms += ((uint64_t) uuid->data[0]) << 40;
+
+ /* convert ms to us, then adjust */
+ ts = (TimestampTz) (tms * 1000) -
+ (POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY * USECS_PER_SEC;
+
+ PG_RETURN_TIMESTAMPTZ(ts);
+ }
+
+ if ((uuid->data[6] & 0xf0) == 0x10)
+ {
+ tms = ((uint64_t) uuid->data[0]) << 24;
+ tms += ((uint64_t) uuid->data[1]) << 16;
+ tms += ((uint64_t) uuid->data[2]) << 8;
+ tms += ((uint64_t) uuid->data[3]);
+ tms += ((uint64_t) uuid->data[4]) << 40;
+ tms += ((uint64_t) uuid->data[5]) << 32;
+ tms += (((uint64_t) uuid->data[6]) & 0xf) << 56;
+ tms += ((uint64_t) uuid->data[7]) << 48;
+
+ /* convert 100-ns intervals to us, then adjust */
+ ts = (TimestampTz) (tms / 10) -
+ ((uint64_t) POSTGRES_EPOCH_JDATE - GREGORIAN_EPOCH_JDATE) * SECS_PER_DAY * USECS_PER_SEC;
+
+ PG_RETURN_TIMESTAMPTZ(ts);
+ }
+
+ if ((uuid->data[6] & 0xf0) == 0x60)
+ {
+ tms = ((uint64_t) uuid->data[0]) << 52;
+ tms += ((uint64_t) uuid->data[1]) << 44;
+ tms += ((uint64_t) uuid->data[2]) << 36;
+ tms += ((uint64_t) uuid->data[3]) << 28;
+ tms += ((uint64_t) uuid->data[4]) << 20;
+ tms += ((uint64_t) uuid->data[5]) << 12;
+ tms += (((uint64_t) uuid->data[6]) & 0xf) << 8;
+ tms += ((uint64_t) uuid->data[7]);
+
+ /* convert 100-ns intervals to us, then adjust */
+ ts = (TimestampTz) (tms / 10) -
+ ((uint64_t) POSTGRES_EPOCH_JDATE - GREGORIAN_EPOCH_JDATE) * SECS_PER_DAY * USECS_PER_SEC;
+
+ PG_RETURN_TIMESTAMPTZ(ts);
+ }
+
+ PG_RETURN_NULL();
+}
+
+/*
+ * Routine to extract UUID version from variant 0b10
+ * Returns NULL if UUID is not 0b10
+ */
+Datum
+uuid_extract_version(PG_FUNCTION_ARGS)
+{
+ pg_uuid_t *uuid = PG_GETARG_UUID_P(0);
+ uint16_t result;
+
+ if ((uuid->data[8] & 0xc0) != 0x80)
+ PG_RETURN_NULL();
+ result = uuid->data[6] >> 4;
+
+ PG_RETURN_UINT16(result);
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 700f7daf7b..ddac2f51f6 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -9170,6 +9170,18 @@
{ oid => '3432', descr => 'generate random UUID',
proname => 'gen_random_uuid', proleakproof => 't', provolatile => 'v',
prorettype => 'uuid', proargtypes => '', prosrc => 'gen_random_uuid' },
+{ oid => '9895', descr => 'generate UUID version 4',
+ proname => 'uuidv4', proleakproof => 't', provolatile => 'v',
+ prorettype => 'uuid', proargtypes => '', prosrc => 'gen_random_uuid' },
+{ oid => '9896', descr => 'generate UUID version 7',
+ proname => 'uuidv7', proleakproof => 't', provolatile => 'v',
+ prorettype => 'uuid', proargtypes => '', prosrc => 'uuidv7' },
+{ oid => '9897', descr => 'extract timestamp from UUID version 1, 6 or 7',
+ proname => 'uuid_extract_timestamp', proleakproof => 't',
+ prorettype => 'timestamptz', proargtypes => 'uuid', prosrc => 'uuid_extract_timestamp' },
+{ oid => '9898', descr => 'extract version from RFC 4122 UUID',
+ proname => 'uuid_extract_version', proleakproof => 't',
+ prorettype => 'int2', proargtypes => 'uuid', prosrc => 'uuid_extract_version' },
# pg_lsn
{ oid => '3229', descr => 'I/O',
diff --git a/src/include/datatype/timestamp.h b/src/include/datatype/timestamp.h
index 3a37cb661e..652aeb428e 100644
--- a/src/include/datatype/timestamp.h
+++ b/src/include/datatype/timestamp.h
@@ -230,9 +230,10 @@ struct pg_itm_in
((y) < JULIAN_MAXYEAR || \
((y) == JULIAN_MAXYEAR && ((m) < JULIAN_MAXMONTH))))
-/* Julian-date equivalents of Day 0 in Unix and Postgres reckoning */
+/* Julian-date equivalents of Day 0 in Unix, Postgres and Gregorian epochs */
#define UNIX_EPOCH_JDATE 2440588 /* == date2j(1970, 1, 1) */
#define POSTGRES_EPOCH_JDATE 2451545 /* == date2j(2000, 1, 1) */
+#define GREGORIAN_EPOCH_JDATE 2299161 /* == date2j(1582,10,15) */
/*
* Range limits for dates and timestamps.
diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index 7610b011d6..2b5d67b7f0 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -872,6 +872,10 @@ xid8ge(xid8,xid8)
xid8eq(xid8,xid8)
xid8ne(xid8,xid8)
xid8cmp(xid8,xid8)
+uuidv4()
+uuidv7()
+uuid_extract_timestamp(uuid)
+uuid_extract_version(uuid)
-- restore normal output mode
\a\t
-- List of functions used by libpq's fe-lobj.c
diff --git a/src/test/regress/expected/uuid.out b/src/test/regress/expected/uuid.out
index 8e7f21910d..15dab4e1c2 100644
--- a/src/test/regress/expected/uuid.out
+++ b/src/test/regress/expected/uuid.out
@@ -168,5 +168,70 @@ SELECT count(DISTINCT guid_field) FROM guid1;
2
(1 row)
+-- test of uuidv4() alias
+TRUNCATE guid1;
+INSERT INTO guid1 (guid_field) VALUES (uuidv4());
+INSERT INTO guid1 (guid_field) VALUES (uuidv4());
+SELECT count(DISTINCT guid_field) FROM guid1;
+ count
+-------
+ 2
+(1 row)
+
+-- generation test for v7
+TRUNCATE guid1;
+INSERT INTO guid1 (guid_field) VALUES (uuidv7());
+INSERT INTO guid1 (guid_field) VALUES (uuidv7());
+SELECT count(DISTINCT guid_field) FROM guid1;
+ count
+-------
+ 2
+(1 row)
+
+-- support functions for UUID versions and variants
+SELECT uuid_extract_version(uuidv7());
+ uuid_extract_version
+----------------------
+ 7
+(1 row)
+
+SELECT uuid_extract_version('11111111-1111-1111-1111-111111111111') IS NULL;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT uuid_extract_version('11111111-1111-5111-8111-111111111111');
+ uuid_extract_version
+----------------------
+ 5
+(1 row)
+
+-- uuid_extract_timestamp() must refuse to accept non-UUIDv7
+SELECT uuid_extract_timestamp(gen_random_uuid());
+ uuid_extract_timestamp
+------------------------
+
+(1 row)
+
+-- extract UUID v1, v6 and v7 timestamp
+SELECT uuid_extract_timestamp('C232AB00-9414-11EC-B3C8-9F6BDECED846') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT uuid_extract_timestamp('1EC9414C-232A-6B00-B3C8-9F6BDECED846') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT uuid_extract_timestamp('017F22E2-79B0-7CC3-98C4-DC0C0C07398F') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00';
+ ?column?
+----------
+ t
+(1 row)
+
-- clean up
DROP TABLE guid1, guid2 CASCADE;
diff --git a/src/test/regress/sql/uuid.sql b/src/test/regress/sql/uuid.sql
index 9a8f437c7d..0d1b041b6c 100644
--- a/src/test/regress/sql/uuid.sql
+++ b/src/test/regress/sql/uuid.sql
@@ -85,5 +85,30 @@ INSERT INTO guid1 (guid_field) VALUES (gen_random_uuid());
INSERT INTO guid1 (guid_field) VALUES (gen_random_uuid());
SELECT count(DISTINCT guid_field) FROM guid1;
+-- test of uuidv4() alias
+TRUNCATE guid1;
+INSERT INTO guid1 (guid_field) VALUES (uuidv4());
+INSERT INTO guid1 (guid_field) VALUES (uuidv4());
+SELECT count(DISTINCT guid_field) FROM guid1;
+
+-- generation test for v7
+TRUNCATE guid1;
+INSERT INTO guid1 (guid_field) VALUES (uuidv7());
+INSERT INTO guid1 (guid_field) VALUES (uuidv7());
+SELECT count(DISTINCT guid_field) FROM guid1;
+
+-- support functions for UUID versions and variants
+SELECT uuid_extract_version(uuidv7());
+SELECT uuid_extract_version('11111111-1111-1111-1111-111111111111') IS NULL;
+SELECT uuid_extract_version('11111111-1111-5111-8111-111111111111');
+
+-- uuid_extract_timestamp() must refuse to accept non-UUIDv7
+SELECT uuid_extract_timestamp(gen_random_uuid());
+
+-- extract UUID v1, v6 and v7 timestamp
+SELECT uuid_extract_timestamp('C232AB00-9414-11EC-B3C8-9F6BDECED846') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00';
+SELECT uuid_extract_timestamp('1EC9414C-232A-6B00-B3C8-9F6BDECED846') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00';
+SELECT uuid_extract_timestamp('017F22E2-79B0-7CC3-98C4-DC0C0C07398F') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00';
+
-- clean up
DROP TABLE guid1, guid2 CASCADE;
--
2.37.1 (Apple Git-137.1)
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: UUID v7
@ 2024-03-19 08:55 Peter Eisentraut <[email protected]>
parent: Andrey M. Borodin <[email protected]>
0 siblings, 1 reply; 19+ messages in thread
From: Peter Eisentraut @ 2024-03-19 08:55 UTC (permalink / raw)
To: Andrey M. Borodin <[email protected]>; Aleksander Alekseev <[email protected]>; +Cc: pgsql-hackers; Sergey Prokhorenko <[email protected]>; Jelte Fennema-Nio <[email protected]>; Przemysław Sztoch <[email protected]>; David G. Johnston <[email protected]>; Mat Arye <[email protected]>; Matthias van de Meent <[email protected]>; Nikolay Samokhvalov <[email protected]>; Junwang Zhao <[email protected]>
On 16.03.24 18:43, Andrey M. Borodin wrote:
>> On 15 Mar 2024, at 14:47, Aleksander Alekseev <[email protected]> wrote:
>>
>> +1 to the idea. I doubt that anyone will miss it.
>
> PFA v22.
>
> Changes:
> 1. Squashed all editorialisation by Jelte
> 2. Fixed my erroneous comments on using Method 2 (we are using method 1 instead)
> 3. Remove all traces of uuid_extract_variant()
I have committed a subset of this for now, namely the additions of
uuid_extract_timestamp() and uuid_extract_version(). These seemed
mature and agreed upon. You can rebase the rest of your patch on top of
that.
I have started a separate discussion to learn about the precision we can
expect from gettimeofday().
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: UUID v7
@ 2024-03-20 18:08 Andrey M. Borodin <[email protected]>
parent: Peter Eisentraut <[email protected]>
0 siblings, 2 replies; 19+ messages in thread
From: Andrey M. Borodin @ 2024-03-20 18:08 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; +Cc: Aleksander Alekseev <[email protected]>; pgsql-hackers; Sergey Prokhorenko <[email protected]>; Jelte Fennema-Nio <[email protected]>; Przemysław Sztoch <[email protected]>; David G. Johnston <[email protected]>; Mat Arye <[email protected]>; Matthias van de Meent <[email protected]>; Nikolay Samokhvalov <[email protected]>; Junwang Zhao <[email protected]>
> On 19 Mar 2024, at 13:55, Peter Eisentraut <[email protected]> wrote:
>
> On 16.03.24 18:43, Andrey M. Borodin wrote:
>>> On 15 Mar 2024, at 14:47, Aleksander Alekseev <[email protected]> wrote:
>>>
>>> +1 to the idea. I doubt that anyone will miss it.
>> PFA v22.
>> Changes:
>> 1. Squashed all editorialisation by Jelte
>> 2. Fixed my erroneous comments on using Method 2 (we are using method 1 instead)
>> 3. Remove all traces of uuid_extract_variant()
>
> I have committed a subset of this for now, namely the additions of uuid_extract_timestamp() and uuid_extract_version(). These seemed mature and agreed upon. You can rebase the rest of your patch on top of that.
Great! Thank you! PFA v23 with rebase on HEAD.
> I have started a separate discussion to learn about the precision we can expect from gettimeofday().
Even in presence of real microsecond-enabled and portable timer using microseconds does not seem to me an optimal way of utilising UUID bits.
Timer-based bits contribute to global sortability. But the real timers we have are not even millisecond adjusted. We can hope for ~few ms variation in one datacenter or in presence of atomic clocks.
Time-based bits contribute to global uniqueness, but certainly they are not that effective as counter bits.
Time-based bits do not provide local sortability guarantees: some UUIDs might get same microseconds or be affected by leap backwards.
I think that microseconds are good only for hardware-specific solutions, not for something that runs on variety of platforms, OSes, devices.
Best regards, Andrey Borodin.
Attachments:
[application/octet-stream] v23-0001-Implement-UUID-v7.patch (17.4K, ../../[email protected]/2-v23-0001-Implement-UUID-v7.patch)
download | inline diff:
From b69c860c9fb793d56493053042e5beceaf069123 Mon Sep 17 00:00:00 2001
From: "Andrey M. Borodin" <[email protected]>
Date: Wed, 20 Mar 2024 22:30:14 +0500
Subject: [PATCH v23] Implement UUID v7
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
This commit adds function for UUID generation. Most important function here
is uuidv7() which generates new UUID according to the new standard.
For code readability this commit adds alias uuidv4() to function
gen_random_uuid().
Author: Andrey Borodin
Reviewed-by: Sergey Prokhorenko, Kirk Wolak, Przemysław Sztoch
Reviewed-by: Nikolay Samokhvalov, Jelte Fennema-Nio, Aleksander Alekseev
Reviewed-by: Peter Eisentraut, Chris Travers, Lukas Fittl
Discussion: https://postgr.es/m/CAAhFRxitJv%3DyoGnXUgeLB_O%2BM7J2BJAmb5jqAT9gZ3bij3uLDA%40mail.gmail.com
---
doc/src/sgml/func.sgml | 38 ++++-
src/backend/utils/adt/uuid.c | 206 ++++++++++++++++++++++-
src/include/catalog/pg_proc.dat | 6 +
src/test/regress/expected/opr_sanity.out | 2 +
src/test/regress/expected/uuid.out | 46 ++++-
src/test/regress/sql/uuid.sql | 18 +-
6 files changed, 308 insertions(+), 8 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 5b225ccf4f..acb655cfeb 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14127,6 +14127,14 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
<primary>gen_random_uuid</primary>
</indexterm>
+ <indexterm>
+ <primary>uuidv4</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>uuidv7</primary>
+ </indexterm>
+
<indexterm>
<primary>uuid_extract_timestamp</primary>
</indexterm>
@@ -14136,12 +14144,36 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
</indexterm>
<para>
- <productname>PostgreSQL</productname> includes one function to generate a UUID:
+ <productname>PostgreSQL</productname> includes several functions to generate a UUID.
<synopsis>
<function>gen_random_uuid</function> () <returnvalue>uuid</returnvalue>
+<function>uuidv4</function> () <returnvalue>uuid</returnvalue>
+</synopsis>
+ These functions return a version 4 (random) UUID.
+<synopsis>
+<function>uuidv7</function> () <returnvalue>uuid</returnvalue>
+</synopsis>
+ This function returns a version 7 UUID (UNIX timestamp with 1ms precision +
+ randomly seeded counter + random).
+ </para>
+
+ <para>
+<synopsis>
+<function>uuid_extract_timestamp</function> (uuid) <returnvalue>timestamptz</returnvalue>
+</synopsis>
+ This function extracts a <type>timestamp with time zone</type> from UUID versions 1, 6 and 7.
+ For other versions and variants this function returns NULL. The extracted timestamp
+ does not necessarily equate to the time of UUID generation. How close it is
+ to the actual time depends on the implementation that generated UUID.
+ The uuidv7() function provided by PostgreSQL will normally store the actual time,
+ with some exceptions: prevention of time leaps backwards and counter overflow
+ being carried to time step.
+<synopsis>
+<function>uuid_extract_version</function> (uuid) <returnvalue>smallint</returnvalue>
</synopsis>
- This function returns a version 4 (random) UUID. This is the most commonly
- used type of UUID and is appropriate for most applications.
+ This function extracts a version bits from UUID of variant described by
+ <ulink url="https://datatracker.ietf.org/doc/html/draft-ietf-uuidrev-rfc4122bis">IETF standard draft</ulink>
+ (b10xx variant). For other variants this function returns NULL.
</para>
<para>
diff --git a/src/backend/utils/adt/uuid.c b/src/backend/utils/adt/uuid.c
index 45eb1b2fea..dfa0746fc7 100644
--- a/src/backend/utils/adt/uuid.c
+++ b/src/backend/utils/adt/uuid.c
@@ -13,11 +13,15 @@
#include "postgres.h"
+#include <sys/time.h>
+
+#include "access/xlog.h"
#include "common/hashfn.h"
#include "lib/hyperloglog.h"
#include "libpq/pqformat.h"
#include "port/pg_bswap.h"
-#include "utils/fmgrprotos.h"
+#include "utils/builtins.h"
+#include "utils/datetime.h"
#include "utils/guc.h"
#include "utils/sortsupport.h"
#include "utils/timestamp.h"
@@ -407,6 +411,12 @@ uuid_hash_extended(PG_FUNCTION_ARGS)
return hash_any_extended(key->data, UUID_LEN, PG_GETARG_INT64(1));
}
+/*
+ * Generate UUID version 4.
+ *
+ * All UUID bytes are filled with strong random numbers except version and
+ * variant 0b10 bits.
+ */
Datum
gen_random_uuid(PG_FUNCTION_ARGS)
{
@@ -427,7 +437,164 @@ gen_random_uuid(PG_FUNCTION_ARGS)
PG_RETURN_UUID_P(uuid);
}
-#define UUIDV1_EPOCH_JDATE 2299161 /* == date2j(1582,10,15) */
+static uint32_t sequence_counter;
+static uint64_t previous_timestamp = 0;
+
+/*
+ * Generate UUID version 7.
+ *
+ * Following description is taken from RFC draft and slightly extended to
+ * reflect implementation specific choices.
+ *
+ * UUIDv7 Field and Bit Layout:
+ * ----------
+ * 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 | rand_a |
+ * +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
+ * |var| rand_b |
+ * +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
+ * | rand_b |
+ * +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
+ *
+ * unix_ts_ms:
+ * 48 bit big-endian unsigned number of Unix epoch timestamp in milliseconds
+ * as per Section 6.1. Occupies bits 0 through 47 (octets 0-5).
+ *
+ * ver:
+ * The 4 bit version field as defined by Section 4.2, set to 0b0111 (7).
+ * Occupies bits 48 through 51 of octet 6.
+ *
+ * rand_a:
+ * Most significant 12 bits of 18-bit counter. This counter is designed to
+ * guarantee additional monotonicity as per Section 6.2 (Method 1). rand_a
+ * occupies bits 52 through 63 (octets 6-7).
+ *
+ * var:
+ * The 2 bit variant field as defined by Section 4.1, set to 0b10. Occupies
+ * bits 64 and 65 of octet 8.
+ *
+ * rand_b:
+ * Starting 6 bits are least significant 6 bits of a counter. The final 56
+ * bits filled with pseudo-random data to provide uniqueness as per
+ * Section 6.9. rand_b Occupies bits 66 through 127 (octets 8-15).
+ * ----------
+ *
+ * "Fixed-Length Dedicated Counter Bits" (Method 1) can be implemented with
+ * arbitrary size of a counter. We choose size 18 to reuse all space of bytes
+ * that are touched by ver and var fields + rand_a bytes between them.
+ * Whenever timestamp unix_ts_ms is moving forward, this counter bits are
+ * reinitialized. Reinilialization always sets most significant bit to 0, other
+ * bits are initialized with random numbers. This gives as approximately 192K
+ * UUIDs within one millisecond without overflow. This ougth to be enough for
+ * most practical purposes. Whenever counter overflow happens, this overflow is
+ * translated to increment of unix_ts_ms. So generation of UUIDs ate rate
+ * higher than 128MHz might lead to using timestamps ahead of time.
+ *
+ * We're not using the "Replace Left-Most Random Bits with Increased Clock
+ * Precision" method Section 6.2 (Method 3), because of portability concerns.
+ * It's unclear if all supported platforms can provide reliable microsocond
+ * precision time.
+ *
+ * All UUID generator state is backend-local. For UUIDs generated in one
+ * backend we guarantee monotonicity. UUIDs generated on different backends
+ * will be mostly monotonic if they are generated at frequences less than 1KHz,
+ * but this monotonicity is not strictly guaranteed. UUIDs generated on
+ * different nodes are mostly monotonic with regards to possible clock drift.
+ * Uniqueness of UUIDs generated at the same timestamp across different
+ * backends and/or nodes is guaranteed by using random bits.
+ */
+Datum
+uuidv7(PG_FUNCTION_ARGS)
+{
+ pg_uuid_t *uuid = palloc(UUID_LEN);
+ uint64_t tms;
+ struct timeval tp;
+ bool increment_counter;
+
+ gettimeofday(&tp, NULL);
+ tms = ((uint64_t) tp.tv_sec) * 1000 + (tp.tv_usec) / 1000;
+ /* time from clock is protected from backward leaps */
+ increment_counter = (tms <= previous_timestamp);
+
+ if (increment_counter)
+ {
+ /*
+ * Time did not advance from the previous generation, we must
+ * increment counter
+ */
+ ++sequence_counter;
+ if (sequence_counter > 0x3ffff)
+ {
+ /* We only have 18-bit counter */
+ sequence_counter = 0;
+ previous_timestamp++;
+ }
+
+ /* protection from leap backward */
+ tms = previous_timestamp;
+
+ /* fill everything after the timestamp and counter with random bytes */
+ if (!pg_strong_random(&uuid->data[8], UUID_LEN - 8))
+ ereport(ERROR,
+ (errcode(ERRCODE_INTERNAL_ERROR),
+ errmsg("could not generate random values")));
+
+ /* most significant 4 bits of 18-bit counter */
+ uuid->data[6] = (unsigned char) (sequence_counter >> 14);
+ /* next 8 bits */
+ uuid->data[7] = (unsigned char) (sequence_counter >> 6);
+ /* least significant 6 bits */
+ uuid->data[8] = (unsigned char) (sequence_counter);
+ }
+ else
+ {
+ /* fill everything after the timestamp with random bytes */
+ if (!pg_strong_random(&uuid->data[6], UUID_LEN - 6))
+ ereport(ERROR,
+ (errcode(ERRCODE_INTERNAL_ERROR),
+ errmsg("could not generate random values")));
+
+ /*
+ * Left-most counter bits are initialized as zero for the sole purpose
+ * of guarding against counter rollovers. See section "Fixed-Length
+ * Dedicated Counter Seeding"
+ * https://datatracker.ietf.org/doc/html/draft-ietf-uuidrev-rfc4122bis#monotonicity_counters
+ */
+ uuid->data[6] = (uuid->data[6] & 0xf7);
+
+ /* read randomly initialized bits of counter */
+ sequence_counter = ((uint32_t) uuid->data[8] & 0x3f) +
+ (((uint32_t) uuid->data[7]) << 6) +
+ (((uint32_t) uuid->data[6] & 0x0f) << 14);
+
+ previous_timestamp = tms;
+ }
+
+ /* Fill in time part */
+ uuid->data[0] = (unsigned char) (tms >> 40);
+ uuid->data[1] = (unsigned char) (tms >> 32);
+ uuid->data[2] = (unsigned char) (tms >> 24);
+ uuid->data[3] = (unsigned char) (tms >> 16);
+ uuid->data[4] = (unsigned char) (tms >> 8);
+ uuid->data[5] = (unsigned char) tms;
+
+ /*
+ * Set magic numbers for a "version 7" (pseudorandom) UUID, see
+ * https://datatracker.ietf.org/doc/html/draft-ietf-uuidrev-rfc4122bis
+ */
+ /* set version field, top four bits are 0, 1, 1, 1 */
+ uuid->data[6] = (uuid->data[6] & 0x0f) | 0x70;
+ /* set variant field, top two bits are 1, 0 */
+ uuid->data[8] = (uuid->data[8] & 0x3f) | 0x80;
+
+ PG_RETURN_UUID_P(uuid);
+}
+
+#define GREGORIAN_EPOCH_JDATE 2299161 /* == date2j(1582,10,15) */
/*
* Extract timestamp from UUID.
@@ -461,7 +628,40 @@ uuid_extract_timestamp(PG_FUNCTION_ARGS)
/* convert 100-ns intervals to us, then adjust */
ts = (TimestampTz) (tms / 10) -
- ((uint64) POSTGRES_EPOCH_JDATE - UUIDV1_EPOCH_JDATE) * SECS_PER_DAY * USECS_PER_SEC;
+ ((uint64) POSTGRES_EPOCH_JDATE - GREGORIAN_EPOCH_JDATE) * SECS_PER_DAY * USECS_PER_SEC;
+ PG_RETURN_TIMESTAMPTZ(ts);
+ }
+
+ if (version == 6)
+ {
+ tms = ((uint64_t) uuid->data[0]) << 52;
+ tms += ((uint64_t) uuid->data[1]) << 44;
+ tms += ((uint64_t) uuid->data[2]) << 36;
+ tms += ((uint64_t) uuid->data[3]) << 28;
+ tms += ((uint64_t) uuid->data[4]) << 20;
+ tms += ((uint64_t) uuid->data[5]) << 12;
+ tms += (((uint64_t) uuid->data[6]) & 0xf) << 8;
+ tms += ((uint64_t) uuid->data[7]);
+
+ /* convert 100-ns intervals to us, then adjust */
+ ts = (TimestampTz) (tms / 10) -
+ ((uint64_t) POSTGRES_EPOCH_JDATE - GREGORIAN_EPOCH_JDATE) * SECS_PER_DAY * USECS_PER_SEC;
+
+ PG_RETURN_TIMESTAMPTZ(ts);
+ }
+
+ if (version == 7)
+ {
+ tms = uuid->data[5];
+ tms += ((uint64_t) uuid->data[4]) << 8;
+ tms += ((uint64_t) uuid->data[3]) << 16;
+ tms += ((uint64_t) uuid->data[2]) << 24;
+ tms += ((uint64_t) uuid->data[1]) << 32;
+ tms += ((uint64_t) uuid->data[0]) << 40;
+
+ /* convert ms to us, then adjust */
+ ts = (TimestampTz) (tms * 1000) -
+ (POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY * USECS_PER_SEC;
PG_RETURN_TIMESTAMPTZ(ts);
}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 177d81a891..30dc2d82af 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -9170,6 +9170,12 @@
{ oid => '3432', descr => 'generate random UUID',
proname => 'gen_random_uuid', proleakproof => 't', provolatile => 'v',
prorettype => 'uuid', proargtypes => '', prosrc => 'gen_random_uuid' },
+{ oid => '9895', descr => 'generate UUID version 4',
+ proname => 'uuidv4', proleakproof => 't', provolatile => 'v',
+ prorettype => 'uuid', proargtypes => '', prosrc => 'gen_random_uuid' },
+{ oid => '9896', descr => 'generate UUID version 7',
+ proname => 'uuidv7', proleakproof => 't', provolatile => 'v',
+ prorettype => 'uuid', proargtypes => '', prosrc => 'uuidv7' },
{ oid => '9897', descr => 'extract timestamp from UUID',
proname => 'uuid_extract_timestamp', proleakproof => 't',
prorettype => 'timestamptz', proargtypes => 'uuid',
diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index 9d047b21b8..2b5d67b7f0 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -872,6 +872,8 @@ xid8ge(xid8,xid8)
xid8eq(xid8,xid8)
xid8ne(xid8,xid8)
xid8cmp(xid8,xid8)
+uuidv4()
+uuidv7()
uuid_extract_timestamp(uuid)
uuid_extract_version(uuid)
-- restore normal output mode
diff --git a/src/test/regress/expected/uuid.out b/src/test/regress/expected/uuid.out
index 6026e15ed3..4f010eed67 100644
--- a/src/test/regress/expected/uuid.out
+++ b/src/test/regress/expected/uuid.out
@@ -168,6 +168,26 @@ SELECT count(DISTINCT guid_field) FROM guid1;
2
(1 row)
+-- test of uuidv4() alias
+TRUNCATE guid1;
+INSERT INTO guid1 (guid_field) VALUES (uuidv4());
+INSERT INTO guid1 (guid_field) VALUES (uuidv4());
+SELECT count(DISTINCT guid_field) FROM guid1;
+ count
+-------
+ 2
+(1 row)
+
+-- generation test for v7
+TRUNCATE guid1;
+INSERT INTO guid1 (guid_field) VALUES (uuidv7());
+INSERT INTO guid1 (guid_field) VALUES (uuidv7());
+SELECT count(DISTINCT guid_field) FROM guid1;
+ count
+-------
+ 2
+(1 row)
+
-- extract functions
-- version
SELECT uuid_extract_version('11111111-1111-5111-8111-111111111111'); -- 5
@@ -188,8 +208,32 @@ SELECT uuid_extract_version('11111111-1111-1111-1111-111111111111'); -- null
(1 row)
+SELECT uuid_extract_version(uuidv4()); --4
+ uuid_extract_version
+----------------------
+ 4
+(1 row)
+
+SELECT uuid_extract_version(uuidv7()); --7
+ uuid_extract_version
+----------------------
+ 7
+(1 row)
+
-- timestamp
-SELECT uuid_extract_timestamp('C232AB00-9414-11EC-B3C8-9F6BDECED846') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00'; -- RFC 4122bis test vector
+SELECT uuid_extract_timestamp('C232AB00-9414-11EC-B3C8-9F6BDECED846') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00'; -- RFC 4122bis test vector for v1
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT uuid_extract_timestamp('1EC9414C-232A-6B00-B3C8-9F6BDECED846') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00'; -- RFC 4122bis test vector for v6
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT uuid_extract_timestamp('017F22E2-79B0-7CC3-98C4-DC0C0C07398F') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00'; -- RFC 4122bis test vector for v7
?column?
----------
t
diff --git a/src/test/regress/sql/uuid.sql b/src/test/regress/sql/uuid.sql
index c88f6d087a..6fa039800a 100644
--- a/src/test/regress/sql/uuid.sql
+++ b/src/test/regress/sql/uuid.sql
@@ -85,6 +85,18 @@ INSERT INTO guid1 (guid_field) VALUES (gen_random_uuid());
INSERT INTO guid1 (guid_field) VALUES (gen_random_uuid());
SELECT count(DISTINCT guid_field) FROM guid1;
+-- test of uuidv4() alias
+TRUNCATE guid1;
+INSERT INTO guid1 (guid_field) VALUES (uuidv4());
+INSERT INTO guid1 (guid_field) VALUES (uuidv4());
+SELECT count(DISTINCT guid_field) FROM guid1;
+
+-- generation test for v7
+TRUNCATE guid1;
+INSERT INTO guid1 (guid_field) VALUES (uuidv7());
+INSERT INTO guid1 (guid_field) VALUES (uuidv7());
+SELECT count(DISTINCT guid_field) FROM guid1;
+
-- extract functions
@@ -92,9 +104,13 @@ SELECT count(DISTINCT guid_field) FROM guid1;
SELECT uuid_extract_version('11111111-1111-5111-8111-111111111111'); -- 5
SELECT uuid_extract_version(gen_random_uuid()); -- 4
SELECT uuid_extract_version('11111111-1111-1111-1111-111111111111'); -- null
+SELECT uuid_extract_version(uuidv4()); --4
+SELECT uuid_extract_version(uuidv7()); --7
-- timestamp
-SELECT uuid_extract_timestamp('C232AB00-9414-11EC-B3C8-9F6BDECED846') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00'; -- RFC 4122bis test vector
+SELECT uuid_extract_timestamp('C232AB00-9414-11EC-B3C8-9F6BDECED846') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00'; -- RFC 4122bis test vector for v1
+SELECT uuid_extract_timestamp('1EC9414C-232A-6B00-B3C8-9F6BDECED846') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00'; -- RFC 4122bis test vector for v6
+SELECT uuid_extract_timestamp('017F22E2-79B0-7CC3-98C4-DC0C0C07398F') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00'; -- RFC 4122bis test vector for v7
SELECT uuid_extract_timestamp(gen_random_uuid()); -- null
SELECT uuid_extract_timestamp('11111111-1111-1111-1111-111111111111'); -- null
--
2.37.1 (Apple Git-137.1)
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: UUID v7
@ 2024-03-21 15:21 Jelte Fennema-Nio <[email protected]>
parent: Andrey M. Borodin <[email protected]>
1 sibling, 2 replies; 19+ messages in thread
From: Jelte Fennema-Nio @ 2024-03-21 15:21 UTC (permalink / raw)
To: Andrey M. Borodin <[email protected]>; +Cc: Peter Eisentraut <[email protected]>; Aleksander Alekseev <[email protected]>; pgsql-hackers; Sergey Prokhorenko <[email protected]>; Przemysław Sztoch <[email protected]>; David G. Johnston <[email protected]>; Mat Arye <[email protected]>; Matthias van de Meent <[email protected]>; Nikolay Samokhvalov <[email protected]>; Junwang Zhao <[email protected]>
On Wed, 20 Mar 2024 at 19:08, Andrey M. Borodin <[email protected]> wrote:
> Timer-based bits contribute to global sortability. But the real timers we have are not even millisecond adjusted. We can hope for ~few ms variation in one datacenter or in presence of atomic clocks.
I think the main benefit of using microseconds would not be
sortability between servers, but sortability between backends. With
the current counter approach between backends we only have sortability
at the millisecond level.
However, I don't really think it is incredibly important to get the
"perfect" approach to filling in rand_a/rand_b right now. As long as
we don't document what we do, we can choose to change the method
without breaking backwards compatibility. Because either approach
results in valid UUIDv7s.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: UUID v7
@ 2024-03-22 06:53 Andrey M. Borodin <[email protected]>
parent: Jelte Fennema-Nio <[email protected]>
1 sibling, 0 replies; 19+ messages in thread
From: Andrey M. Borodin @ 2024-03-22 06:53 UTC (permalink / raw)
To: Jelte Fennema-Nio <[email protected]>; +Cc: Peter Eisentraut <[email protected]>; Aleksander Alekseev <[email protected]>; pgsql-hackers; Sergey Prokhorenko <[email protected]>; Przemysław Sztoch <[email protected]>; David G. Johnston <[email protected]>; Mat Arye <[email protected]>; Matthias van de Meent <[email protected]>; Nikolay Samokhvalov <[email protected]>; Junwang Zhao <[email protected]>
> On 21 Mar 2024, at 20:21, Jelte Fennema-Nio <[email protected]> wrote:
>
> On Wed, 20 Mar 2024 at 19:08, Andrey M. Borodin <[email protected]> wrote:
>> Timer-based bits contribute to global sortability. But the real timers we have are not even millisecond adjusted. We can hope for ~few ms variation in one datacenter or in presence of atomic clocks.
>
> I think the main benefit of using microseconds would not be
> sortability between servers, but sortability between backends.
Oh, that’s an interesting practical feature!
Se, essentially counter is a theoretical guaranty of sortability in one backend, while microseconds are practical sortability between backends.
> However, I don't really think it is incredibly important to get the
> "perfect" approach to filling in rand_a/rand_b right now. As long as
> we don't document what we do, we can choose to change the method
> without breaking backwards compatibility. Because either approach
> results in valid UUIDv7s.
Makes sense to me. I think both methods would be much better than UUIDv4 for practical reasons. And even not using extra bits at all (fill them with random numbers) would work for 0.999 cases.
Best regards, Andrey Borodin.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: UUID v7
@ 2024-03-22 12:51 Peter Eisentraut <[email protected]>
parent: Jelte Fennema-Nio <[email protected]>
1 sibling, 1 reply; 19+ messages in thread
From: Peter Eisentraut @ 2024-03-22 12:51 UTC (permalink / raw)
To: Jelte Fennema-Nio <[email protected]>; Andrey M. Borodin <[email protected]>; +Cc: Aleksander Alekseev <[email protected]>; pgsql-hackers; Sergey Prokhorenko <[email protected]>; Przemysław Sztoch <[email protected]>; David G. Johnston <[email protected]>; Mat Arye <[email protected]>; Matthias van de Meent <[email protected]>; Nikolay Samokhvalov <[email protected]>; Junwang Zhao <[email protected]>
On 21.03.24 16:21, Jelte Fennema-Nio wrote:
> On Wed, 20 Mar 2024 at 19:08, Andrey M. Borodin <[email protected]> wrote:
>> Timer-based bits contribute to global sortability. But the real timers we have are not even millisecond adjusted. We can hope for ~few ms variation in one datacenter or in presence of atomic clocks.
>
> I think the main benefit of using microseconds would not be
> sortability between servers, but sortability between backends.
There is that, and there are also multiple backend workers for one session.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: UUID v7
@ 2024-03-22 13:42 Sergey Prokhorenko <[email protected]>
parent: Peter Eisentraut <[email protected]>
0 siblings, 1 reply; 19+ messages in thread
From: Sergey Prokhorenko @ 2024-03-22 13:42 UTC (permalink / raw)
To: Jelte Fennema-Nio <[email protected]>; Andrey M. Borodin <[email protected]>; Peter Eisentraut <[email protected]>; +Cc: Aleksander Alekseev <[email protected]>; pgsql-hackers; Przemysław Sztoch <[email protected]>; David G. Johnston <[email protected]>; Mat Arye <[email protected]>; Matthias van de Meent <[email protected]>; Nikolay Samokhvalov <[email protected]>; Junwang Zhao <[email protected]>
Why not use a single UUID generator for the database table in this case, similar to autoincrement?
Sergey Prokhorenko
[email protected]
On Friday, 22 March 2024 at 03:51:20 pm GMT+3, Peter Eisentraut <[email protected]> wrote:
On 21.03.24 16:21, Jelte Fennema-Nio wrote:
> On Wed, 20 Mar 2024 at 19:08, Andrey M. Borodin <[email protected]> wrote:
>> Timer-based bits contribute to global sortability. But the real timers we have are not even millisecond adjusted. We can hope for ~few ms variation in one datacenter or in presence of atomic clocks.
>
> I think the main benefit of using microseconds would not be
> sortability between servers, but sortability between backends.
There is that, and there are also multiple backend workers for one session.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: UUID v7
@ 2024-03-22 13:58 Sergey Prokhorenko <[email protected]>
parent: Sergey Prokhorenko <[email protected]>
0 siblings, 1 reply; 19+ messages in thread
From: Sergey Prokhorenko @ 2024-03-22 13:58 UTC (permalink / raw)
To: Jelte Fennema-Nio <[email protected]>; Andrey M. Borodin <[email protected]>; Peter Eisentraut <[email protected]>; +Cc: Aleksander Alekseev <[email protected]>; pgsql-hackers; Przemysław Sztoch <[email protected]>; David G. Johnston <[email protected]>; Mat Arye <[email protected]>; Matthias van de Meent <[email protected]>; Nikolay Samokhvalov <[email protected]>; Junwang Zhao <[email protected]>
BTW: Each microservice should have its own database to ensure data isolation and independence, enabling better scalability and fault tolerance
Source: Microservices Pattern: Shared database
|
|
| |
Microservices Pattern: Shared database
|
|
|
Sergey Prokhorenko [email protected]
On Friday, 22 March 2024 at 04:42:20 pm GMT+3, Sergey Prokhorenko <[email protected]> wrote:
Why not use a single UUID generator for the database table in this case, similar to autoincrement?
Sergey Prokhorenko
[email protected]
On Friday, 22 March 2024 at 03:51:20 pm GMT+3, Peter Eisentraut <[email protected]> wrote:
On 21.03.24 16:21, Jelte Fennema-Nio wrote:
> On Wed, 20 Mar 2024 at 19:08, Andrey M. Borodin <[email protected]> wrote:
>> Timer-based bits contribute to global sortability. But the real timers we have are not even millisecond adjusted. We can hope for ~few ms variation in one datacenter or in presence of atomic clocks.
>
> I think the main benefit of using microseconds would not be
> sortability between servers, but sortability between backends.
There is that, and there are also multiple backend workers for one session.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: UUID v7
@ 2024-03-22 14:06 Sergey Prokhorenko <[email protected]>
parent: Sergey Prokhorenko <[email protected]>
0 siblings, 0 replies; 19+ messages in thread
From: Sergey Prokhorenko @ 2024-03-22 14:06 UTC (permalink / raw)
To: Jelte Fennema-Nio <[email protected]>; Andrey M. Borodin <[email protected]>; Peter Eisentraut <[email protected]>; +Cc: Aleksander Alekseev <[email protected]>; pgsql-hackers; Przemysław Sztoch <[email protected]>; David G. Johnston <[email protected]>; Mat Arye <[email protected]>; Matthias van de Meent <[email protected]>; Nikolay Samokhvalov <[email protected]>; Junwang Zhao <[email protected]>
Another source: Microservices Pattern: Database per service
|
|
|
| | |
|
|
|
| |
Microservices Pattern: Database per service
A service's database is private to that service
|
|
|
Sergey Prokhorenko [email protected]
On Friday, 22 March 2024 at 04:58:59 pm GMT+3, Sergey Prokhorenko <[email protected]> wrote:
BTW: Each microservice should have its own database to ensure data isolation and independence, enabling better scalability and fault tolerance
Source: Microservices Pattern: Shared database
|
|
| |
Microservices Pattern: Shared database
|
|
|
|
|
| |
Microservices Pattern: Shared database
|
|
|
Sergey Prokhorenko [email protected]
On Friday, 22 March 2024 at 04:42:20 pm GMT+3, Sergey Prokhorenko <[email protected]> wrote:
Why not use a single UUID generator for the database table in this case, similar to autoincrement?
Sergey Prokhorenko
[email protected]
On Friday, 22 March 2024 at 03:51:20 pm GMT+3, Peter Eisentraut <[email protected]> wrote:
On 21.03.24 16:21, Jelte Fennema-Nio wrote:
> On Wed, 20 Mar 2024 at 19:08, Andrey M. Borodin <[email protected]> wrote:
>> Timer-based bits contribute to global sortability. But the real timers we have are not even millisecond adjusted. We can hope for ~few ms variation in one datacenter or in presence of atomic clocks.
>
> I think the main benefit of using microseconds would not be
> sortability between servers, but sortability between backends.
There is that, and there are also multiple backend workers for one session.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: UUID v7
@ 2024-03-22 14:15 Peter Eisentraut <[email protected]>
parent: Andrey M. Borodin <[email protected]>
1 sibling, 1 reply; 19+ messages in thread
From: Peter Eisentraut @ 2024-03-22 14:15 UTC (permalink / raw)
To: Andrey M. Borodin <[email protected]>; +Cc: Aleksander Alekseev <[email protected]>; pgsql-hackers; Sergey Prokhorenko <[email protected]>; Jelte Fennema-Nio <[email protected]>; Przemysław Sztoch <[email protected]>; David G. Johnston <[email protected]>; Mat Arye <[email protected]>; Matthias van de Meent <[email protected]>; Nikolay Samokhvalov <[email protected]>; Junwang Zhao <[email protected]>
On 20.03.24 19:08, Andrey M. Borodin wrote:
>> On 19 Mar 2024, at 13:55, Peter Eisentraut <[email protected]> wrote:
>>
>> On 16.03.24 18:43, Andrey M. Borodin wrote:
>>>> On 15 Mar 2024, at 14:47, Aleksander Alekseev <[email protected]> wrote:
>>>>
>>>> +1 to the idea. I doubt that anyone will miss it.
>>> PFA v22.
>>> Changes:
>>> 1. Squashed all editorialisation by Jelte
>>> 2. Fixed my erroneous comments on using Method 2 (we are using method 1 instead)
>>> 3. Remove all traces of uuid_extract_variant()
>>
>> I have committed a subset of this for now, namely the additions of uuid_extract_timestamp() and uuid_extract_version(). These seemed mature and agreed upon. You can rebase the rest of your patch on top of that.
>
> Great! Thank you! PFA v23 with rebase on HEAD.
I have been studying the uuidv() function.
I find this code extremely hard to follow.
We don't need to copy all that documentation from the RFC 4122bis
document. People can read that themselves. What I would like to see is
easy to find information what from there we are implementing. Like,
- UUID version 7
- fixed-length dedicated counter
- counter is 18 bits
- 4 bits are initialized as zero
That's more or less all I would need to know what is going on.
That said, I don't understand why you say it's an 18 bit counter, when
you overwrite 6 bits with variant and version. Then it's just a 12 bit
counter? Which is the size of the rand_a field, so that kind of makes
sense. But 12 bits is the recommended minimum, and (in this patch) we
don't use sub-millisecond timestamp precision, so we should probably use
more than the minimum?
Also, you are initializing 4 bits (I think?) to zero to guard against
counter rollovers (so it's really just an 8 bit counter?). But nothing
checks against such rollovers, so I don't understand the use of that.
The code code be organized better. In the not-increment_counter case,
you could use two separate pg_strong_random calls: One to initialize
rand_b, starting at &uuid->data[8], and one to initialize the counter.
Then the former could be shared between the two branches, and the code
to assign the sequence_counter to the uuid fields could also be shared.
I would also prefer if the normal case (not-increment_counter) were the
first if branch.
Some other notes on your patch:
- Your rebase duplicated the documentation of uuid_extract_timestamp and
uuid_extract_version.
- PostgreSQL code uses uint64 etc. instead of uint64_t etc.
- It seems the added includes
#include "access/xlog.h"
#include "utils/builtins.h"
#include "utils/datetime.h"
are not needed.
- The static variables sequence_counter and previous_timestamp could be
kept inside the uuidv7() function.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: UUID v7
@ 2024-03-26 17:26 Andrey M. Borodin <[email protected]>
parent: Peter Eisentraut <[email protected]>
0 siblings, 0 replies; 19+ messages in thread
From: Andrey M. Borodin @ 2024-03-26 17:26 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; +Cc: Aleksander Alekseev <[email protected]>; pgsql-hackers; Sergey Prokhorenko <[email protected]>; Jelte Fennema-Nio <[email protected]>; Przemysław Sztoch <[email protected]>; David G. Johnston <[email protected]>; Mat Arye <[email protected]>; Matthias van de Meent <[email protected]>; Nikolay Samokhvalov <[email protected]>; Junwang Zhao <[email protected]>
Sorry for this long reply. I was looking on refactoring around pg_strong_random() and could not decide what to do. Finally, I decided to post at least something.
> On 22 Mar 2024, at 19:15, Peter Eisentraut <[email protected]> wrote:
>
> I have been studying the uuidv() function.
>
> I find this code extremely hard to follow.
>
> We don't need to copy all that documentation from the RFC 4122bis document. People can read that themselves. What I would like to see is easy to find information what from there we are implementing. Like,
>
> - UUID version 7
> - fixed-length dedicated counter
> - counter is 18 bits
> - 4 bits are initialized as zero
I've removed table taken from RFC.
> That's more or less all I would need to know what is going on.
>
> That said, I don't understand why you say it's an 18 bit counter, when you overwrite 6 bits with variant and version. Then it's just a 12 bit counter? Which is the size of the rand_a field, so that kind of makes sense. But 12 bits is the recommended minimum, and (in this patch) we don't use sub-millisecond timestamp precision, so we should probably use more than the minimum?
No, we use 4 bits in data[6], 8 bits in data[7], and 6 bits data[8]. It's 18 total. Essentially, we use both partial bytes and one whole byte between.
There was a bug - we used 1 extra byte of random numbers that was not necessary, I think that's what lead you to think that we use 12-bit counter.
> Also, you are initializing 4 bits (I think?) to zero to guard against counter rollovers (so it's really just an 8 bit counter?). But nothing checks against such rollovers, so I don't understand the use of that.
No, there's only one guard rollover bit.
Here: uuid->data[6] = (uuid->data[6] & 0xf7);
Bits that are called "guard bits" do not guard anything, they just ensure counter capacity when it is initialized.
Rollover is carried into time tick here:
++sequence_counter;
if (sequence_counter > 0x3ffff)
{
/* We only have 18-bit counter */
sequence_counter = 0;
previous_timestamp++;
}
I think we might use 10 bits of microseconds and have 8 bits of a counter. Effect of a counter won't change much. But I'm not sure if this is allowed per RFC.
If time source is coarse-grained it still acts like a random initializer. And when it is precise - time is "natural" source of entropy.
> The code code be organized better. In the not-increment_counter case, you could use two separate pg_strong_random calls: One to initialize rand_b, starting at &uuid->data[8], and one to initialize the counter. Then the former could be shared between the two branches, and the code to assign the sequence_counter to the uuid fields could also be shared.
Call to pg_strong_random() is very expensive in builds without ssl (and even with ssl too). If we could ammortize random numbers in small buffers - that would save a lot of time (see v8-0002-Buffer-random-numbers.patch upthread). Or, perhaps, we can ignore cost of two pg_string_random() calls.
>
> I would also prefer if the normal case (not-increment_counter) were the first if branch.
Done.
> Some other notes on your patch:
>
> - Your rebase duplicated the documentation of uuid_extract_timestamp and uuid_extract_version.
>
> - PostgreSQL code uses uint64 etc. instead of uint64_t etc.
>
> - It seems the added includes
>
> #include "access/xlog.h"
> #include "utils/builtins.h"
> #include "utils/datetime.h"
>
> are not needed.
>
> - The static variables sequence_counter and previous_timestamp could be kept inside the uuidv7() function.
Fixed.
Thanks!
Best regards, Andrey Borodin.
Attachments:
[application/octet-stream] v24-0001-Implement-UUID-v7.patch (14.5K, ../../[email protected]/2-v24-0001-Implement-UUID-v7.patch)
download | inline diff:
From 916bbaf8ce0602b5a3639a0207a52a8aea78487a Mon Sep 17 00:00:00 2001
From: "Andrey M. Borodin" <[email protected]>
Date: Wed, 20 Mar 2024 22:30:14 +0500
Subject: [PATCH v24] Implement UUID v7
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
This commit adds function for UUID generation. Most important function here
is uuidv7() which generates new UUID according to the new standard.
For code readability this commit adds alias uuidv4() to function
gen_random_uuid().
Author: Andrey Borodin
Reviewed-by: Sergey Prokhorenko, Kirk Wolak, Przemysław Sztoch
Reviewed-by: Nikolay Samokhvalov, Jelte Fennema-Nio, Aleksander Alekseev
Reviewed-by: Peter Eisentraut, Chris Travers, Lukas Fittl
Discussion: https://postgr.es/m/CAAhFRxitJv%3DyoGnXUgeLB_O%2BM7J2BJAmb5jqAT9gZ3bij3uLDA%40mail.gmail.com
---
doc/src/sgml/func.sgml | 19 ++-
src/backend/utils/adt/uuid.c | 165 ++++++++++++++++++++++-
src/include/catalog/pg_proc.dat | 6 +
src/test/regress/expected/opr_sanity.out | 2 +
src/test/regress/expected/uuid.out | 46 ++++++-
src/test/regress/sql/uuid.sql | 18 ++-
6 files changed, 249 insertions(+), 7 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 8ecc02f2b9..763fdab535 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14127,6 +14127,14 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
<primary>gen_random_uuid</primary>
</indexterm>
+ <indexterm>
+ <primary>uuidv4</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>uuidv7</primary>
+ </indexterm>
+
<indexterm>
<primary>uuid_extract_timestamp</primary>
</indexterm>
@@ -14136,12 +14144,17 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
</indexterm>
<para>
- <productname>PostgreSQL</productname> includes one function to generate a UUID:
+ <productname>PostgreSQL</productname> includes several functions to generate a UUID.
<synopsis>
<function>gen_random_uuid</function> () <returnvalue>uuid</returnvalue>
+<function>uuidv4</function> () <returnvalue>uuid</returnvalue>
+</synopsis>
+ These functions return a version 4 (random) UUID.
+<synopsis>
+<function>uuidv7</function> () <returnvalue>uuid</returnvalue>
</synopsis>
- This function returns a version 4 (random) UUID. This is the most commonly
- used type of UUID and is appropriate for most applications.
+ This function returns a version 7 UUID (UNIX timestamp with 1ms precision +
+ randomly seeded counter + random).
</para>
<para>
diff --git a/src/backend/utils/adt/uuid.c b/src/backend/utils/adt/uuid.c
index 45eb1b2fea..e3b42224e6 100644
--- a/src/backend/utils/adt/uuid.c
+++ b/src/backend/utils/adt/uuid.c
@@ -13,6 +13,8 @@
#include "postgres.h"
+#include <sys/time.h>
+
#include "common/hashfn.h"
#include "lib/hyperloglog.h"
#include "libpq/pqformat.h"
@@ -407,6 +409,12 @@ uuid_hash_extended(PG_FUNCTION_ARGS)
return hash_any_extended(key->data, UUID_LEN, PG_GETARG_INT64(1));
}
+/*
+ * Generate UUID version 4.
+ *
+ * All UUID bytes are filled with strong random numbers except version and
+ * variant 0b10 bits.
+ */
Datum
gen_random_uuid(PG_FUNCTION_ARGS)
{
@@ -427,7 +435,127 @@ gen_random_uuid(PG_FUNCTION_ARGS)
PG_RETURN_UUID_P(uuid);
}
-#define UUIDV1_EPOCH_JDATE 2299161 /* == date2j(1582,10,15) */
+/*
+ * Generate UUID version 7.
+ *
+ * Following description is taken from RFC draft and slightly extended to
+ * reflect implementation specific choices.
+ *
+ * "Fixed-Length Dedicated Counter Bits" (Method 1) can be implemented with
+ * arbitrary size of a counter. We choose size 18 to reuse all space of bytes
+ * that are touched by ver and var fields + rand_a bytes between them.
+ * Whenever timestamp unix_ts_ms is moving forward, this counter bits are
+ * reinitialized. Reinilialization always sets most significant bit to 0, other
+ * bits are initialized with random numbers. This gives as approximately 192K
+ * UUIDs within one millisecond without overflow. This ougth to be enough for
+ * most practical purposes. Whenever counter overflow happens, this overflow is
+ * translated to increment of unix_ts_ms. So generation of UUIDs ate rate
+ * higher than 128MHz might lead to using timestamps ahead of time.
+ *
+ * We're not using the "Replace Left-Most Random Bits with Increased Clock
+ * Precision" method Section 6.2 (Method 3), because of portability concerns.
+ * It's unclear if all supported platforms can provide reliable microsocond
+ * precision time.
+ *
+ * All UUID generator state is backend-local. For UUIDs generated in one
+ * backend we guarantee monotonicity. UUIDs generated on different backends
+ * will be mostly monotonic if they are generated at frequences less than 1KHz,
+ * but this monotonicity is not strictly guaranteed. UUIDs generated on
+ * different nodes are mostly monotonic with regards to possible clock drift.
+ * Uniqueness of UUIDs generated at the same timestamp across different
+ * backends and/or nodes is guaranteed by using random bits.
+ */
+Datum
+uuidv7(PG_FUNCTION_ARGS)
+{
+ static uint32 sequence_counter;
+ static uint64 previous_timestamp = 0;
+
+ pg_uuid_t *uuid = palloc(UUID_LEN);
+ uint64 tms;
+ struct timeval tp;
+ bool time_tick_forward;
+
+ gettimeofday(&tp, NULL);
+ tms = ((uint64) tp.tv_sec) * 1000 + (tp.tv_usec) / 1000;
+ /* time from clock is protected from backward leaps */
+ time_tick_forward = (tms > previous_timestamp);
+
+ if (time_tick_forward)
+ {
+ /* fill everything after the timestamp with random bytes */
+ if (!pg_strong_random(&uuid->data[6], UUID_LEN - 6))
+ ereport(ERROR,
+ (errcode(ERRCODE_INTERNAL_ERROR),
+ errmsg("could not generate random values")));
+
+ /*
+ * Left-most counter bit is initialized as zero for the sole purpose
+ * of guarding against counter rollovers. See section "Fixed-Length
+ * Dedicated Counter Seeding"
+ * https://datatracker.ietf.org/doc/html/draft-ietf-uuidrev-rfc4122bis#monotonicity_counters
+ */
+ uuid->data[6] = (uuid->data[6] & 0xf7);
+
+ /* read randomly initialized bits of counter */
+ sequence_counter = ((uint32) uuid->data[8] & 0x3f) +
+ (((uint32) uuid->data[7]) << 6) +
+ (((uint32) uuid->data[6] & 0x0f) << 14);
+
+ previous_timestamp = tms;
+ }
+ else
+ {
+ /*
+ * Time did not advance from the previous generation, we must
+ * increment counter
+ */
+ ++sequence_counter;
+ if (sequence_counter > 0x3ffff)
+ {
+ /* We only have 18-bit counter */
+ sequence_counter = 0;
+ previous_timestamp++;
+ }
+
+ /* protection from leap backward */
+ tms = previous_timestamp;
+
+ /* fill everything after the timestamp and counter with random bytes */
+ if (!pg_strong_random(&uuid->data[9], UUID_LEN - 9))
+ ereport(ERROR,
+ (errcode(ERRCODE_INTERNAL_ERROR),
+ errmsg("could not generate random values")));
+
+ /* most significant 4 bits of 18-bit counter */
+ uuid->data[6] = (unsigned char) (sequence_counter >> 14);
+ /* next 8 bits */
+ uuid->data[7] = (unsigned char) (sequence_counter >> 6);
+ /* least significant 6 bits */
+ uuid->data[8] = (unsigned char) (sequence_counter);
+ }
+
+ /* Fill in time part */
+ uuid->data[0] = (unsigned char) (tms >> 40);
+ uuid->data[1] = (unsigned char) (tms >> 32);
+ uuid->data[2] = (unsigned char) (tms >> 24);
+ uuid->data[3] = (unsigned char) (tms >> 16);
+ uuid->data[4] = (unsigned char) (tms >> 8);
+ uuid->data[5] = (unsigned char) tms;
+
+ /*
+ * Set magic numbers for a "version 7" (pseudorandom) UUID, see
+ * https://datatracker.ietf.org/doc/html/draft-ietf-uuidrev-rfc4122bis
+ */
+ /* set version field, top four bits are 0, 1, 1, 1 */
+ uuid->data[6] = (uuid->data[6] & 0x0f) | 0x70;
+ /* set variant field, top two bits are 1, 0 */
+ uuid->data[8] = (uuid->data[8] & 0x3f) | 0x80;
+
+ PG_RETURN_UUID_P(uuid);
+}
+
+#define GREGORIAN_EPOCH_JDATE 2299161 /* == date2j(1582,10,15) */
/*
* Extract timestamp from UUID.
@@ -461,7 +589,40 @@ uuid_extract_timestamp(PG_FUNCTION_ARGS)
/* convert 100-ns intervals to us, then adjust */
ts = (TimestampTz) (tms / 10) -
- ((uint64) POSTGRES_EPOCH_JDATE - UUIDV1_EPOCH_JDATE) * SECS_PER_DAY * USECS_PER_SEC;
+ ((uint64) POSTGRES_EPOCH_JDATE - GREGORIAN_EPOCH_JDATE) * SECS_PER_DAY * USECS_PER_SEC;
+ PG_RETURN_TIMESTAMPTZ(ts);
+ }
+
+ if (version == 6)
+ {
+ tms = ((uint64) uuid->data[0]) << 52;
+ tms += ((uint64) uuid->data[1]) << 44;
+ tms += ((uint64) uuid->data[2]) << 36;
+ tms += ((uint64) uuid->data[3]) << 28;
+ tms += ((uint64) uuid->data[4]) << 20;
+ tms += ((uint64) uuid->data[5]) << 12;
+ tms += (((uint64) uuid->data[6]) & 0xf) << 8;
+ tms += ((uint64) uuid->data[7]);
+
+ /* convert 100-ns intervals to us, then adjust */
+ ts = (TimestampTz) (tms / 10) -
+ ((uint64) POSTGRES_EPOCH_JDATE - GREGORIAN_EPOCH_JDATE) * SECS_PER_DAY * USECS_PER_SEC;
+
+ PG_RETURN_TIMESTAMPTZ(ts);
+ }
+
+ if (version == 7)
+ {
+ tms = uuid->data[5];
+ tms += ((uint64) uuid->data[4]) << 8;
+ tms += ((uint64) uuid->data[3]) << 16;
+ tms += ((uint64) uuid->data[2]) << 24;
+ tms += ((uint64) uuid->data[1]) << 32;
+ tms += ((uint64) uuid->data[0]) << 40;
+
+ /* convert ms to us, then adjust */
+ ts = (TimestampTz) (tms * 1000) -
+ (POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY * USECS_PER_SEC;
PG_RETURN_TIMESTAMPTZ(ts);
}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 0d26e5b422..2d8bbd3137 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -9173,6 +9173,12 @@
{ oid => '3432', descr => 'generate random UUID',
proname => 'gen_random_uuid', proleakproof => 't', provolatile => 'v',
prorettype => 'uuid', proargtypes => '', prosrc => 'gen_random_uuid' },
+{ oid => '9895', descr => 'generate UUID version 4',
+ proname => 'uuidv4', proleakproof => 't', provolatile => 'v',
+ prorettype => 'uuid', proargtypes => '', prosrc => 'gen_random_uuid' },
+{ oid => '9896', descr => 'generate UUID version 7',
+ proname => 'uuidv7', proleakproof => 't', provolatile => 'v',
+ prorettype => 'uuid', proargtypes => '', prosrc => 'uuidv7' },
{ oid => '9897', descr => 'extract timestamp from UUID',
proname => 'uuid_extract_timestamp', proleakproof => 't',
prorettype => 'timestamptz', proargtypes => 'uuid',
diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index 9d047b21b8..2b5d67b7f0 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -872,6 +872,8 @@ xid8ge(xid8,xid8)
xid8eq(xid8,xid8)
xid8ne(xid8,xid8)
xid8cmp(xid8,xid8)
+uuidv4()
+uuidv7()
uuid_extract_timestamp(uuid)
uuid_extract_version(uuid)
-- restore normal output mode
diff --git a/src/test/regress/expected/uuid.out b/src/test/regress/expected/uuid.out
index 6026e15ed3..4f010eed67 100644
--- a/src/test/regress/expected/uuid.out
+++ b/src/test/regress/expected/uuid.out
@@ -168,6 +168,26 @@ SELECT count(DISTINCT guid_field) FROM guid1;
2
(1 row)
+-- test of uuidv4() alias
+TRUNCATE guid1;
+INSERT INTO guid1 (guid_field) VALUES (uuidv4());
+INSERT INTO guid1 (guid_field) VALUES (uuidv4());
+SELECT count(DISTINCT guid_field) FROM guid1;
+ count
+-------
+ 2
+(1 row)
+
+-- generation test for v7
+TRUNCATE guid1;
+INSERT INTO guid1 (guid_field) VALUES (uuidv7());
+INSERT INTO guid1 (guid_field) VALUES (uuidv7());
+SELECT count(DISTINCT guid_field) FROM guid1;
+ count
+-------
+ 2
+(1 row)
+
-- extract functions
-- version
SELECT uuid_extract_version('11111111-1111-5111-8111-111111111111'); -- 5
@@ -188,8 +208,32 @@ SELECT uuid_extract_version('11111111-1111-1111-1111-111111111111'); -- null
(1 row)
+SELECT uuid_extract_version(uuidv4()); --4
+ uuid_extract_version
+----------------------
+ 4
+(1 row)
+
+SELECT uuid_extract_version(uuidv7()); --7
+ uuid_extract_version
+----------------------
+ 7
+(1 row)
+
-- timestamp
-SELECT uuid_extract_timestamp('C232AB00-9414-11EC-B3C8-9F6BDECED846') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00'; -- RFC 4122bis test vector
+SELECT uuid_extract_timestamp('C232AB00-9414-11EC-B3C8-9F6BDECED846') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00'; -- RFC 4122bis test vector for v1
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT uuid_extract_timestamp('1EC9414C-232A-6B00-B3C8-9F6BDECED846') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00'; -- RFC 4122bis test vector for v6
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT uuid_extract_timestamp('017F22E2-79B0-7CC3-98C4-DC0C0C07398F') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00'; -- RFC 4122bis test vector for v7
?column?
----------
t
diff --git a/src/test/regress/sql/uuid.sql b/src/test/regress/sql/uuid.sql
index c88f6d087a..6fa039800a 100644
--- a/src/test/regress/sql/uuid.sql
+++ b/src/test/regress/sql/uuid.sql
@@ -85,6 +85,18 @@ INSERT INTO guid1 (guid_field) VALUES (gen_random_uuid());
INSERT INTO guid1 (guid_field) VALUES (gen_random_uuid());
SELECT count(DISTINCT guid_field) FROM guid1;
+-- test of uuidv4() alias
+TRUNCATE guid1;
+INSERT INTO guid1 (guid_field) VALUES (uuidv4());
+INSERT INTO guid1 (guid_field) VALUES (uuidv4());
+SELECT count(DISTINCT guid_field) FROM guid1;
+
+-- generation test for v7
+TRUNCATE guid1;
+INSERT INTO guid1 (guid_field) VALUES (uuidv7());
+INSERT INTO guid1 (guid_field) VALUES (uuidv7());
+SELECT count(DISTINCT guid_field) FROM guid1;
+
-- extract functions
@@ -92,9 +104,13 @@ SELECT count(DISTINCT guid_field) FROM guid1;
SELECT uuid_extract_version('11111111-1111-5111-8111-111111111111'); -- 5
SELECT uuid_extract_version(gen_random_uuid()); -- 4
SELECT uuid_extract_version('11111111-1111-1111-1111-111111111111'); -- null
+SELECT uuid_extract_version(uuidv4()); --4
+SELECT uuid_extract_version(uuidv7()); --7
-- timestamp
-SELECT uuid_extract_timestamp('C232AB00-9414-11EC-B3C8-9F6BDECED846') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00'; -- RFC 4122bis test vector
+SELECT uuid_extract_timestamp('C232AB00-9414-11EC-B3C8-9F6BDECED846') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00'; -- RFC 4122bis test vector for v1
+SELECT uuid_extract_timestamp('1EC9414C-232A-6B00-B3C8-9F6BDECED846') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00'; -- RFC 4122bis test vector for v6
+SELECT uuid_extract_timestamp('017F22E2-79B0-7CC3-98C4-DC0C0C07398F') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00'; -- RFC 4122bis test vector for v7
SELECT uuid_extract_timestamp(gen_random_uuid()); -- null
SELECT uuid_extract_timestamp('11111111-1111-1111-1111-111111111111'); -- null
--
2.37.1 (Apple Git-137.1)
^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2024-03-26 17:26 UTC | newest]
Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-03-06 23:23 [PATCH v31 06/11] pg_ls_logdir to ignore error if initial/top dir is missing.. Justin Pryzby <[email protected]>
2024-03-06 07:13 Re: UUID v7 Peter Eisentraut <[email protected]>
2024-03-10 12:59 ` Re: UUID v7 Andrey M. Borodin <[email protected]>
2024-03-14 11:07 ` Re: UUID v7 Peter Eisentraut <[email protected]>
2024-03-14 11:25 ` Re: UUID v7 Andrey M. Borodin <[email protected]>
2024-03-14 15:10 ` Re: UUID v7 Peter Eisentraut <[email protected]>
2024-03-14 18:06 ` Re: UUID v7 Andrey M. Borodin <[email protected]>
2024-03-15 09:47 ` Re: UUID v7 Aleksander Alekseev <[email protected]>
2024-03-16 17:43 ` Re: UUID v7 Andrey M. Borodin <[email protected]>
2024-03-19 08:55 ` Re: UUID v7 Peter Eisentraut <[email protected]>
2024-03-20 18:08 ` Re: UUID v7 Andrey M. Borodin <[email protected]>
2024-03-21 15:21 ` Re: UUID v7 Jelte Fennema-Nio <[email protected]>
2024-03-22 06:53 ` Re: UUID v7 Andrey M. Borodin <[email protected]>
2024-03-22 12:51 ` Re: UUID v7 Peter Eisentraut <[email protected]>
2024-03-22 13:42 ` Re: UUID v7 Sergey Prokhorenko <[email protected]>
2024-03-22 13:58 ` Re: UUID v7 Sergey Prokhorenko <[email protected]>
2024-03-22 14:06 ` Re: UUID v7 Sergey Prokhorenko <[email protected]>
2024-03-22 14:15 ` Re: UUID v7 Peter Eisentraut <[email protected]>
2024-03-26 17:26 ` Re: UUID v7 Andrey M. Borodin <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox