Add UUID version 7 generation function.

This commit introduces the uuidv7() SQL function, which generates UUID
version 7 as specified in RFC 9652. UUIDv7 combines a Unix timestamp
in milliseconds and random bits, offering both uniqueness and
sortability.

In our implementation, the 12-bit sub-millisecond timestamp fraction
is stored immediately after the timestamp, in the space referred to as
"rand_a" in the RFC. This ensures additional monotonicity within a
millisecond. The rand_a bits also function as a counter. We select a
sub-millisecond timestamp so that it monotonically increases for
generated UUIDs within the same backend, even when the system clock
goes backward or when generating UUIDs at very high
frequency. Therefore, the monotonicity of generated UUIDs is ensured
within the same backend.

This commit also expands the uuid_extract_timestamp() function to
support UUID version 7.

Additionally, an alias uuidv4() is added for the existing
gen_random_uuid() SQL function to maintain consistency.

Bump catalog version.

Author: Andrey Borodin
Reviewed-by: Sergey Prokhorenko, Przemysław Sztoch, Nikolay Samokhvalov
Reviewed-by: Peter Eisentraut, Jelte Fennema-Nio, Aleksander Alekseev
Reviewed-by: Masahiko Sawada, Lukas Fittl, Michael Paquier, Japin Li
Reviewed-by: Marcos Pegoraro, Junwang Zhao, Stepan Neretin
Reviewed-by: Daniel Vérité
Discussion: https://postgr.es/m/CAAhFRxitJv%3DyoGnXUgeLB_O%2BM7J2BJAmb5jqAT9gZ3bij3uLDA%40mail.gmail.com

diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index e0d33f12e1c50ff6d541d261947ffb0a2b14134b..3e6751d64cc70f6b5d5b44fd62ce8d6db1cede15 100644 (file)
--- 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 2c35252dc06936c05e9c50a2925fb1c424e91a1b..47370e581aef778c2878afdf7ceffc7daa0f0714 100644 (file)
--- 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 5284d23dcc4016e0686129bc130ab252e9163305..2e32592f5722407d6524e24811a5b5b88d419c96 100644 (file)
--- 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 calculate 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/catversion.h b/src/include/catalog/catversion.h
index 834b79c91bcd39935e569c4f5128e51ff30aaded..f815d15415f0e91a52e14fbce56d3a7e1babd375 100644 (file)
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -57,6 +57,6 @@
  */
 
 /*                         yyyymmddN */
-#define CATALOG_VERSION_NO 202412111
+#define CATALOG_VERSION_NO 202412112
 
 #endif

diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index ccf79761da577bf49f0d9627adddfed4101c7219..0f22c2172359209dd92a87495b77ce4c3a066dc7 100644 (file)
--- 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 8f4ef0d7a6a888092bc1668f0706b99a7272da54..798633ad51e3eb0ceb7e96d12438b76a5c9431c4 100644 (file)
--- 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 75ee966ded08e8c60ee1945a673528d91253edcf..110188361d1a1244b423187cc88c7042b3181950 100644 (file)
--- 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;