public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v35 7/7] pg_ls_*dir to return all the metadata from pg_stat_file..
14+ messages / 8 participants
[nested] [flat]
* [PATCH v35 7/7] pg_ls_*dir to return all the metadata from pg_stat_file..
@ 2020-03-10 02:56 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 14+ messages in thread
From: Justin Pryzby @ 2020-03-10 02:56 UTC (permalink / raw)
..but it doesn't seem worth factoring out the common bits, since stat_file
doesn't return a name, so all the field numbers are off by one.
NOTE, the atime is now shown where the mtime used to be.
Need catversion bump
---
doc/src/sgml/func.sgml | 34 ++++++---
src/backend/utils/adt/genfile.c | 72 +++++++++-----------
src/include/catalog/pg_proc.dat | 42 ++++++------
src/test/regress/expected/misc_functions.out | 40 +++++------
src/test/regress/expected/tablespace.out | 8 +--
5 files changed, 102 insertions(+), 94 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index e7c63640d7e..a3004148a52 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -25998,7 +25998,10 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
<returnvalue>setof record</returnvalue>
( <parameter>filename</parameter> <type>text</type>,
<parameter>size</parameter> <type>bigint</type>,
+ <parameter>access</parameter> <type>timestamp with time zone</type>,
<parameter>modification</parameter> <type>timestamp with time zone</type>,
+ <parameter>change</parameter> <type>timestamp with time zone</type>,
+ <parameter>creation</parameter> <type>timestamp with time zone</type>,
<parameter>isdir</parameter> <type>boolean</type> )
</para>
<para>
@@ -27417,13 +27420,16 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
<returnvalue>setof record</returnvalue>
( <parameter>name</parameter> <type>text</type>,
<parameter>size</parameter> <type>bigint</type>,
+ <parameter>access</parameter> <type>timestamp with time zone</type>,
<parameter>modification</parameter> <type>timestamp with time zone</type>,
+ <parameter>change</parameter> <type>timestamp with time zone</type>,
+ <parameter>creation</parameter> <type>timestamp with time zone</type>,
<parameter>isdir</parameter> <type>boolean</type> )
</para>
<para>
For each file in the server's log directory,
- return the file's name, size, last modification time (mtime), and a boolean
- indicating if the file is a directory.
+ return the file's name, along with the metadata columns returned by
+ <function>pg_stat_file</function>.
Filenames beginning with a dot and special file types are excluded.
</para>
<para>
@@ -27442,13 +27448,16 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
<returnvalue>setof record</returnvalue>
( <parameter>name</parameter> <type>text</type>,
<parameter>size</parameter> <type>bigint</type>,
+ <parameter>access</parameter> <type>timestamp with time zone</type>,
<parameter>modification</parameter> <type>timestamp with time zone</type>,
+ <parameter>change</parameter> <type>timestamp with time zone</type>,
+ <parameter>creation</parameter> <type>timestamp with time zone</type>,
<parameter>isdir</parameter> <type>boolean</type> )
</para>
<para>
For each file in the server's write-ahead log (WAL) directory, list the
- file's name, size, last modification time (mtime), and a boolean
- indicating if the file is a directory.
+ file's name along with the metadata columns returned by
+ <function>pg_stat_file</function>.
Filenames beginning with a dot and special files types are excluded.
</para>
<para>
@@ -27540,14 +27549,17 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
<returnvalue>setof record</returnvalue>
( <parameter>name</parameter> <type>text</type>,
<parameter>size</parameter> <type>bigint</type>,
+ <parameter>access</parameter> <type>timestamp with time zone</type>,
<parameter>modification</parameter> <type>timestamp with time zone</type>,
+ <parameter>change</parameter> <type>timestamp with time zone</type>,
+ <parameter>creation</parameter> <type>timestamp with time zone</type>,
<parameter>isdir</parameter> <type>boolean</type> )
</para>
<para>
For each file in the server's WAL archive status directory
- (<filename>pg_wal/archive_status</filename>), list the file's
- name, size, last modification time (mtime), and a boolean indicating if
- the file is a directory.
+ (<filename>pg_wal/archive_status</filename>), list the file's name
+ along with the metadata columns returned by
+ <function>pg_stat_file</function>.
Filenames beginning with a dot and special file types are excluded.
</para>
<para>
@@ -27567,13 +27579,17 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
<returnvalue>setof record</returnvalue>
( <parameter>name</parameter> <type>text</type>,
<parameter>size</parameter> <type>bigint</type>,
+ <parameter>access</parameter> <type>timestamp with time zone</type>,
<parameter>modification</parameter> <type>timestamp with time zone</type>,
+ <parameter>change</parameter> <type>timestamp with time zone</type>,
+ <parameter>creation</parameter> <type>timestamp with time zone</type>,
<parameter>isdir</parameter> <type>boolean</type> )
</para>
<para>
For each file in the temporary directory within the given
- <parameter>tablespace</parameter>, return the file's name, size, last
- modification time (mtime) and a boolean indicating if the file is a directory.
+ <parameter>tablespace</parameter>, list the file's name
+ along with the metadata columns returned by
+ <function>pg_stat_file</function>.
Directories are used for temporary files shared by parallel processes.
If <parameter>tablespace</parameter> is not provided, the
<literal>pg_default</literal> tablespace is examined.
diff --git a/src/backend/utils/adt/genfile.c b/src/backend/utils/adt/genfile.c
index ca5223be2ee..c289cf1ad85 100644
--- a/src/backend/utils/adt/genfile.c
+++ b/src/backend/utils/adt/genfile.c
@@ -37,6 +37,8 @@
#include "utils/syscache.h"
#include "utils/timestamp.h"
+static void values_from_stat(struct stat *fst, const char *path, Datum *values,
+ bool *nulls);
static Datum pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, int flags);
#define LS_DIR_ISDIR (1<<0) /* Show column: isdir */
@@ -394,6 +396,28 @@ pg_read_binary_file_all(PG_FUNCTION_ARGS)
return pg_read_binary_file(fcinfo);
}
+/*
+ * Populate values and nulls from fst and path.
+ * Used for pg_stat_file() and pg_ls_dir_files()
+ * nulls is assumed to have been zerod.
+ */
+static void
+values_from_stat(struct stat *fst, const char *path, Datum *values, bool *nulls)
+{
+ values[0] = Int64GetDatum((int64) fst->st_size);
+ values[1] = TimestampTzGetDatum(time_t_to_timestamptz(fst->st_atime));
+ values[2] = TimestampTzGetDatum(time_t_to_timestamptz(fst->st_mtime));
+ /* Unix has file status change time, while Win32 has creation time */
+#if !defined(WIN32) && !defined(__CYGWIN__)
+ values[3] = TimestampTzGetDatum(time_t_to_timestamptz(fst->st_ctime));
+ nulls[4] = true;
+#else
+ nulls[3] = true;
+ values[4] = TimestampTzGetDatum(time_t_to_timestamptz(fst->st_ctime));
+#endif
+ values[5] = BoolGetDatum(S_ISDIR(fst->st_mode));
+}
+
/*
* stat a file
*/
@@ -404,7 +428,7 @@ pg_stat_file(PG_FUNCTION_ARGS)
char *filename;
struct stat fst;
Datum values[6];
- bool isnull[6];
+ bool nulls[6];
HeapTuple tuple;
TupleDesc tupdesc;
bool missing_ok = false;
@@ -444,27 +468,9 @@ pg_stat_file(PG_FUNCTION_ARGS)
"isdir", BOOLOID, -1, 0);
BlessTupleDesc(tupdesc);
- memset(isnull, false, sizeof(isnull));
-
- values[0] = Int64GetDatum((int64) fst.st_size);
- values[1] = TimestampTzGetDatum(time_t_to_timestamptz(fst.st_atime));
- values[2] = TimestampTzGetDatum(time_t_to_timestamptz(fst.st_mtime));
- /* Unix has file status change time, while Win32 has creation time */
-#if !defined(WIN32) && !defined(__CYGWIN__)
- values[3] = TimestampTzGetDatum(time_t_to_timestamptz(fst.st_ctime));
- isnull[4] = true;
-#else
- isnull[3] = true;
- values[4] = TimestampTzGetDatum(time_t_to_timestamptz(fst.st_ctime));
-#endif
- values[5] = BoolGetDatum(S_ISDIR(fst.st_mode));
-#ifdef WIN32
- /* Links should have isdir=false */
- if (pgwin32_is_junction(filename))
- values[5] = BoolGetDatum(false);
-#endif
-
- tuple = heap_form_tuple(tupdesc, values, isnull);
+ memset(nulls, false, sizeof(nulls));
+ values_from_stat(&fst, filename, values, nulls);
+ tuple = heap_form_tuple(tupdesc, values, nulls);
pfree(filename);
@@ -571,8 +577,8 @@ pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, int flags)
while ((de = ReadDir(dirdesc, dir)) != NULL)
{
- Datum values[4];
- bool nulls[4];
+ Datum values[7];
+ bool nulls[7];
char path[MAXPGPATH * 2];
struct stat attrib;
@@ -611,24 +617,10 @@ pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, int flags)
continue;
}
+ memset(nulls, false, sizeof(nulls));
values[0] = CStringGetTextDatum(de->d_name);
if (flags & LS_DIR_METADATA)
- {
- values[1] = Int64GetDatum((int64) attrib.st_size);
- values[2] = TimestampTzGetDatum(time_t_to_timestamptz(attrib.st_mtime));
- if (flags & LS_DIR_ISDIR)
- {
-#ifdef WIN32
- /* Links should have isdir=false */
- if (pgwin32_is_junction(path))
- values[3] = BoolGetDatum(false);
- else
-#endif
- values[3] = BoolGetDatum(S_ISDIR(attrib.st_mode));
- }
- }
-
- memset(nulls, 0, sizeof(nulls));
+ values_from_stat(&attrib, path, 1+values, 1+nulls);
tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index a68558cfed0..c7e96685e1b 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -11723,63 +11723,63 @@
{ oid => '3353', descr => 'list files in the log directory',
proname => 'pg_ls_logdir', procost => '10', prorows => '20', proretset => 't',
provolatile => 'v', prorettype => 'record', proargtypes => '',
- proallargtypes => '{text,int8,timestamptz,bool}', proargmodes => '{o,o,o,o}',
- proargnames => '{name,size,modification,isdir}', prosrc => 'pg_ls_logdir' },
+ proallargtypes => '{text,int8,timestamptz,timestamptz,timestamptz,timestamptz,bool}', proargmodes => '{o,o,o,o,o,o,o}',
+ proargnames => '{name,size,access,modification,change,creation,isdir}', prosrc => 'pg_ls_logdir' },
{ oid => '3354', descr => 'list of files in the WAL directory',
proname => 'pg_ls_waldir', procost => '10', prorows => '20', proretset => 't',
provolatile => 'v', prorettype => 'record', proargtypes => '',
- proallargtypes => '{text,int8,timestamptz,bool}', proargmodes => '{o,o,o,o}',
- proargnames => '{name,size,modification,isdir}', prosrc => 'pg_ls_waldir' },
+ proallargtypes => '{text,int8,timestamptz,timestamptz,timestamptz,timestamptz,bool}', proargmodes => '{o,o,o,o,o,o,o}',
+ proargnames => '{name,size,access,modification,change,creation,isdir}', prosrc => 'pg_ls_waldir' },
{ oid => '5031', descr => 'list of files in the archive_status directory',
proname => 'pg_ls_archive_statusdir', procost => '10', prorows => '20',
proretset => 't', provolatile => 'v', prorettype => 'record',
- proargtypes => '', proallargtypes => '{text,int8,timestamptz,bool}',
- proargmodes => '{o,o,o,o}', proargnames => '{name,size,modification,isdir}',
+ proargtypes => '', proallargtypes => '{text,int8,timestamptz,timestamptz,timestamptz,timestamptz,bool}',
+ proargmodes => '{o,o,o,o,o,o,o}', proargnames => '{name,size,access,modification,change,creation,isdir}',
prosrc => 'pg_ls_archive_statusdir' },
{ oid => '5029', descr => 'list files in the pgsql_tmp directory',
proname => 'pg_ls_tmpdir', procost => '10', prorows => '20', proretset => 't',
provolatile => 'v', prorettype => 'record', proargtypes => '',
- proallargtypes => '{text,int8,timestamptz,bool}', proargmodes => '{o,o,o,o}',
- proargnames => '{name,size,modification,isdir}', prosrc => 'pg_ls_tmpdir_noargs' },
+ proallargtypes => '{text,int8,timestamptz,timestamptz,timestamptz,timestamptz,bool}', proargmodes => '{o,o,o,o,o,o,o}',
+ proargnames => '{name,size,access,modification,change,creation,isdir}', prosrc => 'pg_ls_tmpdir_noargs' },
{ oid => '5030', descr => 'list files in the pgsql_tmp directory',
proname => 'pg_ls_tmpdir', procost => '10', prorows => '20', proretset => 't',
provolatile => 'v', prorettype => 'record', proargtypes => 'oid',
- proallargtypes => '{oid,text,int8,timestamptz,bool}', proargmodes => '{i,o,o,o,o}',
- proargnames => '{tablespace,name,size,modification,isdir}',
+ proallargtypes => '{oid,text,int8,timestamptz,timestamptz,timestamptz,timestamptz,bool}', proargmodes => '{i,o,o,o,o,o,o,o}',
+ proargnames => '{tablespace,name,size,access,modification,change,creation,isdir}',
prosrc => 'pg_ls_tmpdir_1arg' },
{ oid => '9858',
descr => 'list of files in the pg_logical/snapshots directory',
proname => 'pg_ls_logicalsnapdir', procost => '10', prorows => '20',
proretset => 't', provolatile => 'v', prorettype => 'record',
- proargtypes => '', proallargtypes => '{text,int8,timestamptz,bool}',
- proargmodes => '{o,o,o,o}', proargnames => '{name,size,modification,isdir}',
+ proargtypes => '', proallargtypes => '{text,int8,timestamptz,timestamptz,timestamptz,timestamptz,bool}',
+ proargmodes => '{o,o,o,o,o,o,o}', proargnames => '{name,size,access,modification,change,creation,isdir}',
prosrc => 'pg_ls_logicalsnapdir' },
{ oid => '9859',
descr => 'list of files in the pg_logical/mappings directory',
proname => 'pg_ls_logicalmapdir', procost => '10', prorows => '20',
proretset => 't', provolatile => 'v', prorettype => 'record',
- proargtypes => '', proallargtypes => '{text,int8,timestamptz,bool}',
- proargmodes => '{o,o,o,o}', proargnames => '{name,size,modification,isdir}',
+ proargtypes => '', proallargtypes => '{text,int8,timestamptz,timestamptz,timestamptz,timestamptz,bool}',
+ proargmodes => '{o,o,o,o,o,o,o}', proargnames => '{name,size,access,modification,change,creation,isdir}',
prosrc => 'pg_ls_logicalmapdir' },
{ oid => '9860',
descr => 'list of files in the pg_replslot/slot_name directory',
proname => 'pg_ls_replslotdir', procost => '10', prorows => '20',
proretset => 't', provolatile => 'v', prorettype => 'record',
- proargtypes => 'text', proallargtypes => '{text,text,int8,timestamptz,bool}',
- proargmodes => '{i,o,o,o,o}',
- proargnames => '{slot_name,name,size,modification,isdir}',
+ proargtypes => 'text', proallargtypes => '{text,text,int8,timestamptz,timestamptz,timestamptz,timestamptz,bool}',
+ proargmodes => '{i,o,o,o,o,o,o,o}',
+ proargnames => '{slot_name,name,size,access,modification,change,creation,isdir}',
prosrc => 'pg_ls_replslotdir' },
{ oid => '8450', descr => 'list directory with metadata',
proname => 'pg_ls_dir_metadata', procost => '10', prorows => '20', proretset => 't',
provolatile => 'v', prorettype => 'record', proargtypes => 'text bool bool',
- proallargtypes => '{text,bool,bool,text,int8,timestamptz,bool}', proargmodes => '{i,i,i,o,o,o,o}',
- proargnames => '{dirname,missing_ok,include_dot_dirs,filename,size,modification,isdir}',
+ proallargtypes => '{text,bool,bool,text,int8,timestamptz,timestamptz,timestamptz,timestamptz,bool}', proargmodes => '{i,i,i,o,o,o,o,o,o,o}',
+ proargnames => '{dirname,missing_ok,include_dot_dirs,filename,size,access,modification,change,creation,isdir}',
prosrc => 'pg_ls_dir_metadata' },
{ oid => '8451', descr => 'list directory with metadata',
proname => 'pg_ls_dir_metadata', procost => '10', prorows => '20', proretset => 't',
provolatile => 'v', prorettype => 'record', proargtypes => 'text',
- proallargtypes => '{text,text,int8,timestamptz,bool}', proargmodes => '{i,o,o,o,o}',
- proargnames => '{dirname,filename,size,modification,isdir}',
+ proallargtypes => '{text,text,int8,timestamptz,timestamptz,timestamptz,timestamptz,bool}', proargmodes => '{i,o,o,o,o,o,o,o}',
+ proargnames => '{dirname,filename,size,access,modification,change,creation,isdir}',
prosrc => 'pg_ls_dir_metadata_1arg' },
# hash partitioning constraint function
diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out
index 94b5eac47ea..afb1744bee7 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -349,8 +349,8 @@ select count(*) > 0 as ok from (select pg_ls_waldir()) ss;
-- Test not-run-to-completion cases.
select * from pg_ls_waldir() limit 0;
- name | size | modification | isdir
-------+------+--------------+-------
+ name | size | access | modification | change | creation | isdir
+------+------+--------+--------------+--------+----------+-------
(0 rows)
select count(*) > 0 as ok from (select * from pg_ls_waldir() limit 1) ss;
@@ -436,38 +436,38 @@ select pg_ls_dir('does not exist'); -- fails with missingok=false
ERROR: could not open directory "does not exist": No such file or directory
-- Check that expected columns are present
select * from pg_ls_archive_statusdir() limit 0;
- name | size | modification | isdir
-------+------+--------------+-------
+ name | size | access | modification | change | creation | isdir
+------+------+--------+--------------+--------+----------+-------
(0 rows)
select * from pg_ls_logdir() limit 0;
- name | size | modification | isdir
-------+------+--------------+-------
+ name | size | access | modification | change | creation | isdir
+------+------+--------+--------------+--------+----------+-------
(0 rows)
select * from pg_ls_logicalmapdir() limit 0;
- name | size | modification | isdir
-------+------+--------------+-------
+ name | size | access | modification | change | creation | isdir
+------+------+--------+--------------+--------+----------+-------
(0 rows)
select * from pg_ls_logicalsnapdir() limit 0;
- name | size | modification | isdir
-------+------+--------------+-------
+ name | size | access | modification | change | creation | isdir
+------+------+--------+--------------+--------+----------+-------
(0 rows)
select * from pg_ls_replslotdir('') limit 0;
- name | size | modification | isdir
-------+------+--------------+-------
+ name | size | access | modification | change | creation | isdir
+------+------+--------+--------------+--------+----------+-------
(0 rows)
select * from pg_ls_tmpdir() limit 0;
- name | size | modification | isdir
-------+------+--------------+-------
+ name | size | access | modification | change | creation | isdir
+------+------+--------+--------------+--------+----------+-------
(0 rows)
select * from pg_ls_waldir() limit 0;
- name | size | modification | isdir
-------+------+--------------+-------
+ name | size | access | modification | change | creation | isdir
+------+------+--------+--------------+--------+----------+-------
(0 rows)
select * from pg_stat_file('.') limit 0;
@@ -478,8 +478,8 @@ select * from pg_stat_file('.') limit 0;
-- This tests the missing_ok parameter, which causes pg_ls_tmpdir to succeed even if the tmpdir doesn't exist yet
-- The name='' condition is never true, so the function runs to completion but returns zero rows.
select * from pg_ls_tmpdir() where name='Does not exist';
- name | size | modification | isdir
-------+------+--------------+-------
+ name | size | access | modification | change | creation | isdir
+------+------+--------+--------------+--------+----------+-------
(0 rows)
select filename, isdir from pg_ls_dir_metadata('.') where filename='.';
@@ -495,8 +495,8 @@ select filename, isdir from pg_ls_dir_metadata('.', false, false) where filename
-- Check that expected columns are present
select * from pg_ls_dir_metadata('.') limit 0;
- filename | size | modification | isdir
-----------+------+--------------+-------
+ filename | size | access | modification | change | creation | isdir
+----------+------+--------+--------------+--------+----------+-------
(0 rows)
--
diff --git a/src/test/regress/expected/tablespace.out b/src/test/regress/expected/tablespace.out
index 53662fb0efd..ef64e7bfa8b 100644
--- a/src/test/regress/expected/tablespace.out
+++ b/src/test/regress/expected/tablespace.out
@@ -37,15 +37,15 @@ SELECT regexp_replace(pg_tablespace_location(oid), '(pg_tblspc)/(\d+)', '\1/NNN'
-- The name='' condition is never true, so the function runs to completion but returns zero rows.
-- 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';
- name | size | modification | isdir
-------+------+--------------+-------
+ name | size | access | modification | change | creation | isdir
+------+------+--------+--------------+--------+----------+-------
(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
-------+------+--------------+-------
+ name | size | access | modification | change | creation | isdir
+------+------+--------+--------------+--------+----------+-------
(0 rows)
-- try setting and resetting some properties for the new tablespace
--
2.17.1
--olLTNZSltDMg5Vbm--
^ permalink raw reply [nested|flat] 14+ messages in thread
* RE: UUID v7
@ 2023-02-14 14:13 Kyzer Davis (kydavis) <[email protected]>
2023-06-22 18:30 ` Re: UUID v7 Nikolay Samokhvalov <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Kyzer Davis (kydavis) @ 2023-02-14 14:13 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; Andres Freund <[email protected]>; Andrey Borodin <[email protected]>; +Cc: pgsql-hackers; [email protected] <[email protected]>; [email protected] <[email protected]>; Nikolay Samokhvalov <[email protected]>
Hello Group,
I am happy to see others interested in the improvements provided by UUIDv7!
I caught up on the thread and you all are correct.
Work has moved on GitHub from uuid6/uuid6-ietf-draft to
ietf-wg-uuidrev/rfc4122bis
- Draft 00 merged RFC4122 with Draft 04 and fixed as many problems as possible
with RFC4122.
- Draft 01 continued to iterate on RFC4122 problems:
https://author-tools.ietf.org/iddiff?url2=draft-ietf-uuidrev-rfc4122bis-01
- Draft 02 items being changed are summarized in the latest PR for review in
the upcoming interim meeting (Feb 16th):
https://github.com/ietf-wg-uuidrev/rfc4122bis/pull/60
Note: Draft 02 should be published by the end of the week and long term we
have one more meeting at IETF 116 to iron out the replacement of RFC4122,
perform last call and submit to the IESG for official review and consideration
for replacement of RFC4122 (actual timeline for that varies based on what IESG
wants me to fix.)
That all being said:
The point is 99% of the work since adoption by the IETF has been ironing out
RFC4122's problems and nothing major related to UUIDv6/7/8 which are all in a
very good state.
If anybody has any feedback found during draft reviewing or prototyping;
please either email [email protected] or drop an issue on the tracker:
https://github.com/ietf-wg-uuidrev/rfc4122bis/issues
Lastly, I have added the C/SQL implementation to the prototypes page below:
https://github.com/uuid6/prototypes
Thanks!
-----Original Message-----
From: Peter Eisentraut <[email protected]>
Sent: Saturday, February 11, 2023 10:51 AM
To: Andres Freund <[email protected]>; Andrey Borodin <[email protected]>
Cc: pgsql-hackers <[email protected]>; [email protected];
[email protected]; Kyzer Davis (kydavis) <[email protected]>; Nikolay
Samokhvalov <[email protected]>
Subject: Re: UUID v7
On 11.02.23 02:14, Andres Freund wrote:
> On 2023-02-10 15:57:50 -0800, Andrey Borodin wrote:
>> As you may know there's a new version of UUID being standardized [0].
>> These new algorithms of UUID generation are very promising for
>> database performance.
>
> I agree it's very useful to have.
>
>
>> [0]
>> https://datatracker.ietf.org/doc/html/draft-peabody-dispatch-new-uuid
>> -format-04
>
> That looks to not be the current version anymore, it's superseded by:
> https://datatracker.ietf.org/doc/html/draft-ietf-uuidrev-rfc4122bis
Yes, this means that the draft that an individual had uploaded has now been
taken on by a working group for formal review. If there is a prototype
implementation, this is a good time to provide feedback. But it's too early
to ship a production version.
Attachments:
[application/pkcs7-signature] smime.p7s (5.3K, ../../PH0PR11MB5029DF5E0A0EAF8E3CC2C652BBA29@PH0PR11MB5029.namprd11.prod.outlook.com/2-smime.p7s)
download
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: UUID v7
2023-02-14 14:13 RE: UUID v7 Kyzer Davis (kydavis) <[email protected]>
@ 2023-06-22 18:30 ` Nikolay Samokhvalov <[email protected]>
2023-07-06 12:24 ` Re: UUID v7 Daniel Gustafsson <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Nikolay Samokhvalov @ 2023-06-22 18:30 UTC (permalink / raw)
To: Kyzer Davis (kydavis) <[email protected]>; +Cc: Peter Eisentraut <[email protected]>; Andres Freund <[email protected]>; Andrey Borodin <[email protected]>; pgsql-hackers; [email protected] <[email protected]>; [email protected] <[email protected]>
On Tue, Feb 14, 2023 at 6:13 AM Kyzer Davis (kydavis) <[email protected]> wrote:
> I am happy to see others interested in the improvements provided by UUIDv7!
Thank you for providing the details!
Some small updates as I see them:
- there is revision 7 now in https://github.com/ietf-wg-uuidrev/rfc4122bis
- noticing that there is no commitfest record, I created one:
https://commitfest.postgresql.org/43/4388/
- recent post by Ants Aasma, Cybertec about the downsides of
traditional UUID raised a big discussion today on HN:
https://news.ycombinator.com/item?id=36429986
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: UUID v7
2023-02-14 14:13 RE: UUID v7 Kyzer Davis (kydavis) <[email protected]>
2023-06-22 18:30 ` Re: UUID v7 Nikolay Samokhvalov <[email protected]>
@ 2023-07-06 12:24 ` Daniel Gustafsson <[email protected]>
2023-07-06 13:29 ` Re: UUID v7 Matthias van de Meent <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Daniel Gustafsson @ 2023-07-06 12:24 UTC (permalink / raw)
To: Nikolay Samokhvalov <[email protected]>; +Cc: Kyzer Davis (kydavis) <[email protected]>; Peter Eisentraut <[email protected]>; Andres Freund <[email protected]>; Andrey Borodin <[email protected]>; pgsql-hackers; [email protected] <[email protected]>; [email protected] <[email protected]>
> On 22 Jun 2023, at 20:30, Nikolay Samokhvalov <[email protected]> wrote:
> Some small updates as I see them:
> - there is revision 7 now in https://github.com/ietf-wg-uuidrev/rfc4122bis
> - noticing that there is no commitfest record, I created one:
I will actually go ahead and close this entry in the current CF, not because we
don't want the feature but because it's unlikely that it will go in now given
that standardization is still underway. Comitting anything right now seems
premature, we might as well wait for standardization given that we have lots of
time before the v17 freeze.
--
Daniel Gustafsson
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: UUID v7
2023-02-14 14:13 RE: UUID v7 Kyzer Davis (kydavis) <[email protected]>
2023-06-22 18:30 ` Re: UUID v7 Nikolay Samokhvalov <[email protected]>
2023-07-06 12:24 ` Re: UUID v7 Daniel Gustafsson <[email protected]>
@ 2023-07-06 13:29 ` Matthias van de Meent <[email protected]>
2023-07-06 13:43 ` Re: UUID v7 Daniel Gustafsson <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Matthias van de Meent @ 2023-07-06 13:29 UTC (permalink / raw)
To: Daniel Gustafsson <[email protected]>; +Cc: Nikolay Samokhvalov <[email protected]>; Kyzer Davis (kydavis) <[email protected]>; Peter Eisentraut <[email protected]>; Andres Freund <[email protected]>; Andrey Borodin <[email protected]>; pgsql-hackers; [email protected] <[email protected]>; [email protected] <[email protected]>
On Thu, 6 Jul 2023 at 14:24, Daniel Gustafsson <[email protected]> wrote:
>
> > On 22 Jun 2023, at 20:30, Nikolay Samokhvalov <[email protected]> wrote:
>
> > Some small updates as I see them:
> > - there is revision 7 now in https://github.com/ietf-wg-uuidrev/rfc4122bis
> > - noticing that there is no commitfest record, I created one:
>
> I will actually go ahead and close this entry in the current CF, not because we
> don't want the feature but because it's unlikely that it will go in now given
> that standardization is still underway. Comitting anything right now seems
> premature, we might as well wait for standardization given that we have lots of
> time before the v17 freeze.
I'd like to note that this draft has recently had its last call
period, and has been proposed for publishing early last month. I don't
know how long this publishing process usually takes, but it seems like
the WG considers the text final, so unless this would take months I
wouldn't mind keeping this patch around as "waiting for external
process to complete". Sure, it's earlier than the actual release of
the standard, but that wasn't a blocker for SQL features that were
considered finalized either.
Kind regards,
Matthias van de Meent
Neon (https://neon.tech)
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: UUID v7
2023-02-14 14:13 RE: UUID v7 Kyzer Davis (kydavis) <[email protected]>
2023-06-22 18:30 ` Re: UUID v7 Nikolay Samokhvalov <[email protected]>
2023-07-06 12:24 ` Re: UUID v7 Daniel Gustafsson <[email protected]>
2023-07-06 13:29 ` Re: UUID v7 Matthias van de Meent <[email protected]>
@ 2023-07-06 13:43 ` Daniel Gustafsson <[email protected]>
2023-07-06 14:02 ` Re: UUID v7 Tom Lane <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Daniel Gustafsson @ 2023-07-06 13:43 UTC (permalink / raw)
To: Matthias van de Meent <[email protected]>; +Cc: Nikolay Samokhvalov <[email protected]>; Kyzer Davis (kydavis) <[email protected]>; Peter Eisentraut <[email protected]>; Andres Freund <[email protected]>; Andrey Borodin <[email protected]>; pgsql-hackers; [email protected] <[email protected]>; [email protected] <[email protected]>
> On 6 Jul 2023, at 15:29, Matthias van de Meent <[email protected]> wrote:
>
> On Thu, 6 Jul 2023 at 14:24, Daniel Gustafsson <[email protected]> wrote:
>>
>>> On 22 Jun 2023, at 20:30, Nikolay Samokhvalov <[email protected]> wrote:
>>
>>> Some small updates as I see them:
>>> - there is revision 7 now in https://github.com/ietf-wg-uuidrev/rfc4122bis
>>> - noticing that there is no commitfest record, I created one:
>>
>> I will actually go ahead and close this entry in the current CF, not because we
>> don't want the feature but because it's unlikely that it will go in now given
>> that standardization is still underway. Comitting anything right now seems
>> premature, we might as well wait for standardization given that we have lots of
>> time before the v17 freeze.
>
> I'd like to note that this draft has recently had its last call
> period, and has been proposed for publishing early last month.
Sure, but this document is in AD Evaluation and there are many stages left in
the IESG process, it may still take a fair bit of time before this is done.
> Sure, it's earlier than the actual release of
> the standard, but that wasn't a blocker for SQL features that were
> considered finalized either.
I can't speak for any SQL standard features we've committed before being
standardized, it's for sure not the norm for the project. I'm only commenting
on this particular Internet standard which we have plenty of time to commit
before v17 without rushing to beat a standards committee.
Also, if you look you can see that I moved it to the next CF in a vague hope
that standardization will be swift (which is admittedly never is).
--
Daniel Gustafsson
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: UUID v7
2023-02-14 14:13 RE: UUID v7 Kyzer Davis (kydavis) <[email protected]>
2023-06-22 18:30 ` Re: UUID v7 Nikolay Samokhvalov <[email protected]>
2023-07-06 12:24 ` Re: UUID v7 Daniel Gustafsson <[email protected]>
2023-07-06 13:29 ` Re: UUID v7 Matthias van de Meent <[email protected]>
2023-07-06 13:43 ` Re: UUID v7 Daniel Gustafsson <[email protected]>
@ 2023-07-06 14:02 ` Tom Lane <[email protected]>
2023-07-06 16:38 ` Re: UUID v7 Peter Eisentraut <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Tom Lane @ 2023-07-06 14:02 UTC (permalink / raw)
To: Daniel Gustafsson <[email protected]>; +Cc: Matthias van de Meent <[email protected]>; Nikolay Samokhvalov <[email protected]>; Kyzer Davis (kydavis) <[email protected]>; Peter Eisentraut <[email protected]>; Andres Freund <[email protected]>; Andrey Borodin <[email protected]>; pgsql-hackers; [email protected] <[email protected]>; [email protected] <[email protected]>
Daniel Gustafsson <[email protected]> writes:
> On 6 Jul 2023, at 15:29, Matthias van de Meent <[email protected]> wrote:
>> Sure, it's earlier than the actual release of
>> the standard, but that wasn't a blocker for SQL features that were
>> considered finalized either.
> I can't speak for any SQL standard features we've committed before being
> standardized, it's for sure not the norm for the project.
We have done a couple of things that way recently. An important
reason why we felt we could get away with that is that nowadays
we have people who actually sit on the SQL committee and have
reliable information on what's likely to make it into the final text
of the next version. I don't think we have equivalent visibility or
should have equivalent confidence about how UUID v7 standardization
will play out.
regards, tom lane
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: UUID v7
2023-02-14 14:13 RE: UUID v7 Kyzer Davis (kydavis) <[email protected]>
2023-06-22 18:30 ` Re: UUID v7 Nikolay Samokhvalov <[email protected]>
2023-07-06 12:24 ` Re: UUID v7 Daniel Gustafsson <[email protected]>
2023-07-06 13:29 ` Re: UUID v7 Matthias van de Meent <[email protected]>
2023-07-06 13:43 ` Re: UUID v7 Daniel Gustafsson <[email protected]>
2023-07-06 14:02 ` Re: UUID v7 Tom Lane <[email protected]>
@ 2023-07-06 16:38 ` Peter Eisentraut <[email protected]>
2023-07-07 12:06 ` Re: UUID v7 Andrey M. Borodin <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Peter Eisentraut @ 2023-07-06 16:38 UTC (permalink / raw)
To: Tom Lane <[email protected]>; Daniel Gustafsson <[email protected]>; +Cc: Matthias van de Meent <[email protected]>; Nikolay Samokhvalov <[email protected]>; Kyzer Davis (kydavis) <[email protected]>; Andres Freund <[email protected]>; Andrey Borodin <[email protected]>; pgsql-hackers; [email protected] <[email protected]>; [email protected] <[email protected]>
On 06.07.23 16:02, Tom Lane wrote:
> Daniel Gustafsson <[email protected]> writes:
>> On 6 Jul 2023, at 15:29, Matthias van de Meent <[email protected]> wrote:
>>> Sure, it's earlier than the actual release of
>>> the standard, but that wasn't a blocker for SQL features that were
>>> considered finalized either.
>
>> I can't speak for any SQL standard features we've committed before being
>> standardized, it's for sure not the norm for the project.
>
> We have done a couple of things that way recently. An important
> reason why we felt we could get away with that is that nowadays
> we have people who actually sit on the SQL committee and have
> reliable information on what's likely to make it into the final text
> of the next version. I don't think we have equivalent visibility or
> should have equivalent confidence about how UUID v7 standardization
> will play out.
(I have been attending some meetings and I'm on the mailing list.)
Anyway, I think it would be reasonable to review this patch now. We
might leave it hanging in "Ready for Committer" for a while when we get
there. But surely review can start now.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: UUID v7
2023-02-14 14:13 RE: UUID v7 Kyzer Davis (kydavis) <[email protected]>
2023-06-22 18:30 ` Re: UUID v7 Nikolay Samokhvalov <[email protected]>
2023-07-06 12:24 ` Re: UUID v7 Daniel Gustafsson <[email protected]>
2023-07-06 13:29 ` Re: UUID v7 Matthias van de Meent <[email protected]>
2023-07-06 13:43 ` Re: UUID v7 Daniel Gustafsson <[email protected]>
2023-07-06 14:02 ` Re: UUID v7 Tom Lane <[email protected]>
2023-07-06 16:38 ` Re: UUID v7 Peter Eisentraut <[email protected]>
@ 2023-07-07 12:06 ` Andrey M. Borodin <[email protected]>
2023-07-10 16:50 ` Re: UUID v7 Peter Eisentraut <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Andrey M. Borodin @ 2023-07-07 12:06 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; +Cc: Tom Lane <[email protected]>; Daniel Gustafsson <[email protected]>; Matthias van de Meent <[email protected]>; Nikolay Samokhvalov <[email protected]>; Kyzer Davis (kydavis) <[email protected]>; Andres Freund <[email protected]>; Andrey Borodin <[email protected]>; pgsql-hackers; [email protected] <[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)
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: UUID v7
2023-02-14 14:13 RE: UUID v7 Kyzer Davis (kydavis) <[email protected]>
2023-06-22 18:30 ` Re: UUID v7 Nikolay Samokhvalov <[email protected]>
2023-07-06 12:24 ` Re: UUID v7 Daniel Gustafsson <[email protected]>
2023-07-06 13:29 ` Re: UUID v7 Matthias van de Meent <[email protected]>
2023-07-06 13:43 ` Re: UUID v7 Daniel Gustafsson <[email protected]>
2023-07-06 14:02 ` Re: UUID v7 Tom Lane <[email protected]>
2023-07-06 16:38 ` Re: UUID v7 Peter Eisentraut <[email protected]>
2023-07-07 12:06 ` Re: UUID v7 Andrey M. Borodin <[email protected]>
@ 2023-07-10 16:50 ` Peter Eisentraut <[email protected]>
2023-07-30 10:08 ` Re: UUID v7 Andrey M. Borodin <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Peter Eisentraut @ 2023-07-10 16:50 UTC (permalink / raw)
To: Andrey M. Borodin <[email protected]>; +Cc: Tom Lane <[email protected]>; Daniel Gustafsson <[email protected]>; Matthias van de Meent <[email protected]>; Nikolay Samokhvalov <[email protected]>; Kyzer Davis (kydavis) <[email protected]>; Andres Freund <[email protected]>; Andrey Borodin <[email protected]>; pgsql-hackers; [email protected] <[email protected]>; [email protected] <[email protected]>
On 07.07.23 14:06, Andrey M. Borodin wrote:
> 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].
I suggest we keep this thread to v7, which has pretty straightforward
semantics for PostgreSQL. v8 by definition has many possible
implementations, so you're going to have to make pretty strong arguments
that yours is the best and only one, if you are going to claim the
gen_uuid_v8 function name.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: UUID v7
2023-02-14 14:13 RE: UUID v7 Kyzer Davis (kydavis) <[email protected]>
2023-06-22 18:30 ` Re: UUID v7 Nikolay Samokhvalov <[email protected]>
2023-07-06 12:24 ` Re: UUID v7 Daniel Gustafsson <[email protected]>
2023-07-06 13:29 ` Re: UUID v7 Matthias van de Meent <[email protected]>
2023-07-06 13:43 ` Re: UUID v7 Daniel Gustafsson <[email protected]>
2023-07-06 14:02 ` Re: UUID v7 Tom Lane <[email protected]>
2023-07-06 16:38 ` Re: UUID v7 Peter Eisentraut <[email protected]>
2023-07-07 12:06 ` Re: UUID v7 Andrey M. Borodin <[email protected]>
2023-07-10 16:50 ` Re: UUID v7 Peter Eisentraut <[email protected]>
@ 2023-07-30 10:08 ` Andrey M. Borodin <[email protected]>
2023-08-20 20:56 ` Re: UUID v7 Andrey M. Borodin <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Andrey M. Borodin @ 2023-07-30 10:08 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; +Cc: Tom Lane <[email protected]>; Daniel Gustafsson <[email protected]>; Matthias van de Meent <[email protected]>; Nikolay Samokhvalov <[email protected]>; Kyzer Davis (kydavis) <[email protected]>; Andres Freund <[email protected]>; Andrey Borodin <[email protected]>; pgsql-hackers; [email protected] <[email protected]>; [email protected] <[email protected]>; [email protected]
> On 10 Jul 2023, at 21:50, Peter Eisentraut <[email protected]> wrote:
>
> I suggest we keep this thread to v7, which has pretty straightforward semantics for PostgreSQL. v8 by definition has many possible implementations, so you're going to have to make pretty strong arguments that yours is the best and only one, if you are going to claim the gen_uuid_v8 function name.
Thanks Peter, I'll follow this course of action.
After discussion on GitHub with Sergey Prokhorenko [0] I understood that counter is optional, but useful part of UUID v7. It actually promotes sortability of data generated at high speed.
The standard does not specify how big counter should be. PFA patch with 16 bit counter. Maybe it worth doing 18bit counter - it will save us one byte of PRNG data. Currently we only take 2 bits out of the whole random byte.
Best regards, Andrey Borodin.
[0] https://github.com/x4m/pg_uuid_next/issues/1#issuecomment-1657074776 <https://github.com/x4m/pg_uuid_next/issues/1#issuecomment-1657074776;
Attachments:
[application/octet-stream] v3-0001-Implement-UUID-v7-as-per-IETF-draft.patch (5.7K, ../../[email protected]/3-v3-0001-Implement-UUID-v7-as-per-IETF-draft.patch)
download | inline diff:
From 67f4202555d1c8cf3230235e134d070ddb82173c Mon Sep 17 00:00:00 2001
From: Andrey Borodin <[email protected]>
Date: Fri, 10 Feb 2023 15:38:40 -0800
Subject: [PATCH v3] Implement UUID v7 as per IETF draft
Authors: Andrey Borodin, Sergey Prokhorenko
---
doc/src/sgml/func.sgml | 10 ++++-
src/backend/utils/adt/uuid.c | 50 ++++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 3 ++
src/test/regress/expected/opr_sanity.out | 1 +
src/test/regress/expected/uuid.out | 10 +++++
src/test/regress/sql/uuid.sql | 6 +++
6 files changed, 79 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index be2f54c914..b2d89cf415 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -13947,13 +13947,21 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
<primary>gen_random_uuid</primary>
</indexterm>
+ <indexterm>
+ <primary>gen_uuid_v7</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.
</para>
<para>
diff --git a/src/backend/utils/adt/uuid.c b/src/backend/utils/adt/uuid.c
index 4f7aa768fd..49f9c03995 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,50 @@ gen_random_uuid(PG_FUNCTION_ARGS)
PG_RETURN_UUID_P(uuid);
}
+
+static uint16_t sequence_counter;
+
+Datum
+gen_uuid_v7(PG_FUNCTION_ARGS)
+{
+ pg_uuid_t *uuid = palloc(UUID_LEN);
+ struct timeval tp;
+ uint64_t tms;
+ uint16_t local_counter = sequence_counter++;
+
+ 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 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 counter */
+ uuid->data[6] = (unsigned char)(local_counter>>12);
+ /* next 8 bits */
+ uuid->data[7] = (unsigned char)(local_counter>>4);
+ /* least significant 4 bits in a middle of a byte, leaving 2 bits of entropy */
+ uuid->data[8] = (unsigned char)(local_counter<<2);
+
+ /*
+ * 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);
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 6996073989..b11e0382c0 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -9119,6 +9119,9 @@
{ 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' },
# 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..3141183b01 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -857,6 +857,7 @@ sha384(bytea)
sha512(bytea)
gen_random_uuid()
starts_with(text,text)
+gen_uuid_v7()
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..fc9f50e69e 100644
--- a/src/test/regress/expected/uuid.out
+++ b/src/test/regress/expected/uuid.out
@@ -168,5 +168,15 @@ 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)
+
-- 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..02b8e7f10c 100644
--- a/src/test/regress/sql/uuid.sql
+++ b/src/test/regress/sql/uuid.sql
@@ -85,5 +85,11 @@ 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;
+
-- clean up
DROP TABLE guid1, guid2 CASCADE;
--
2.37.1 (Apple Git-137.1)
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: UUID v7
2023-02-14 14:13 RE: UUID v7 Kyzer Davis (kydavis) <[email protected]>
2023-06-22 18:30 ` Re: UUID v7 Nikolay Samokhvalov <[email protected]>
2023-07-06 12:24 ` Re: UUID v7 Daniel Gustafsson <[email protected]>
2023-07-06 13:29 ` Re: UUID v7 Matthias van de Meent <[email protected]>
2023-07-06 13:43 ` Re: UUID v7 Daniel Gustafsson <[email protected]>
2023-07-06 14:02 ` Re: UUID v7 Tom Lane <[email protected]>
2023-07-06 16:38 ` Re: UUID v7 Peter Eisentraut <[email protected]>
2023-07-07 12:06 ` Re: UUID v7 Andrey M. Borodin <[email protected]>
2023-07-10 16:50 ` Re: UUID v7 Peter Eisentraut <[email protected]>
2023-07-30 10:08 ` Re: UUID v7 Andrey M. Borodin <[email protected]>
@ 2023-08-20 20:56 ` Andrey M. Borodin <[email protected]>
2023-08-21 08:42 ` Re: UUID v7 Andrey M. Borodin <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Andrey M. Borodin @ 2023-08-20 20:56 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; +Cc: Tom Lane <[email protected]>; Daniel Gustafsson <[email protected]>; Matthias van de Meent <[email protected]>; Nikolay Samokhvalov <[email protected]>; Kyzer Davis (kydavis) <[email protected]>; Andres Freund <[email protected]>; Andrey Borodin <[email protected]>; pgsql-hackers; [email protected] <[email protected]>; [email protected] <[email protected]>; [email protected]
> On 30 Jul 2023, at 13:08, Andrey M. Borodin <[email protected]> wrote:
>
>
> After discussion on GitHub with Sergey Prokhorenko [0] I understood that counter is optional, but useful part of UUID v7. It actually promotes sortability of data generated at high speed.
> The standard does not specify how big counter should be. PFA patch with 16 bit counter. Maybe it worth doing 18bit counter - it will save us one byte of PRNG data. Currently we only take 2 bits out of the whole random byte.
>
Here's a new patch version. Now counter is initialised with strong random on every time change (each ms). However, one first bit of the counter is preserved to zero. This is done to extend counter capacity (I left comments with reference to RFC with explanations).
Thanks!
Best regards, Andrey Borodin.
Attachments:
[application/octet-stream] v4-0001-Implement-UUID-v7-as-per-IETF-draft.patch (6.5K, ../../[email protected]/2-v4-0001-Implement-UUID-v7-as-per-IETF-draft.patch)
download | inline diff:
From f53c76291c2b832aab9bcac0dd96b05ad37c37cd Mon Sep 17 00:00:00 2001
From: "Andrey M. Borodin" <[email protected]>
Date: Sun, 20 Aug 2023 23:55:31 +0300
Subject: [PATCH v4] Implement UUID v7 as per IETF draft
Authors: Andrey Borodin, Sergey Prokhorenko
---
doc/src/sgml/func.sgml | 10 +++-
src/backend/utils/adt/uuid.c | 76 ++++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 3 +
src/test/regress/expected/opr_sanity.out | 1 +
src/test/regress/expected/uuid.out | 10 ++++
src/test/regress/sql/uuid.sql | 6 ++
6 files changed, 105 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index be2f54c914..b2d89cf415 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -13947,13 +13947,21 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
<primary>gen_random_uuid</primary>
</indexterm>
+ <indexterm>
+ <primary>gen_uuid_v7</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.
</para>
<para>
diff --git a/src/backend/utils/adt/uuid.c b/src/backend/utils/adt/uuid.c
index 4f7aa768fd..fed0b1bc52 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,76 @@ gen_random_uuid(PG_FUNCTION_ARGS)
PG_RETURN_UUID_P(uuid);
}
+
+static uint32_t sequence_counter;
+static uint64_t previous_timestamp = 0;
+
+
+Datum
+gen_uuid_v7(PG_FUNCTION_ARGS)
+{
+ pg_uuid_t *uuid = palloc(UUID_LEN);
+ uint64_t tms;
+ struct timeval tp;
+
+ gettimeofday(&tp, NULL);
+
+ tms = ((uint64_t)tp.tv_sec) * 1000 + (tp.tv_usec) / 1000;
+
+ tms = pg_hton64(tms<<16);
+
+ /* Fill in time part */
+ memcpy(&uuid->data[0], &tms, 6);
+
+ if (tms == previous_timestamp)
+ {
+ /* Time did not change from the previous generation, we must increment counter */
+ ++sequence_counter;
+ /* 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);
+
+ sequence_counter = ((uint32_t)uuid->data[8] & 0x3f) +
+ (((uint32_t)uuid->data[7]) << 6) +
+ (((uint32_t)uuid->data[6] & 0x0f) << 14);
+
+ previous_timestamp = tms;
+ }
+
+ /*
+ * 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);
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 12fac15ceb..4e6089060a 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -9125,6 +9125,9 @@
{ 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' },
# 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..3141183b01 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -857,6 +857,7 @@ sha384(bytea)
sha512(bytea)
gen_random_uuid()
starts_with(text,text)
+gen_uuid_v7()
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..fc9f50e69e 100644
--- a/src/test/regress/expected/uuid.out
+++ b/src/test/regress/expected/uuid.out
@@ -168,5 +168,15 @@ 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)
+
-- 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..02b8e7f10c 100644
--- a/src/test/regress/sql/uuid.sql
+++ b/src/test/regress/sql/uuid.sql
@@ -85,5 +85,11 @@ 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;
+
-- clean up
DROP TABLE guid1, guid2 CASCADE;
--
2.37.1 (Apple Git-137.1)
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: UUID v7
2023-02-14 14:13 RE: UUID v7 Kyzer Davis (kydavis) <[email protected]>
2023-06-22 18:30 ` Re: UUID v7 Nikolay Samokhvalov <[email protected]>
2023-07-06 12:24 ` Re: UUID v7 Daniel Gustafsson <[email protected]>
2023-07-06 13:29 ` Re: UUID v7 Matthias van de Meent <[email protected]>
2023-07-06 13:43 ` Re: UUID v7 Daniel Gustafsson <[email protected]>
2023-07-06 14:02 ` Re: UUID v7 Tom Lane <[email protected]>
2023-07-06 16:38 ` Re: UUID v7 Peter Eisentraut <[email protected]>
2023-07-07 12:06 ` Re: UUID v7 Andrey M. Borodin <[email protected]>
2023-07-10 16:50 ` Re: UUID v7 Peter Eisentraut <[email protected]>
2023-07-30 10:08 ` Re: UUID v7 Andrey M. Borodin <[email protected]>
2023-08-20 20:56 ` Re: UUID v7 Andrey M. Borodin <[email protected]>
@ 2023-08-21 08:42 ` Andrey M. Borodin <[email protected]>
2023-08-30 19:04 ` Re: UUID v7 Andrey M. Borodin <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Andrey M. Borodin @ 2023-08-21 08:42 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; +Cc: Tom Lane <[email protected]>; Daniel Gustafsson <[email protected]>; Matthias van de Meent <[email protected]>; Nikolay Samokhvalov <[email protected]>; Kyzer Davis (kydavis) <[email protected]>; Andres Freund <[email protected]>; Andrey Borodin <[email protected]>; pgsql-hackers; [email protected] <[email protected]>; [email protected] <[email protected]>; [email protected]
> On 20 Aug 2023, at 23:56, Andrey M. Borodin <[email protected]> wrote:
>
> <v4-0001-Implement-UUID-v7-as-per-IETF-draft.patch>
I've observed, that pre-generating and buffering random numbers makes UUID generation 10 times faster.
Without buffering
postgres=# with x as (select gen_uuid_v7() from generate_series(1,1e6)) select count(*) from x;
Time: 5286.572 ms (00:05.287)
With buffering
postgres=# with x as (select gen_uuid_v7() from generate_series(1,1e6)) select count(*) from x;
Time: 390.091 ms
This can speed up gen_random_uuid() on the same scale too. PFA implementation of this technique.
Best regards, Andrey Borodin.
Attachments:
[application/octet-stream] v5-0001-Implement-UUID-v7-as-per-IETF-draft.patch (6.5K, ../../[email protected]/2-v5-0001-Implement-UUID-v7-as-per-IETF-draft.patch)
download | inline diff:
From f53c76291c2b832aab9bcac0dd96b05ad37c37cd Mon Sep 17 00:00:00 2001
From: "Andrey M. Borodin" <[email protected]>
Date: Sun, 20 Aug 2023 23:55:31 +0300
Subject: [PATCH v5 1/3] Implement UUID v7 as per IETF draft
Authors: Andrey Borodin, Sergey Prokhorenko
---
doc/src/sgml/func.sgml | 10 +++-
src/backend/utils/adt/uuid.c | 76 ++++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 3 +
src/test/regress/expected/opr_sanity.out | 1 +
src/test/regress/expected/uuid.out | 10 ++++
src/test/regress/sql/uuid.sql | 6 ++
6 files changed, 105 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index be2f54c914..b2d89cf415 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -13947,13 +13947,21 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
<primary>gen_random_uuid</primary>
</indexterm>
+ <indexterm>
+ <primary>gen_uuid_v7</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.
</para>
<para>
diff --git a/src/backend/utils/adt/uuid.c b/src/backend/utils/adt/uuid.c
index 4f7aa768fd..fed0b1bc52 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,76 @@ gen_random_uuid(PG_FUNCTION_ARGS)
PG_RETURN_UUID_P(uuid);
}
+
+static uint32_t sequence_counter;
+static uint64_t previous_timestamp = 0;
+
+
+Datum
+gen_uuid_v7(PG_FUNCTION_ARGS)
+{
+ pg_uuid_t *uuid = palloc(UUID_LEN);
+ uint64_t tms;
+ struct timeval tp;
+
+ gettimeofday(&tp, NULL);
+
+ tms = ((uint64_t)tp.tv_sec) * 1000 + (tp.tv_usec) / 1000;
+
+ tms = pg_hton64(tms<<16);
+
+ /* Fill in time part */
+ memcpy(&uuid->data[0], &tms, 6);
+
+ if (tms == previous_timestamp)
+ {
+ /* Time did not change from the previous generation, we must increment counter */
+ ++sequence_counter;
+ /* 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);
+
+ sequence_counter = ((uint32_t)uuid->data[8] & 0x3f) +
+ (((uint32_t)uuid->data[7]) << 6) +
+ (((uint32_t)uuid->data[6] & 0x0f) << 14);
+
+ previous_timestamp = tms;
+ }
+
+ /*
+ * 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);
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 12fac15ceb..4e6089060a 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -9125,6 +9125,9 @@
{ 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' },
# 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..3141183b01 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -857,6 +857,7 @@ sha384(bytea)
sha512(bytea)
gen_random_uuid()
starts_with(text,text)
+gen_uuid_v7()
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..fc9f50e69e 100644
--- a/src/test/regress/expected/uuid.out
+++ b/src/test/regress/expected/uuid.out
@@ -168,5 +168,15 @@ 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)
+
-- 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..02b8e7f10c 100644
--- a/src/test/regress/sql/uuid.sql
+++ b/src/test/regress/sql/uuid.sql
@@ -85,5 +85,11 @@ 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;
+
-- clean up
DROP TABLE guid1, guid2 CASCADE;
--
2.37.1 (Apple Git-137.1)
[application/octet-stream] v5-0002-Buffer-random-numbers.patch (2.1K, ../../[email protected]/3-v5-0002-Buffer-random-numbers.patch)
download | inline diff:
From b8b61133c36babae861ec3d0f38597314308de93 Mon Sep 17 00:00:00 2001
From: "Andrey M. Borodin" <[email protected]>
Date: Mon, 21 Aug 2023 11:34:55 +0300
Subject: [PATCH v5 2/3] Buffer random numbers
This allows to generate uuids 10 times faster
---
src/backend/utils/adt/uuid.c | 23 ++++++++++++++++++++---
1 file changed, 20 insertions(+), 3 deletions(-)
diff --git a/src/backend/utils/adt/uuid.c b/src/backend/utils/adt/uuid.c
index fed0b1bc52..a4e6349440 100644
--- a/src/backend/utils/adt/uuid.c
+++ b/src/backend/utils/adt/uuid.c
@@ -405,6 +405,24 @@ uuid_hash_extended(PG_FUNCTION_ARGS)
return hash_any_extended(key->data, UUID_LEN, PG_GETARG_INT64(1));
}
+#define UUID_RND_CACHE_LEN 512
+int rnd_cache_ptr = UUID_RND_CACHE_LEN;
+unsigned char random_cache[UUID_RND_CACHE_LEN];
+
+static bool
+cached_strong_random(void *buf, size_t len)
+{
+ if (len + rnd_cache_ptr >= UUID_RND_CACHE_LEN)
+ {
+ if (!pg_strong_random(random_cache, UUID_RND_CACHE_LEN))
+ return false;
+ rnd_cache_ptr = 0;
+ }
+ memcpy(buf, &random_cache[rnd_cache_ptr], len);
+ rnd_cache_ptr += len;
+ return true;
+}
+
Datum
gen_random_uuid(PG_FUNCTION_ARGS)
{
@@ -428,7 +446,6 @@ gen_random_uuid(PG_FUNCTION_ARGS)
static uint32_t sequence_counter;
static uint64_t previous_timestamp = 0;
-
Datum
gen_uuid_v7(PG_FUNCTION_ARGS)
{
@@ -450,7 +467,7 @@ gen_uuid_v7(PG_FUNCTION_ARGS)
/* Time did not change from the previous generation, we must increment counter */
++sequence_counter;
/* fill everything after the timestamp and counter with random bytes */
- if (!pg_strong_random(&uuid->data[8], UUID_LEN - 8))
+ if (!cached_strong_random(&uuid->data[8], UUID_LEN - 8))
ereport(ERROR,
(errcode(ERRCODE_INTERNAL_ERROR),
errmsg("could not generate random values")));
@@ -465,7 +482,7 @@ gen_uuid_v7(PG_FUNCTION_ARGS)
else
{
/* fill everything after the timestamp with random bytes */
- if (!pg_strong_random(&uuid->data[6], UUID_LEN - 6))
+ if (!cached_strong_random(&uuid->data[6], UUID_LEN - 6))
ereport(ERROR,
(errcode(ERRCODE_INTERNAL_ERROR),
errmsg("could not generate random values")));
--
2.37.1 (Apple Git-137.1)
[application/octet-stream] v5-0003-Use-cached-random-numbers-in-gen_random_uuid-too.patch (822B, ../../[email protected]/4-v5-0003-Use-cached-random-numbers-in-gen_random_uuid-too.patch)
download | inline diff:
From 1e76676eff1c57a7cab049c2e2e4ed0a65fa6a5b Mon Sep 17 00:00:00 2001
From: "Andrey M. Borodin" <[email protected]>
Date: Mon, 21 Aug 2023 11:35:57 +0300
Subject: [PATCH v5 3/3] Use cached random numbers in gen_random_uuid() too
---
src/backend/utils/adt/uuid.c | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/src/backend/utils/adt/uuid.c b/src/backend/utils/adt/uuid.c
index a4e6349440..2b1d6aaae3 100644
--- a/src/backend/utils/adt/uuid.c
+++ b/src/backend/utils/adt/uuid.c
@@ -428,7 +428,7 @@ gen_random_uuid(PG_FUNCTION_ARGS)
{
pg_uuid_t *uuid = palloc(UUID_LEN);
- if (!pg_strong_random(uuid, UUID_LEN))
+ if (!cached_strong_random(uuid, UUID_LEN))
ereport(ERROR,
(errcode(ERRCODE_INTERNAL_ERROR),
errmsg("could not generate random values")));
--
2.37.1 (Apple Git-137.1)
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: UUID v7
2023-02-14 14:13 RE: UUID v7 Kyzer Davis (kydavis) <[email protected]>
2023-06-22 18:30 ` Re: UUID v7 Nikolay Samokhvalov <[email protected]>
2023-07-06 12:24 ` Re: UUID v7 Daniel Gustafsson <[email protected]>
2023-07-06 13:29 ` Re: UUID v7 Matthias van de Meent <[email protected]>
2023-07-06 13:43 ` Re: UUID v7 Daniel Gustafsson <[email protected]>
2023-07-06 14:02 ` Re: UUID v7 Tom Lane <[email protected]>
2023-07-06 16:38 ` Re: UUID v7 Peter Eisentraut <[email protected]>
2023-07-07 12:06 ` Re: UUID v7 Andrey M. Borodin <[email protected]>
2023-07-10 16:50 ` Re: UUID v7 Peter Eisentraut <[email protected]>
2023-07-30 10:08 ` Re: UUID v7 Andrey M. Borodin <[email protected]>
2023-08-20 20:56 ` Re: UUID v7 Andrey M. Borodin <[email protected]>
2023-08-21 08:42 ` Re: UUID v7 Andrey M. Borodin <[email protected]>
@ 2023-08-30 19:04 ` Andrey M. Borodin <[email protected]>
0 siblings, 0 replies; 14+ messages in thread
From: Andrey M. Borodin @ 2023-08-30 19:04 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; +Cc: Tom Lane <[email protected]>; Daniel Gustafsson <[email protected]>; Matthias van de Meent <[email protected]>; Nikolay Samokhvalov <[email protected]>; Kyzer Davis (kydavis) <[email protected]>; Andres Freund <[email protected]>; Andrey Borodin <[email protected]>; pgsql-hackers; [email protected] <[email protected]>; [email protected] <[email protected]>; [email protected]
> On 21 Aug 2023, at 13:42, Andrey M. Borodin <[email protected]> wrote:
>
> <v5-0001-Implement-UUID-v7-as-per-IETF-draft.patch><v5-0002-Buffer-random-numbers.patch><v5-0003-Use-cached-random-numbers-in-gen_random_uuid-too.patch>
FPA attached next version.
Changes:
- implemented protection from time leap backwards when series is generated on the same backend
- counter overflow is now translated into ms step forward
Best regards, Andrey Borodin.
Attachments:
[application/octet-stream] v6-0001-Implement-UUID-v7-as-per-IETF-draft.patch (6.9K, ../../[email protected]/2-v6-0001-Implement-UUID-v7-as-per-IETF-draft.patch)
download | inline diff:
From 12bd390775c43a9ccf53451eae35c8930f97d481 Mon Sep 17 00:00:00 2001
From: "Andrey M. Borodin" <[email protected]>
Date: Sun, 20 Aug 2023 23:55:31 +0300
Subject: [PATCH v6 1/3] Implement UUID v7 as per IETF draft
Authors: Andrey Borodin, Sergey Prokhorenko
---
doc/src/sgml/func.sgml | 10 ++-
src/backend/utils/adt/uuid.c | 89 ++++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 3 +
src/test/regress/expected/opr_sanity.out | 1 +
src/test/regress/expected/uuid.out | 10 +++
src/test/regress/sql/uuid.sql | 6 ++
6 files changed, 118 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index be2f54c914..b2d89cf415 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -13947,13 +13947,21 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
<primary>gen_random_uuid</primary>
</indexterm>
+ <indexterm>
+ <primary>gen_uuid_v7</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.
</para>
<para>
diff --git a/src/backend/utils/adt/uuid.c b/src/backend/utils/adt/uuid.c
index 4f7aa768fd..7a493016c9 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,89 @@ gen_random_uuid(PG_FUNCTION_ARGS)
PG_RETURN_UUID_P(uuid);
}
+
+static uint32_t sequence_counter;
+static uint64_t previous_timestamp = 0;
+
+
+Datum
+gen_uuid_v7(PG_FUNCTION_ARGS)
+{
+ pg_uuid_t *uuid = palloc(UUID_LEN);
+ uint64_t tms;
+ struct timeval tp;
+
+ gettimeofday(&tp, NULL);
+
+ tms = ((uint64_t)tp.tv_sec) * 1000 + (tp.tv_usec) / 1000;
+
+ if (tms <= previous_timestamp)
+ {
+ /* Time did not increment 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);
+
+ 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
+ * 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);
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 12fac15ceb..4e6089060a 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -9125,6 +9125,9 @@
{ 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' },
# 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..3141183b01 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -857,6 +857,7 @@ sha384(bytea)
sha512(bytea)
gen_random_uuid()
starts_with(text,text)
+gen_uuid_v7()
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..fc9f50e69e 100644
--- a/src/test/regress/expected/uuid.out
+++ b/src/test/regress/expected/uuid.out
@@ -168,5 +168,15 @@ 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)
+
-- 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..02b8e7f10c 100644
--- a/src/test/regress/sql/uuid.sql
+++ b/src/test/regress/sql/uuid.sql
@@ -85,5 +85,11 @@ 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;
+
-- clean up
DROP TABLE guid1, guid2 CASCADE;
--
2.37.1 (Apple Git-137.1)
[application/octet-stream] v6-0003-Use-cached-random-numbers-in-gen_random_uuid-too.patch (822B, ../../[email protected]/3-v6-0003-Use-cached-random-numbers-in-gen_random_uuid-too.patch)
download | inline diff:
From d94739169c1fee8e1c7a810ca51f34130453d98d Mon Sep 17 00:00:00 2001
From: "Andrey M. Borodin" <[email protected]>
Date: Mon, 21 Aug 2023 11:35:57 +0300
Subject: [PATCH v6 3/3] Use cached random numbers in gen_random_uuid() too
---
src/backend/utils/adt/uuid.c | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/src/backend/utils/adt/uuid.c b/src/backend/utils/adt/uuid.c
index 28a79a7590..2ea4f84c91 100644
--- a/src/backend/utils/adt/uuid.c
+++ b/src/backend/utils/adt/uuid.c
@@ -428,7 +428,7 @@ gen_random_uuid(PG_FUNCTION_ARGS)
{
pg_uuid_t *uuid = palloc(UUID_LEN);
- if (!pg_strong_random(uuid, UUID_LEN))
+ if (!cached_strong_random(uuid, UUID_LEN))
ereport(ERROR,
(errcode(ERRCODE_INTERNAL_ERROR),
errmsg("could not generate random values")));
--
2.37.1 (Apple Git-137.1)
[application/octet-stream] v6-0002-Buffer-random-numbers.patch (2.0K, ../../[email protected]/4-v6-0002-Buffer-random-numbers.patch)
download | inline diff:
From 4be94504ab11fe44d3a14d0a7a4a64eb7167e6d4 Mon Sep 17 00:00:00 2001
From: "Andrey M. Borodin" <[email protected]>
Date: Mon, 21 Aug 2023 11:34:55 +0300
Subject: [PATCH v6 2/3] Buffer random numbers
This allows to generate uuids 10 times faster
---
src/backend/utils/adt/uuid.c | 23 ++++++++++++++++++++---
1 file changed, 20 insertions(+), 3 deletions(-)
diff --git a/src/backend/utils/adt/uuid.c b/src/backend/utils/adt/uuid.c
index 7a493016c9..28a79a7590 100644
--- a/src/backend/utils/adt/uuid.c
+++ b/src/backend/utils/adt/uuid.c
@@ -405,6 +405,24 @@ uuid_hash_extended(PG_FUNCTION_ARGS)
return hash_any_extended(key->data, UUID_LEN, PG_GETARG_INT64(1));
}
+#define UUID_RND_CACHE_LEN 512
+int rnd_cache_ptr = UUID_RND_CACHE_LEN;
+unsigned char random_cache[UUID_RND_CACHE_LEN];
+
+static bool
+cached_strong_random(void *buf, size_t len)
+{
+ if (len + rnd_cache_ptr >= UUID_RND_CACHE_LEN)
+ {
+ if (!pg_strong_random(random_cache, UUID_RND_CACHE_LEN))
+ return false;
+ rnd_cache_ptr = 0;
+ }
+ memcpy(buf, &random_cache[rnd_cache_ptr], len);
+ rnd_cache_ptr += len;
+ return true;
+}
+
Datum
gen_random_uuid(PG_FUNCTION_ARGS)
{
@@ -428,7 +446,6 @@ gen_random_uuid(PG_FUNCTION_ARGS)
static uint32_t sequence_counter;
static uint64_t previous_timestamp = 0;
-
Datum
gen_uuid_v7(PG_FUNCTION_ARGS)
{
@@ -455,7 +472,7 @@ gen_uuid_v7(PG_FUNCTION_ARGS)
tms = previous_timestamp;
/* fill everything after the timestamp and counter with random bytes */
- if (!pg_strong_random(&uuid->data[8], UUID_LEN - 8))
+ if (!cached_strong_random(&uuid->data[8], UUID_LEN - 8))
ereport(ERROR,
(errcode(ERRCODE_INTERNAL_ERROR),
errmsg("could not generate random values")));
@@ -470,7 +487,7 @@ gen_uuid_v7(PG_FUNCTION_ARGS)
else
{
/* fill everything after the timestamp with random bytes */
- if (!pg_strong_random(&uuid->data[6], UUID_LEN - 6))
+ if (!cached_strong_random(&uuid->data[6], UUID_LEN - 6))
ereport(ERROR,
(errcode(ERRCODE_INTERNAL_ERROR),
errmsg("could not generate random values")));
--
2.37.1 (Apple Git-137.1)
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2023-08-30 19:04 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-03-10 02:56 [PATCH v35 7/7] pg_ls_*dir to return all the metadata from pg_stat_file.. Justin Pryzby <[email protected]>
2023-02-14 14:13 RE: UUID v7 Kyzer Davis (kydavis) <[email protected]>
2023-06-22 18:30 ` Re: UUID v7 Nikolay Samokhvalov <[email protected]>
2023-07-06 12:24 ` Re: UUID v7 Daniel Gustafsson <[email protected]>
2023-07-06 13:29 ` Re: UUID v7 Matthias van de Meent <[email protected]>
2023-07-06 13:43 ` Re: UUID v7 Daniel Gustafsson <[email protected]>
2023-07-06 14:02 ` Re: UUID v7 Tom Lane <[email protected]>
2023-07-06 16:38 ` Re: UUID v7 Peter Eisentraut <[email protected]>
2023-07-07 12:06 ` Re: UUID v7 Andrey M. Borodin <[email protected]>
2023-07-10 16:50 ` Re: UUID v7 Peter Eisentraut <[email protected]>
2023-07-30 10:08 ` Re: UUID v7 Andrey M. Borodin <[email protected]>
2023-08-20 20:56 ` Re: UUID v7 Andrey M. Borodin <[email protected]>
2023-08-21 08:42 ` Re: UUID v7 Andrey M. Borodin <[email protected]>
2023-08-30 19:04 ` 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