public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andrey M. Borodin <[email protected]>
To: Peter Eisentraut <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Daniel Gustafsson <[email protected]>
Cc: Matthias van de Meent <[email protected]>
Cc: Nikolay Samokhvalov <[email protected]>
Cc: Kyzer Davis (kydavis) <[email protected]>
Cc: Andres Freund <[email protected]>
Cc: Andrey Borodin <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: [email protected] <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: UUID v7
Date: Fri, 7 Jul 2023 17:06:19 +0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <CAAhFRxitJv=yoGnXUgeLB_O+M7J2BJAmb5jqAT9gZ3bij3uLDA@mail.gmail.com>
<[email protected]>
<[email protected]>
<PH0PR11MB5029DF5E0A0EAF8E3CC2C652BBA29@PH0PR11MB5029.namprd11.prod.outlook.com>
<CANNMO+L9pmgrEZPLhpRrjRcTnzMKgzURSeWFofuKfbHJT9euiA@mail.gmail.com>
<[email protected]>
<CAEze2WhHf8WURmcqu4ZxS7HFzVxPxK4jXUijnPu-easySjgZWg@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
> On 6 Jul 2023, at 21:38, Peter Eisentraut <[email protected]> wrote:
>
> I think it would be reasonable to review this patch now.
+1.
Also, I think we should discuss UUID v8. UUID version 8 provides an RFC-compatible format for experimental or vendor-specific use cases. Revision 1 of IETF draft contained interesting code for v8: almost similar to v7, but with fields for "node ID" and "rolling sequence number".
I think this is reasonable approach, thus I attach implementation of UUID v8 per [0]. But from my point of view this implementation has some flaws.
These two new fields "node ID" and "sequence" are there not for uniqueness, but rather for data locality.
But they are placed at the end, in bytes 14 and 15, after randomly generated numbers.
I think that "sequence" is there to help generate local ascending identifiers when the real time clock do not provide enough resolution. So "sequence" field must be placed after 6 bytes of time-generated identifier.
On a contrary "node ID" must differentiate identifiers generated on different nodes. So it makes sense to place "node ID" before timing. So identifiers generated on different nodes will tend to be in different ranges.
Although, section "6.4. Distributed UUID Generation" states that "node ID" is there to decrease the likelihood of a collision. So my intuition might be wrong here.
Do we want to provide this "vendor-specific" UUID with tweaks for databases? Or should we limit the scope with well defined UUID v7?
Best regards, Andrey Borodin.
[0] https://datatracker.ietf.org/doc/html/draft-ietf-uuidrev-rfc4122bis-01
Attachments:
[application/octet-stream] v2-0001-Implement-UUID-v7-and-v8-as-per-IETF-draft.patch (7.4K, ../[email protected]/2-v2-0001-Implement-UUID-v7-and-v8-as-per-IETF-draft.patch)
download | inline diff:
From 9f4c97a81aae3087581a024374a06e49156f2689 Mon Sep 17 00:00:00 2001
From: Andrey Borodin <[email protected]>
Date: Fri, 10 Feb 2023 15:38:40 -0800
Subject: [PATCH v2] Implement UUID v7 and v8 as per IETF draft
---
doc/src/sgml/func.sgml | 18 ++++-
src/backend/utils/adt/uuid.c | 87 ++++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 6 ++
src/test/regress/expected/opr_sanity.out | 2 +
src/test/regress/expected/uuid.out | 20 ++++++
src/test/regress/sql/uuid.sql | 12 ++++
6 files changed, 144 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 5a47ce4343..b8b5ee210a 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -13947,13 +13947,29 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
<primary>gen_random_uuid</primary>
</indexterm>
+ <indexterm>
+ <primary>gen_uuid_v7</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>gen_uuid_v8</primary>
+ </indexterm>
+
<para>
- <productname>PostgreSQL</productname> includes one function to generate a UUID:
+ <productname>PostgreSQL</productname> includes three functions to generate a UUID:
<synopsis>
<function>gen_random_uuid</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.
+<synopsis>
+<function>gen_uuid_v7</function> () <returnvalue>uuid</returnvalue>
+</synopsis>
+ This function returns a version 7 (time-ordered + random) UUID.
+<synopsis>
+<function>gen_uuid_v8</function> () <returnvalue>uuid</returnvalue>
+</synopsis>
+ This function returns a version 8 (time-ordered + random + node ID + rolling sequence number) UUID.
</para>
<para>
diff --git a/src/backend/utils/adt/uuid.c b/src/backend/utils/adt/uuid.c
index 4f7aa768fd..44deead6b1 100644
--- a/src/backend/utils/adt/uuid.c
+++ b/src/backend/utils/adt/uuid.c
@@ -13,6 +13,9 @@
#include "postgres.h"
+#include <sys/time.h>
+
+#include "access/xlog.h"
#include "common/hashfn.h"
#include "lib/hyperloglog.h"
#include "libpq/pqformat.h"
@@ -421,3 +424,87 @@ gen_random_uuid(PG_FUNCTION_ARGS)
PG_RETURN_UUID_P(uuid);
}
+
+Datum
+gen_uuid_v7(PG_FUNCTION_ARGS)
+{
+ pg_uuid_t *uuid = palloc(UUID_LEN);
+ struct timeval tp;
+ uint64_t tms;
+
+ gettimeofday(&tp, NULL);
+
+ tms = ((uint64_t)tp.tv_sec) * 1000;
+ tms += ((uint64_t)tp.tv_usec) / 1000;
+
+ tms = pg_hton64(tms<<16);
+
+ /* Fill in time part */
+ memcpy(&uuid->data[0], &tms, 6);
+
+ /* 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")));
+
+ /*
+ * Set magic numbers for a "version 7" (pseudorandom) UUID, see
+ * http://tools.ietf.org/html/rfc ???
+ * https://datatracker.ietf.org/doc/html/draft-peabody-dispatch-new-uuid-format#name-creating-a-uuidv7-value
+ */
+ /* 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);
+}
+
+static uint8_t sequence_counter;
+
+Datum
+gen_uuid_v8(PG_FUNCTION_ARGS)
+{
+ pg_uuid_t *uuid = palloc(UUID_LEN);
+ struct timeval tp;
+ uint32_t t;
+ uint16_t ut;
+ uint8_t node_id = GetSystemIdentifier();
+ uint8_t sequence = sequence_counter++;
+
+ /*
+ TODO: Consider supplying node ID and rolling sequence number
+ if (PG_NARGS() >= 1)
+ node_id = PG_GETARG_CHAR(0);
+ if (PG_NARGS() >= 2)
+ node_id = PG_GETARG_CHAR(1);
+ */
+
+ gettimeofday(&tp, NULL);
+ t = tp.tv_sec - 1577836800;
+ t = pg_hton32(t);
+ memcpy(&uuid->data[0], &t, 4);
+
+ /* 16 bit subsecond fraction (~15 microsecond resolution) */
+ ut = ((uint64_t)tp.tv_usec << 16) / 1000000;
+ memcpy(&uuid->data[4], &ut, 2);
+
+ /* 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")));
+
+ /*
+ * Set magic numbers for a "version 8" UID, see
+ * https://datatracker.ietf.org/doc/html/draft-ietf-uuidrev-rfc4122bis#name-creating-a-uuidv8-value
+ */
+ uuid->data[6] = (uuid->data[6] & 0x0f) | 0x80;
+ uuid->data[8] = (uuid->data[8] & 0x3f) | 0x80;
+
+ uuid->data[14] = node_id;
+ uuid->data[15] = sequence;
+
+ PG_RETURN_UUID_P(uuid);
+}
\ No newline at end of file
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 6996073989..0c82f9280f 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -9119,6 +9119,12 @@
{ oid => '3432', descr => 'generate random UUID',
proname => 'gen_random_uuid', proleakproof => 't', provolatile => 'v',
prorettype => 'uuid', proargtypes => '', prosrc => 'gen_random_uuid' },
+{ oid => '3813', descr => 'generate UUID version 7',
+ proname => 'gen_uuid_v7', proleakproof => 't', provolatile => 'v',
+ prorettype => 'uuid', proargtypes => '', prosrc => 'gen_uuid_v7' },
+{ oid => '3814', descr => 'generate UUID version 8',
+ proname => 'gen_uuid_v8', proleakproof => 't', provolatile => 'v',
+ prorettype => 'uuid', proargtypes => '', prosrc => 'gen_uuid_v8' },
# pg_lsn
{ oid => '3229', descr => 'I/O',
diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index a1bdf2c0b5..1fb9c654d3 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -857,6 +857,8 @@ sha384(bytea)
sha512(bytea)
gen_random_uuid()
starts_with(text,text)
+gen_uuid_v7()
+gen_uuid_v8()
macaddr8_eq(macaddr8,macaddr8)
macaddr8_lt(macaddr8,macaddr8)
macaddr8_le(macaddr8,macaddr8)
diff --git a/src/test/regress/expected/uuid.out b/src/test/regress/expected/uuid.out
index 8e7f21910d..516d4998a7 100644
--- a/src/test/regress/expected/uuid.out
+++ b/src/test/regress/expected/uuid.out
@@ -168,5 +168,25 @@ SELECT count(DISTINCT guid_field) FROM guid1;
2
(1 row)
+-- generation test for v7
+TRUNCATE guid1;
+INSERT INTO guid1 (guid_field) VALUES (gen_uuid_v7());
+INSERT INTO guid1 (guid_field) VALUES (gen_uuid_v7());
+SELECT count(DISTINCT guid_field) FROM guid1;
+ count
+-------
+ 2
+(1 row)
+
+-- generation test for v8
+TRUNCATE guid1;
+INSERT INTO guid1 (guid_field) VALUES (gen_uuid_v8());
+INSERT INTO guid1 (guid_field) VALUES (gen_uuid_v8());
+SELECT count(DISTINCT guid_field) FROM guid1;
+ count
+-------
+ 2
+(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..0d6784e70b 100644
--- a/src/test/regress/sql/uuid.sql
+++ b/src/test/regress/sql/uuid.sql
@@ -85,5 +85,17 @@ 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;
+-- generation test for v7
+TRUNCATE guid1;
+INSERT INTO guid1 (guid_field) VALUES (gen_uuid_v7());
+INSERT INTO guid1 (guid_field) VALUES (gen_uuid_v7());
+SELECT count(DISTINCT guid_field) FROM guid1;
+
+-- generation test for v8
+TRUNCATE guid1;
+INSERT INTO guid1 (guid_field) VALUES (gen_uuid_v8());
+INSERT INTO guid1 (guid_field) VALUES (gen_uuid_v8());
+SELECT count(DISTINCT guid_field) FROM guid1;
+
-- clean up
DROP TABLE guid1, guid2 CASCADE;
--
2.37.1 (Apple Git-137.1)
view thread (14+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: UUID v7
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox