On Wed, Dec 4, 2024 at 9:04 AM Andrey M. Borodin <x4...@yandex-team.ru> wrote:
>
>
>
> > On 2 Dec 2024, at 11:00, Masahiko Sawada <sawada.m...@gmail.com> wrote:
> >
> > The monotonicity of generated UUIDv7 is guaranteed only within a
> > single backend.
>
> I've addressed all items, except formatting a table... I can't build docs to
> make a reasonable judgement if the table looks OK.
>
Thank you for updating the patch!
> Changes:
> - restored leakproof flag of functions without arguments to be consistent
> with gen_random_uuid()
If I understand the below Peter's comment correctly, we don't need to
mark all of three functions leakproof:
>
> * src/test/regress/expected/opr_sanity.out
>
> +uuidv4()
> +uuidv7()
> +uuidv7(interval)
>
> Functions without arguments don't need to be marked leakproof.
>
> uuidv7(interval) internally calls timestamptz_pl_interval(), which is
> not leakproof, so I don't think that classification is sound.
I've attached the updated patches. The 0001 patch unmarks the existing
gen_random_uuid() leakproof and is being discussed on another
thread[1]. I'm going to push the main UUIDv7 patch barring objections
and further comments, after pushing the fix for gen_random_uuid().
>
> Also PFA a prototype of making uuidv7() ordered across all backends via
> keeping previous_ns in shared memory. IMO it's overcomplicating and RFC does
> not require such guarantees. Also, this would cost us several hundreds of ns
> on each uuidv7() call. I think we should focus on committing existing
> implementation and leave such things for a future improvement.
I also feel like it's overcomplicating. We can focus on the main patch
and can implement it later if we really need it.
Regards,
[1]
https://www.postgresql.org/message-id/CAD21AoBE1ePPWY1NQEgk3DkqjYzLPZwYTzCySHm0e%2B9a69PfZw%40mail.gmail.com
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
From 960c0df86affe4cbc6feba74df6d9643e37506e4 Mon Sep 17 00:00:00 2001
From: Masahiko Sawada <sawada.mshk@gmail.com>
Date: Mon, 9 Dec 2024 13:17:27 -0800
Subject: [PATCH v42 1/3] Unmark gen_random_uuid() leakproof.
---
src/include/catalog/pg_proc.dat | 2 +-
src/test/regress/expected/opr_sanity.out | 1 -
2 files changed, 1 insertion(+), 2 deletions(-)
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 9575524007f..ccf79761da5 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -9345,7 +9345,7 @@
proname => 'uuid_hash_extended', prorettype => 'int8',
proargtypes => 'uuid int8', prosrc => 'uuid_hash_extended' },
{ oid => '3432', descr => 'generate random UUID',
- proname => 'gen_random_uuid', proleakproof => 't', provolatile => 'v',
+ proname => 'gen_random_uuid', provolatile => 'v',
prorettype => 'uuid', proargtypes => '', prosrc => 'gen_random_uuid' },
{ oid => '6342', descr => 'extract timestamp from UUID',
proname => 'uuid_extract_timestamp', proleakproof => 't',
diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index 34a32bd11d2..452f2572302 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -855,7 +855,6 @@ sha224(bytea)
sha256(bytea)
sha384(bytea)
sha512(bytea)
-gen_random_uuid()
starts_with(text,text)
macaddr8_eq(macaddr8,macaddr8)
macaddr8_lt(macaddr8,macaddr8)
--
2.43.5
From cb7912a7d97e78253ce498d23224097cf2f5d895 Mon Sep 17 00:00:00 2001
From: "Andrey M. Borodin" <x4mmm@night.local>
Date: Wed, 20 Mar 2024 22:30:14 +0500
Subject: [PATCH v42 2/3] Add UUID version 7 generation function.
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
This commit implements the uuidv7() SQL function for generating UUID
version 7, as defined in RFC 9652. UUID v7 comprises a Unix timestamp
in milliseconds and random bits, providing uniqueness and
sortability.
In our implementation, the 12-bit sub-millisecond timestamp fraction
is stored immediately after the timestamp, referred to as "rand_a" in
the RFC. This ensures additional monotonicity within a millisecond.
Additionally, an alias uuidv4() is added for the existing
gen_random_uuid() SQL function to maintain consistency.
XXX Bump catalog 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
Reviewed-by: Michael Paquier, Masahiko Sawada, Stepan Neretin
Discussion: https://postgr.es/m/CAAhFRxitJv%3DyoGnXUgeLB_O%2BM7J2BJAmb5jqAT9gZ3bij3uLDA%40mail.gmail.com
---
doc/src/sgml/datatype.sgml | 2 +-
doc/src/sgml/func.sgml | 30 +++-
src/backend/utils/adt/uuid.c | 248 +++++++++++++++++++++++++++--
src/include/catalog/pg_proc.dat | 11 +-
src/test/regress/expected/uuid.out | 56 ++++++-
src/test/regress/sql/uuid.sql | 28 +++-
6 files changed, 352 insertions(+), 23 deletions(-)
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index e0d33f12e1c..3e6751d64cc 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -4380,7 +4380,7 @@ SELECT to_tsvector( 'postgraduate' ), to_tsquery( 'postgres:*' );
<para>
The data type <type>uuid</type> stores Universally Unique Identifiers
- (UUID) as defined by <ulink url="https://datatracker.ietf.org/doc/html/rfc4122";>RFC 4122</ulink>,
+ (UUID) as defined by <ulink url="https://datatracker.ietf.org/doc/html/rfc9562";>RFC 9562</ulink>,
ISO/IEC 9834-8:2005, and related standards.
(Some systems refer to this data type as a globally unique identifier, or
GUID,<indexterm><primary>GUID</primary></indexterm> instead.) This
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 8b81106fa23..bf3a3041344 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14255,6 +14255,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>
@@ -14264,12 +14272,19 @@ 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> (<optional> <parameter>shift</parameter> <type>interval</type> </optional>) <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 millisecond
+ precision + sub-millisecond timestamp + random). This function can accept
+ optional <parameter>shift</parameter> parameter of type <type>interval</type>
+ which shift internal timestamp by the given interval.
</para>
<para>
@@ -14283,9 +14298,10 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
<function>uuid_extract_timestamp</function> (uuid) <returnvalue>timestamp with time zone</returnvalue>
</synopsis>
This function extracts a <type>timestamp with time zone</type> from UUID
- version 1. For other versions, this function returns null. Note that the
- extracted timestamp is not necessarily exactly equal to the time the UUID
- was generated; this depends on the implementation that generated the UUID.
+ version 1 and 7. For other versions, this function returns null. Note that
+ the extracted timestamp is not necessarily exactly equal to the time the
+ UUID was generated; this depends on the implementation that generated the
+ UUID.
</para>
<para>
@@ -14293,7 +14309,7 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
<function>uuid_extract_version</function> (uuid) <returnvalue>smallint</returnvalue>
</synopsis>
This function extracts the version from a UUID of the variant described by
- <ulink url="https://datatracker.ietf.org/doc/html/rfc4122";>RFC 4122</ulink>. For
+ <ulink url="https://datatracker.ietf.org/doc/html/rfc9562";>RFC 9562</ulink>. For
other variants, this function returns null. For example, for a UUID
generated by <function>gen_random_uuid</function>, this function will
return 4.
diff --git a/src/backend/utils/adt/uuid.c b/src/backend/utils/adt/uuid.c
index 5284d23dcc4..5842e8b9f4a 100644
--- a/src/backend/utils/adt/uuid.c
+++ b/src/backend/utils/adt/uuid.c
@@ -13,6 +13,8 @@
#include "postgres.h"
+#include <time.h> /* for clock_gettime() */
+
#include "common/hashfn.h"
#include "lib/hyperloglog.h"
#include "libpq/pqformat.h"
@@ -23,6 +25,34 @@
#include "utils/timestamp.h"
#include "utils/uuid.h"
+/* helper macros */
+#define NS_PER_S INT64CONST(1000000000)
+#define NS_PER_MS INT64CONST(1000000)
+#define NS_PER_US INT64CONST(1000)
+
+/*
+ * UUID version 7 uses 12 bits in "rand_a" to store 1/4096 (or 2^12) fractions of
+ * sub-millisecond. While most Unix-like platforms provide nanosecond-precision
+ * timestamps, some systems only offer microsecond precision, limiting us to 10
+ * bits of sub-millisecond information. For example, on macOS, real time is
+ * truncated to microseconds. Additionally, MSVC uses the ported version of
+ * gettimeofday() that returns microsecond precision.
+ *
+ * On systems with only 10 bits of sub-millisecond precision, we still use
+ * 1/4096 parts of a millisecond, but fill lower 2 bits with random numbers
+ * (see generate_uuidv7() for details).
+ *
+ * SUBMS_MINIMAL_STEP_NS defines the minimum number of nanoseconds that guarantees
+ * an increase in the UUID's clock precision.
+ */
+#if defined(__darwin__) || defined(_MSC_VER)
+#define SUBMS_MINIMAL_STEP_BITS 10
+#else
+#define SUBMS_MINIMAL_STEP_BITS 12
+#endif
+#define SUBMS_BITS 12
+#define SUBMS_MINIMAL_STEP_NS ((NS_PER_MS / (1 << SUBMS_MINIMAL_STEP_BITS)) + 1)
+
/* sortsupport for uuid */
typedef struct
{
@@ -37,6 +67,8 @@ static int uuid_internal_cmp(const pg_uuid_t *arg1, const pg_uuid_t *arg2);
static int uuid_fast_cmp(Datum x, Datum y, SortSupport ssup);
static bool uuid_abbrev_abort(int memtupcount, SortSupport ssup);
static Datum uuid_abbrev_convert(Datum original, SortSupport ssup);
+static inline void uuid_set_version(pg_uuid_t *uuid, unsigned char version);
+static inline int64 get_real_time_ns_ascending();
Datum
uuid_in(PG_FUNCTION_ARGS)
@@ -401,6 +433,25 @@ uuid_hash_extended(PG_FUNCTION_ARGS)
return hash_any_extended(key->data, UUID_LEN, PG_GETARG_INT64(1));
}
+/*
+ * Set the given UUID version and the variant bits
+ */
+static inline void
+uuid_set_version(pg_uuid_t *uuid, unsigned char version)
+{
+ /* set version field, top four bits */
+ uuid->data[6] = (uuid->data[6] & 0x0f) | (version << 4);
+
+ /* set variant field, top two bits are 1, 0 */
+ uuid->data[8] = (uuid->data[8] & 0x3f) | 0x80;
+}
+
+/*
+ * Generate UUID version 4.
+ *
+ * All UUID bytes are filled with strong random numbers except version and
+ * variant bits.
+ */
Datum
gen_random_uuid(PG_FUNCTION_ARGS)
{
@@ -412,21 +463,183 @@ gen_random_uuid(PG_FUNCTION_ARGS)
errmsg("could not generate random values")));
/*
- * Set magic numbers for a "version 4" (pseudorandom) UUID, see
- * http://tools.ietf.org/html/rfc4122#section-4.4
+ * Set magic numbers for a "version 4" (pseudorandom) UUID and variant,
+ * see https://datatracker.ietf.org/doc/html/rfc9562#name-uuid-version-4
*/
- uuid->data[6] = (uuid->data[6] & 0x0f) | 0x40; /* time_hi_and_version */
- uuid->data[8] = (uuid->data[8] & 0x3f) | 0x80; /* clock_seq_hi_and_reserved */
+ uuid_set_version(uuid, 4);
PG_RETURN_UUID_P(uuid);
}
-#define UUIDV1_EPOCH_JDATE 2299161 /* == date2j(1582,10,15) */
+/*
+ * Get the current timestamp with nanosecond precision for UUID generation.
+ * The returned timestamp is ensured to be at least SUBMS_MINIMAL_STEP greater
+ * than the previous returned timestamp (on this backend).
+ */
+static inline int64
+get_real_time_ns_ascending()
+{
+ static int64 previous_ns = 0;
+ int64 ns;
+
+ /* Get the current real timestamp */
+
+#ifdef _MSC_VER
+ struct timeval tmp;
+
+ gettimeofday(&tmp, NULL);
+ ns = tmp.tv_sec * NS_PER_S + tmp.tv_usec * NS_PER_US;
+#else
+ struct timespec tmp;
+
+ /*
+ * We don't use gettimeofday(), instead use clock_gettime() with
+ * CLOCK_REALTIME where available in order to get a high-precision
+ * (nanoseconds) real timestamp.
+ *
+ * Note while a timestamp returned by clock_gettime() with CLOCK_REALTIME
+ * is nanosecond-precision on most Unix-like platforms, on some platforms
+ * such as macOS it's restricted to microsecond-precision.
+ */
+ clock_gettime(CLOCK_REALTIME, &tmp);
+ ns = tmp.tv_sec * NS_PER_S + tmp.tv_nsec;
+#endif
+
+ /* Guarantee the minimal step advancement of the timestamp */
+ if (previous_ns + SUBMS_MINIMAL_STEP_NS >= ns)
+ ns = previous_ns + SUBMS_MINIMAL_STEP_NS;
+ previous_ns = ns;
+
+ return ns;
+}
+
+/*
+ * Generate UUID version 7 per RFC 9562, with the given timestamp.
+ *
+ * UUID version 7 consists of a Unix timestamp in milliseconds (48 bits) and
+ * 74 random bits, excluding the required version and variant bits. To ensure
+ * monotonicity in scenarios of high-frequency UUID generation, we employ the
+ * method "Replace Leftmost Random Bits with Increased Clock Precision (Method 3)",
+ * described in the RFC. This method utilizes 12 bits from the "rand_a" bits
+ * to store a 1/4096 (or 2^12) fraction of sub-millisecond precision.
+ *
+ * ns is a number of nanoseconds since start of the UNIX epoch. This value is
+ * used for time-dependent bits of UUID.
+ */
+static pg_uuid_t *
+generate_uuidv7(int64 ns)
+{
+ pg_uuid_t *uuid = palloc(UUID_LEN);
+ int64 unix_ts_ms;
+ int32 increased_clock_precision;
+
+ unix_ts_ms = ns / NS_PER_MS;
+
+ /* Fill in time part */
+ uuid->data[0] = (unsigned char) (unix_ts_ms >> 40);
+ uuid->data[1] = (unsigned char) (unix_ts_ms >> 32);
+ uuid->data[2] = (unsigned char) (unix_ts_ms >> 24);
+ uuid->data[3] = (unsigned char) (unix_ts_ms >> 16);
+ uuid->data[4] = (unsigned char) (unix_ts_ms >> 8);
+ uuid->data[5] = (unsigned char) unix_ts_ms;
+
+ /*
+ * sub-millisecond timestamp fraction (SUBMS_BITS bits, not
+ * SUBMS_MINIMAL_STEP_BITS)
+ */
+ increased_clock_precision = ((ns % NS_PER_MS) * (1 << SUBMS_BITS)) / NS_PER_MS;
+
+ /* Fill the increased clock precision to "rand_a" bits */
+ uuid->data[6] = (unsigned char) (increased_clock_precision >> 8);
+ uuid->data[7] = (unsigned char) (increased_clock_precision);
+
+ /* fill everything after the increased clock precision 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")));
+
+#if SUBMS_MINIMAL_STEP_BITS == 10
+
+ /*
+ * On systems that have only 10 bits of sub-ms precision, 2 least
+ * significant are dependent on other time-specific bits, and they do not
+ * contribute to uniqueness. To make these bit random we mix in two bits
+ * from CSPRNG. SUBMS_MINIMAL_STEP is chosen so that we still guarantee
+ * monotonicity despite altering these bits.
+ */
+ uuid->data[7] = uuid->data[7] ^ (uuid->data[8] >> 6);
+#endif
+
+ /*
+ * Set magic numbers for a "version 7" (pseudorandom) UUID and variant,
+ * see https://www.rfc-editor.org/rfc/rfc9562#name-version-field
+ */
+ uuid_set_version(uuid, 7);
+
+ return uuid;
+}
+
+/*
+ * Generate UUID version 7 with the current timestamp.
+ */
+Datum
+uuidv7(PG_FUNCTION_ARGS)
+{
+ pg_uuid_t *uuid = generate_uuidv7(get_real_time_ns_ascending());
+
+ PG_RETURN_UUID_P(uuid);
+}
+
+/*
+ * Similar to uuidv7() but with the timestamp adjusted by the given interval.
+ */
+Datum
+uuidv7_interval(PG_FUNCTION_ARGS)
+{
+ Interval *shift = PG_GETARG_INTERVAL_P(0);
+ TimestampTz ts;
+ pg_uuid_t *uuid;
+ int64 ns = get_real_time_ns_ascending();
+
+ /*
+ * Shift the current timestamp by the given interval. To calsulate time
+ * shift correctly, we convert the UNIX epoch to TimestampTz and use
+ * timestamptz_pl_interval(). Since this calculation is done with
+ * microsecond precision, we carry nanoseconds from original ns value to
+ * shifted ns value.
+ */
+
+ ts = (TimestampTz) (ns / NS_PER_US) -
+ (POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY * USECS_PER_SEC;
+
+ /* Compute time shift */
+ ts = DatumGetTimestampTz(DirectFunctionCall2(timestamptz_pl_interval,
+ TimestampTzGetDatum(ts),
+ IntervalPGetDatum(shift)));
+
+ /*
+ * Convert a TimestampTz value back to an UNIX epoch and back nanoseconds.
+ */
+ ns = (ts + (POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY * USECS_PER_SEC)
+ * NS_PER_US + ns % NS_PER_US;
+
+ /* Generate an UUIDv7 */
+ uuid = generate_uuidv7(ns);
+
+ PG_RETURN_UUID_P(uuid);
+}
+
+/*
+ * Start of a Gregorian epoch == date2j(1582,10,15)
+ * We cast it to 64-bit because it's used in overflow-prone computations
+ */
+#define GREGORIAN_EPOCH_JDATE INT64CONST(2299161)
/*
* Extract timestamp from UUID.
*
- * Returns null if not RFC 4122 variant or not a version that has a timestamp.
+ * Returns null if not RFC 9562 variant or not a version that has a timestamp.
*/
Datum
uuid_extract_timestamp(PG_FUNCTION_ARGS)
@@ -436,7 +649,7 @@ uuid_extract_timestamp(PG_FUNCTION_ARGS)
uint64 tms;
TimestampTz ts;
- /* check if RFC 4122 variant */
+ /* check if RFC 9562 variant */
if ((uuid->data[8] & 0xc0) != 0x80)
PG_RETURN_NULL();
@@ -455,7 +668,22 @@ 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 == 7)
+ {
+ tms = (uuid->data[5])
+ + (((uint64) uuid->data[4]) << 8)
+ + (((uint64) uuid->data[3]) << 16)
+ + (((uint64) uuid->data[2]) << 24)
+ + (((uint64) uuid->data[1]) << 32)
+ + (((uint64) uuid->data[0]) << 40);
+
+ /* convert ms to us, then adjust */
+ ts = (TimestampTz) (tms * NS_PER_US) -
+ (POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY * USECS_PER_SEC;
PG_RETURN_TIMESTAMPTZ(ts);
}
@@ -467,7 +695,7 @@ uuid_extract_timestamp(PG_FUNCTION_ARGS)
/*
* Extract UUID version.
*
- * Returns null if not RFC 4122 variant.
+ * Returns null if not RFC 9562 variant.
*/
Datum
uuid_extract_version(PG_FUNCTION_ARGS)
@@ -475,7 +703,7 @@ uuid_extract_version(PG_FUNCTION_ARGS)
pg_uuid_t *uuid = PG_GETARG_UUID_P(0);
uint16 version;
- /* check if RFC 4122 variant */
+ /* check if RFC 9562 variant */
if ((uuid->data[8] & 0xc0) != 0x80)
PG_RETURN_NULL();
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index ccf79761da5..0f22c217235 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -9347,11 +9347,20 @@
{ oid => '3432', descr => 'generate random UUID',
proname => 'gen_random_uuid', provolatile => 'v',
prorettype => 'uuid', proargtypes => '', prosrc => 'gen_random_uuid' },
+{ oid => '9895', descr => 'generate UUID version 4',
+ proname => 'uuidv4', provolatile => 'v',
+ prorettype => 'uuid', proargtypes => '', prosrc => 'gen_random_uuid' },
+{ oid => '9896', descr => 'generate UUID version 7',
+ proname => 'uuidv7', provolatile => 'v',
+ prorettype => 'uuid', proargtypes => '', prosrc => 'uuidv7' },
+{ oid => '9897', descr => 'generate UUID version 7 with a timestamp shifted by specified interval',
+ proname => 'uuidv7', provolatile => 'v', proargnames => '{shift}',
+ prorettype => 'uuid', proargtypes => 'interval', prosrc => 'uuidv7_interval' },
{ oid => '6342', descr => 'extract timestamp from UUID',
proname => 'uuid_extract_timestamp', proleakproof => 't',
prorettype => 'timestamptz', proargtypes => 'uuid',
prosrc => 'uuid_extract_timestamp' },
-{ oid => '6343', descr => 'extract version from RFC 4122 UUID',
+{ oid => '6343', descr => 'extract version from RFC 9562 UUID',
proname => 'uuid_extract_version', proleakproof => 't', prorettype => 'int2',
proargtypes => 'uuid', prosrc => 'uuid_extract_version' },
diff --git a/src/test/regress/expected/uuid.out b/src/test/regress/expected/uuid.out
index 8f4ef0d7a6a..798633ad51e 100644
--- a/src/test/regress/expected/uuid.out
+++ b/src/test/regress/expected/uuid.out
@@ -10,6 +10,11 @@ CREATE TABLE guid2
guid_field UUID,
text_field TEXT DEFAULT(now())
);
+CREATE TABLE guid3
+(
+ id SERIAL,
+ guid_field UUID
+);
-- inserting invalid data tests
-- too long
INSERT INTO guid1(guid_field) VALUES('11111111-1111-1111-1111-111111111111F');
@@ -199,6 +204,35 @@ 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());
+INSERT INTO guid1 (guid_field) VALUES (uuidv7(INTERVAL '1 day'));
+SELECT count(DISTINCT guid_field) FROM guid1;
+ count
+-------
+ 3
+(1 row)
+
+-- test sortability of v7
+INSERT INTO guid3 (guid_field) SELECT uuidv7() FROM generate_series(1, 10);
+SELECT array_agg(id ORDER BY guid_field) FROM guid3;
+ array_agg
+------------------------
+ {1,2,3,4,5,6,7,8,9,10}
+(1 row)
+
-- extract functions
-- version
SELECT uuid_extract_version('11111111-1111-5111-8111-111111111111'); -- 5
@@ -219,8 +253,26 @@ 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 9562 test vector for v1
+ ?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 9562 test vector for v7
?column?
----------
t
@@ -239,4 +291,4 @@ SELECT uuid_extract_timestamp('11111111-1111-1111-1111-111111111111'); -- null
(1 row)
-- clean up
-DROP TABLE guid1, guid2 CASCADE;
+DROP TABLE guid1, guid2, guid3 CASCADE;
diff --git a/src/test/regress/sql/uuid.sql b/src/test/regress/sql/uuid.sql
index 75ee966ded0..110188361d1 100644
--- a/src/test/regress/sql/uuid.sql
+++ b/src/test/regress/sql/uuid.sql
@@ -10,6 +10,11 @@ CREATE TABLE guid2
guid_field UUID,
text_field TEXT DEFAULT(now())
);
+CREATE TABLE guid3
+(
+ id SERIAL,
+ guid_field UUID
+);
-- inserting invalid data tests
-- too long
@@ -97,6 +102,22 @@ 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());
+INSERT INTO guid1 (guid_field) VALUES (uuidv7(INTERVAL '1 day'));
+SELECT count(DISTINCT guid_field) FROM guid1;
+
+-- test sortability of v7
+INSERT INTO guid3 (guid_field) SELECT uuidv7() FROM generate_series(1, 10);
+SELECT array_agg(id ORDER BY guid_field) FROM guid3;
-- extract functions
@@ -104,12 +125,15 @@ 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 9562 test vector for v1
+SELECT uuid_extract_timestamp('017F22E2-79B0-7CC3-98C4-DC0C0C07398F') = 'Tuesday, February 22, 2022 2:22:22.00 PM GMT+05:00'; -- RFC 9562 test vector for v7
SELECT uuid_extract_timestamp(gen_random_uuid()); -- null
SELECT uuid_extract_timestamp('11111111-1111-1111-1111-111111111111'); -- null
-- clean up
-DROP TABLE guid1, guid2 CASCADE;
+DROP TABLE guid1, guid2, guid3 CASCADE;
--
2.43.5
