v38-0001-Add-UUID-version-7-generation-function.patch

From c589d9d5ea6ff5d814d2ad4813b8d7cc933bd0e2 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 v38] 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 uuid_v7() 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 uuid_v4() is added for the existing
gen_random_uuid() function to maintain consistency.

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                   |  21 +-
 src/backend/utils/adt/uuid.c             | 237 ++++++++++++++++++++++-
 src/include/catalog/pg_proc.dat          |  11 +-
 src/test/regress/expected/opr_sanity.out |   3 +
 src/test/regress/expected/uuid.out       |  56 +++++-
 src/test/regress/sql/uuid.sql            |  28 ++-
 7 files changed, 338 insertions(+), 20 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 13ccbe7d78c..ab1d42ae759 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14213,6 +14213,14 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
    <primary>gen_random_uuid</primary>
   </indexterm>
 
+  <indexterm>
+   <primary>uuid_v4</primary>
+  </indexterm>
+
+  <indexterm>
+   <primary>uuid_v7</primary>
+  </indexterm>
+
   <indexterm>
    <primary>uuid_extract_timestamp</primary>
   </indexterm>
@@ -14222,12 +14230,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>uuidv_4</function> () <returnvalue>uuid</returnvalue>
+</synopsis>
+   These functions return a version 4 (random) UUID.
+<synopsis>
+<function>uuidv_7</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 millisecond
+    precision + sub-millisecond + random).
   </para>
 
   <para>
@@ -14251,7 +14264,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..4c907fa80aa 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,26 @@
 #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)
+
+/*
+ * In UUID version 7, we use 12 bits in "rand_a" to store 1/4096
+ * fractions of sub-millisecond. On systems that have only 10 bits of sub-ms
+ * precision we still use 1/4096 parts of a millisecond, but fill lower 2 bits
+ * with random numbers. SUBMS_MINIMAL_STEP is the minimum amount of
+ * nanoseconds that guarantees step of UUID increased 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 +59,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 +425,23 @@ 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 +453,182 @@ 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() where available, instead use
+	 * clock_gettime() with CLOCK_REALTIME in order to get a high-precision
+	 * (nanoseconds) real timestamp.
+	 *
+	 * Note that 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)".
+ * This method utilizes 12 bits from the "rand_a" bits to store a 1/4096
+ * (or 2^12) fraction of sub-millisecond precision.
+ */
+static pg_attribute_always_inline pg_uuid_t *
+generate_uuid_v7(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 defined(__darwin__) || defined(_MSC_VER)
+
+	/*
+	 * On MacOS real time is truncated to microseconds. Thus, 2 least
+	 * significant are dependent on other time-specific bits, thus 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
+uuid_v7(PG_FUNCTION_ARGS)
+{
+	pg_uuid_t  *uuid = generate_uuid_v7(get_real_time_ns_ascending());
+
+	PG_RETURN_UUID_P(uuid);
+}
+
+/*
+ * Similar to uuid_v7() but with the timestamp adjusted by the given interval.
+ */
+Datum
+uuid_v7_interval(PG_FUNCTION_ARGS)
+{
+	Interval   *span = 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 make correct
+	 * calculating the time shift, we convert the UNIX epoch to TimestampTz
+	 * and use timestamptz_pl_interval(). Since this calculation is done with
+	 * microsecond precision, we carry back the nanoseconds.
+	 */
+
+	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(span)));
+
+	/*
+	 * Convert a TimestampTz value back to an UNIX epoch and carry 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 UUID */
+	uuid = generate_uuid_v7(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 +638,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 +657,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 +684,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 +692,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 cbbe8acd382..9210edfec81 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -9342,11 +9342,20 @@
 { 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 => 'uuid_v4', proleakproof => 't', provolatile => 'v',
+  prorettype => 'uuid', proargtypes => '', prosrc => 'gen_random_uuid' },
+{ oid => '9896', descr => 'generate UUID version 7',
+  proname => 'uuid_v7', proleakproof => 't', provolatile => 'v',
+  prorettype => 'uuid', proargtypes => '', prosrc => 'uuid_v7' },
+{ oid => '9897', descr => 'generate UUID version 7 with a timestamp shifted on specific interval',
+  proname => 'uuid_v7', proleakproof => 't', provolatile => 'v',
+  prorettype => 'uuid', proargtypes => 'interval', prosrc => 'uuid_v7_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/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index 34a32bd11d2..2341907a0b0 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -878,6 +878,9 @@ crc32(bytea)
 crc32c(bytea)
 bytea_larger(bytea,bytea)
 bytea_smaller(bytea,bytea)
+uuid_v4()
+uuid_v7()
+uuid_v7(interval)
 -- 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 8f4ef0d7a6a..b24e85d4475 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 uuid_v4() alias
+TRUNCATE guid1;
+INSERT INTO guid1 (guid_field) VALUES (uuid_v4());
+INSERT INTO guid1 (guid_field) VALUES (uuid_v4());
+SELECT count(DISTINCT guid_field) FROM guid1;
+ count 
+-------
+     2
+(1 row)
+
+-- generation test for v7
+TRUNCATE guid1;
+INSERT INTO guid1 (guid_field) VALUES (uuid_v7());
+INSERT INTO guid1 (guid_field) VALUES (uuid_v7());
+INSERT INTO guid1 (guid_field) VALUES (uuid_v7(INTERVAL '1 day'));
+SELECT count(DISTINCT guid_field) FROM guid1;
+ count 
+-------
+     3
+(1 row)
+
+-- test sortability of v7
+INSERT INTO guid3 (guid_field) SELECT uuid_v7() 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(uuid_v4()); --4
+ uuid_extract_version 
+----------------------
+                    4
+(1 row)
+
+SELECT uuid_extract_version(uuid_v7()); --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..03a5a08be4d 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 uuid_v4() alias
+TRUNCATE guid1;
+INSERT INTO guid1 (guid_field) VALUES (uuid_v4());
+INSERT INTO guid1 (guid_field) VALUES (uuid_v4());
+SELECT count(DISTINCT guid_field) FROM guid1;
+
+-- generation test for v7
+TRUNCATE guid1;
+INSERT INTO guid1 (guid_field) VALUES (uuid_v7());
+INSERT INTO guid1 (guid_field) VALUES (uuid_v7());
+INSERT INTO guid1 (guid_field) VALUES (uuid_v7(INTERVAL '1 day'));
+SELECT count(DISTINCT guid_field) FROM guid1;
+
+-- test sortability of v7
+INSERT INTO guid3 (guid_field) SELECT uuid_v7() 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(uuid_v4()); --4
+SELECT uuid_extract_version(uuid_v7()); --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