Last active
July 20, 2024 00:23
-
-
Save fabiolimace/515a0440e3e40efeb234e12644a6a346 to your computer and use it in GitHub Desktop.
Functions for generating UUIDv6 and UUIDv7 on PostgreSQL
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* | |
| * MIT License | |
| * | |
| * Copyright (c) 2023-2024 Fabio Lima | |
| * | |
| * Permission is hereby granted, free of charge, to any person obtaining a copy | |
| * of this software and associated documentation files (the "Software"), to deal | |
| * in the Software without restriction, including without limitation the rights | |
| * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell | |
| * copies of the Software, and to permit persons to whom the Software is | |
| * furnished to do so, subject to the following conditions: | |
| * | |
| * The above copyright notice and this permission notice shall be included in | |
| * all copies or substantial portions of the Software. | |
| * | |
| * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR | |
| * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, | |
| * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE | |
| * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER | |
| * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, | |
| * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN | |
| * THE SOFTWARE. | |
| */ | |
| /** | |
| * Returns a time-ordered UUID with Gregorian Epoch (UUIDv6). | |
| * | |
| * Referencie: https://www.rfc-editor.org/rfc/rfc9562.html | |
| * | |
| * MIT License. | |
| * | |
| */ | |
| create or replace function uuid6() returns uuid as $$ | |
| declare | |
| begin | |
| return uuid6(clock_timestamp()); | |
| end $$ language plpgsql; | |
| create or replace function uuid6(p_timestamp timestamp with time zone) returns uuid as $$ | |
| declare | |
| v_time double precision := null; | |
| v_gregorian_t bigint := null; | |
| v_clock_sequence_and_node bigint := null; | |
| v_gregorian_t_hex_a varchar := null; | |
| v_gregorian_t_hex_b varchar := null; | |
| v_clock_sequence_and_node_hex varchar := null; | |
| c_epoch double precision := 12219292800; -- RFC-9562 epoch: 1582-10-15 | |
| c_100ns_factor double precision := 10^7; -- RFC-9562 precision: 100 ns | |
| c_version bigint := x'0000000000006000'::bigint; -- RFC-9562 version: b'0110...' | |
| c_variant bigint := x'8000000000000000'::bigint; -- RFC-9562 variant: b'10xx...' | |
| begin | |
| v_time := extract(epoch from p_timestamp); | |
| v_gregorian_t := trunc((v_time + c_epoch) * c_100ns_factor); | |
| v_clock_sequence_and_node := trunc(random() * 2^30)::bigint << 32 | trunc(random() * 2^32)::bigint; | |
| v_gregorian_t_hex_a := lpad(to_hex((v_gregorian_t >> 12)), 12, '0'); | |
| v_gregorian_t_hex_b := lpad(to_hex((v_gregorian_t & 4095) | c_version), 4, '0'); | |
| v_clock_sequence_and_node_hex := lpad(to_hex(v_clock_sequence_and_node | c_variant), 16, '0'); | |
| return (v_gregorian_t_hex_a || v_gregorian_t_hex_b || v_clock_sequence_and_node_hex)::uuid; | |
| end $$ language plpgsql; | |
| ------------------------------------------------------------------- | |
| -- EXAMPLE: | |
| ------------------------------------------------------------------- | |
| -- | |
| -- select uuid6() uuid, clock_timestamp()-statement_timestamp() time_taken; | |
| -- | |
| -- |uuid |time_taken | | |
| -- |--------------------------------------|------------------| | |
| -- |1eeca632-cf2a-65e0-85f3-151064c2409d |00:00:00.000108 | | |
| -- | |
| ------------------------------------------------------------------- | |
| -- EXAMPLE: generate a list | |
| ------------------------------------------------------------------- | |
| -- | |
| -- with x as (select clock_timestamp() as t from generate_series(1, 1000)) | |
| -- select uuid6(x.t) uuid, x.t::text ts from x; | |
| -- | |
| -- |uuid |ts | | |
| -- |------------------------------------|-----------------------------| | |
| -- |1eeca634-f783-63f0-9988-48906d79f782|2024-02-13 08:30:37.891480-03| | |
| -- |1eeca634-f783-6c24-97af-605238f4c3d0|2024-02-13 08:30:37.891691-03| | |
| -- |1eeca634-f783-6e7c-9c2e-624f24b87738|2024-02-13 08:30:37.891754-03| | |
| -- |1eeca634-f784-6070-a67b-4fc6659143e7|2024-02-13 08:30:37.891800-03| | |
| -- |1eeca634-f784-6200-befd-0e20be5b0087|2024-02-13 08:30:37.891842-03| | |
| -- |1eeca634-f784-6390-8f79-d4dacec1c3e0|2024-02-13 08:30:37.891881-03| | |
| -- |1eeca634-f784-6520-8ee7-96091b017d4c|2024-02-13 08:30:37.891920-03| | |
| -- |1eeca634-f784-66b0-a63e-c285d8a63e21|2024-02-13 08:30:37.891958-03| | |
| -- |1eeca634-f784-6840-8c00-38659c4bf807|2024-02-13 08:30:37.891997-03| | |
| -- |1eeca634-f784-69d0-b775-4bbfd45eb99e|2024-02-13 08:30:37.892036-03| | |
| -- | |
| ------------------------------------------------------------------- | |
| -- FOR TEST: the expected result is an empty result set | |
| ------------------------------------------------------------------- | |
| -- | |
| -- with t as (select uuid6() as id from generate_series(1, 1000)) | |
| -- select * from t where (id is null or id::text !~ '^[a-f0-9]{8}-[a-f0-9]{4}-6[a-f0-9]{3}-[89ab][a-f0-9]{3}-[a-f0-9]{12}$'); | |
| -- | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* | |
| * MIT License | |
| * | |
| * Copyright (c) 2023-2024 Fabio Lima | |
| * | |
| * Permission is hereby granted, free of charge, to any person obtaining a copy | |
| * of this software and associated documentation files (the "Software"), to deal | |
| * in the Software without restriction, including without limitation the rights | |
| * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell | |
| * copies of the Software, and to permit persons to whom the Software is | |
| * furnished to do so, subject to the following conditions: | |
| * | |
| * The above copyright notice and this permission notice shall be included in | |
| * all copies or substantial portions of the Software. | |
| * | |
| * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR | |
| * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, | |
| * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE | |
| * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER | |
| * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, | |
| * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN | |
| * THE SOFTWARE. | |
| */ | |
| /** | |
| * Returns a time-ordered UUID with Unix Epoch (UUIDv7). | |
| * | |
| * Referencie: https://www.rfc-editor.org/rfc/rfc9562.html | |
| * | |
| * MIT License. | |
| * | |
| */ | |
| create or replace function uuid7() returns uuid as $$ | |
| declare | |
| begin | |
| return uuid7(clock_timestamp()); | |
| end $$ language plpgsql; | |
| create or replace function uuid7(p_timestamp timestamp with time zone) returns uuid as $$ | |
| declare | |
| v_time double precision := null; | |
| v_unix_t bigint := null; | |
| v_rand_a bigint := null; | |
| v_rand_b bigint := null; | |
| v_unix_t_hex varchar := null; | |
| v_rand_a_hex varchar := null; | |
| v_rand_b_hex varchar := null; | |
| c_milli double precision := 10^3; -- 1 000 | |
| c_micro double precision := 10^6; -- 1 000 000 | |
| c_scale double precision := 4.096; -- 4.0 * (1024 / 1000) | |
| c_version bigint := x'0000000000007000'::bigint; -- RFC-9562 version: b'0111...' | |
| c_variant bigint := x'8000000000000000'::bigint; -- RFC-9562 variant: b'10xx...' | |
| begin | |
| v_time := extract(epoch from p_timestamp); | |
| v_unix_t := trunc(v_time * c_milli); | |
| v_rand_a := trunc((v_time * c_micro - v_unix_t * c_milli) * c_scale); | |
| v_rand_b := trunc(random() * 2^30)::bigint << 32 | trunc(random() * 2^32)::bigint; | |
| v_unix_t_hex := lpad(to_hex(v_unix_t), 12, '0'); | |
| v_rand_a_hex := lpad(to_hex((v_rand_a | c_version)::bigint), 4, '0'); | |
| v_rand_b_hex := lpad(to_hex((v_rand_b | c_variant)::bigint), 16, '0'); | |
| return (v_unix_t_hex || v_rand_a_hex || v_rand_b_hex)::uuid; | |
| end $$ language plpgsql; | |
| ------------------------------------------------------------------- | |
| -- EXAMPLE: | |
| ------------------------------------------------------------------- | |
| -- | |
| -- select uuid7() uuid, clock_timestamp()-statement_timestamp() time_taken; | |
| -- | |
| -- |uuid |time_taken | | |
| -- |--------------------------------------|------------------| | |
| -- |018da240-e0db-72e1-86f5-345c2c240387 |00:00:00.000222 | | |
| -- | |
| ------------------------------------------------------------------- | |
| -- EXAMPLE: generate a list | |
| ------------------------------------------------------------------- | |
| -- | |
| -- with x as (select clock_timestamp() as t from generate_series(1, 1000)) | |
| -- select uuid7(x.t) uuid, x.t::text ts from x; | |
| -- | |
| -- |uuid |ts | | |
| -- |------------------------------------|-----------------------------| | |
| -- |018da235-6271-70cd-a937-0bb7d22b801e|2024-02-13 08:23:44.113054-03| | |
| -- |018da235-6271-7214-9188-1d3191883b5d|2024-02-13 08:23:44.113126-03| | |
| -- |018da235-6271-723d-bebe-87f66085fad7|2024-02-13 08:23:44.113143-03| | |
| -- |018da235-6271-728f-86ba-6e277d10c0a3|2024-02-13 08:23:44.113156-03| | |
| -- |018da235-6271-72b8-9887-f31e4ca48020|2024-02-13 08:23:44.113168-03| | |
| -- |018da235-6271-72e1-bbeb-8b686d0d4281|2024-02-13 08:23:44.113179-03| | |
| -- |018da235-6271-730a-96a2-73275626f72a|2024-02-13 08:23:44.113190-03| | |
| -- |018da235-6271-7333-8a5c-9d1ab89dc489|2024-02-13 08:23:44.113201-03| | |
| -- |018da235-6271-735c-ba64-a42b55ad7d5c|2024-02-13 08:23:44.113212-03| | |
| -- |018da235-6271-7385-a0fb-c65f5be24073|2024-02-13 08:23:44.113223-03| | |
| -- | |
| ------------------------------------------------------------------- | |
| -- FOR TEST: the expected result is an empty result set | |
| ------------------------------------------------------------------- | |
| -- | |
| -- with t as (select uuid7() as id from generate_series(1, 1000)) | |
| -- select * from t where (id is null or id::text !~ '^[a-f0-9]{8}-[a-f0-9]{4}-7[a-f0-9]{3}-[89ab][a-f0-9]{3}-[a-f0-9]{12}$'); | |
| -- | |
the uuid generated is not accurate, sometimes 1/10 condition
Could you show the code used to test the accuracy?
The UUID generated by the function is accurate to the millisecond.
In the test below, I changed 2 lines of the original code. At the end there is a comparison of the millisecond in the timestamp and the millisecond embedded in the UUID.
Note that there is no significant loss of accuracy. The only difference is due to the floating-point division operation of the IEEE-754 standard.
Additionally, the microsecond variable in the function is used to add extra monotonicity, it is not intended to be precise.
-- create or replace function uuid7() returns uuid as $$ -- ## REPLACED LINE ##
create or replace function uuid7(parameter_timestamp timestamp with time zone) returns uuid as $$
declare
v_time timestamp with time zone:= null;
v_secs bigint := null;
v_msec bigint := null;
v_usec bigint := null;
v_timestamp bigint := null;
v_timestamp_hex varchar := null;
v_random bigint := null;
v_random_hex varchar := null;
v_bytes bytea;
c_variant bit(64):= x'8000000000000000'; -- RFC-4122 variant: b'10xx...'
begin
-- Get seconds and micros
-- v_time := clock_timestamp(); -- ## REPLACED LINE ##
v_time := parameter_timestamp;
v_secs := EXTRACT(EPOCH FROM v_time);
v_msec := mod(EXTRACT(MILLISECONDS FROM v_time)::numeric, 10^3::numeric);
v_usec := mod(EXTRACT(MICROSECONDS FROM v_time)::numeric, 10^3::numeric);
-- Generate timestamp hexadecimal (and set version 7)
v_timestamp := (((v_secs * 10^3) + v_msec)::bigint << 12) | (v_usec << 2);
v_timestamp_hex := lpad(to_hex(v_timestamp), 16, '0');
v_timestamp_hex := substr(v_timestamp_hex, 2, 12) || '7' || substr(v_timestamp_hex, 14, 3);
-- Generate the random hexadecimal (and set variant b'10xx')
v_random := ((random()::numeric * 2^62::numeric)::bigint::bit(64) | c_variant)::bigint;
v_random_hex := lpad(to_hex(v_random), 16, '0');
-- Concat timestemp and random hexadecimal
v_bytes := decode(v_timestamp_hex || v_random_hex, 'hex');
return encode(v_bytes, 'hex')::uuid;
end $$ language plpgsql;
create or replace function extract_timestamp(parameter_uuid uuid) returns bigint as $$
declare
v_uuid_hex varchar := null;
v_timestamp bigint := null;
v_timestamp_hex varchar := null;
begin
v_uuid_hex := replace(parameter_uuid::varchar, '-', '');
v_timestamp_hex := substring(v_uuid_hex, 1, 12) || substring(v_uuid_hex, 14, 3);
v_timestamp := ('x'||lpad(v_timestamp_hex,16,'0'))::bit(64)::bigint;
return mod(v_timestamp >> 12, (10^3)::bigint);
end $$ language plpgsql;
select statement_timestamp()::varchar ts, mod(EXTRACT(MILLISECONDS from statement_timestamp())::bigint, 1000) ms_from_ts, extract_timestamp(uuid7(statement_timestamp())) ms_from_uuid;| ts | ms_from_ts | ms_from_uuid |
|---|---|---|
| 2023-11-14 01:53:15.61599-03 | 616 | 616 |
For postgres 9.2 compatibility you need to cast the following two values explicitly:
v_secs := EXTRACT(EPOCH FROM v_time)::bigint;
v_usec := mod(EXTRACT(MICROSECONDS FROM v_time)::numeric, 10^6::numeric)::bigint;Otherwise you'll get
SQL Error [22P02]: ERROR: invalid input syntax for integer: "1707596980.41937304"
Where: PL/pgSQL function uuid7() line 20 at assignment
Thank you, @andnorxor !
Functions updated.
Thank you for sharing your code; it truly was a lifesaver. In one of my projects I realized too late that postgresql-contrib was not installed on some legacy systems and it was nearly impossible to install it due security restrictions. But I had already based large parts of my module on UUIDs. Your function was the perfect workaround.
🚨 FIXED rounding error
Extracting epoch seconds and converting them to bigint was causing rounding errors. For example, extracting the epoch seconds from the timestamp '2024-02-12 17:49:10.827009Z' should return 1707760150, but was returning 1707760151 (+1).
To fix this, you need to truncate before converting to bigint.
See the result of this query:
select EXTRACT(EPOCH FROM '2024-02-12 17:49:10.827009Z'::timestamp) as seconds,
EXTRACT(EPOCH FROM '2024-02-12 17:49:10.827009Z'::timestamp)::bigint as seconds_bigint, -- implicitly rounded
TRUNC(EXTRACT(EPOCH FROM '2024-02-12 17:49:10.827009Z'::timestamp))::bigint as seconds_truncated; -- explicitly truncated|seconds |seconds_bigint|seconds_truncated|
|--------------------|--------------|-----------------|
|1,707,760,150.827009|1.707.760.151 |1.707.760.150 |
The rounding issue is now fixed.
Thanks, Tobias, for letting me know about this!
I'm refactoring the functions. I hope they are more readable after refactoring.
Refactored.
When will v6 become stable, is there any news about it?
I don't know. I hope draft 14 is the last and the new RFC is finally completed.
There are no updates to the email archive:
https://mailarchive.ietf.org/arch/browse/uuidrev/
I had to cast 2^12 as numeric to get this to work in PostgreSQL version 16.2
DIV(v_gregorian_t, 2^12::NUMERIC)
MOD(v_gregorian_t, 2^12::NUMERIC)
Dude, fantastic job! \m/
An anonymous said and deleted later:
Getting ERROR: invalid input syntax for type uuid: "018d3f3b-4390-7375-9acd-7611c9a7c" error on some uuids generated by these functions.
The last 3 hex digits are missing in 018d3f3b-4390-7375-9acd-7611c9a7c???.
For this error to occur inside the function, the following condition must be true for some return of random(), but I didn't find any value that would cause this.
select length(to_hex(((random()::numeric * 2^62::numeric)::bigint::bit(64) | x'8000000000000000')::bigint)) < 16;I think rand_b can be split into two 32-bit random(), just in case of another floating point issue.
Instead, I just removed the decode() and encode() operations because they are no longer needed.
Diffs:
- v_output_bytes := decode(v_gregorian_t_hex_a || v_gregorian_t_hex_b || v_clock_sequence_and_node_hex, 'hex');
- return encode(v_output_bytes, 'hex')::uuid;
+ return (v_gregorian_t_hex_a || v_gregorian_t_hex_b || v_clock_sequence_and_node_hex)::uuid;- v_output_bytes := decode(v_unix_t_hex || v_rand_a_hex || v_rand_b_hex, 'hex');
- return encode(v_output_bytes, 'hex')::uuid;
+ return (v_unix_t_hex || v_rand_a_hex || v_rand_b_hex)::uuid;Thanks any way, anonymous!
v_rand_b := random()::numeric * 2^62::numeric;
This line wants to produce 62 random bits, but a double between 0 and 1 only has 52 bits in the mantissa. Casting it to numeric doesn't give you more bits of entropy.
You need to do something like:
-- Generate a random 64-bit integer
create or replace function random_bigint() returns bigint as $$
declare
hi bigint;
lo bigint;
begin
-- generate a number from 0 to 4294967295 inclusive
hi := (random() * 0x100000000)::bigint;
lo := (random() * 0x100000000)::bigint;
return (hi << 32) | lo;
end;
$$ language plpgsql;
You are right @eloff. I missed the mantissa detail.
Replaced all numeric data types with bigint or double precision. I was using numeric to resist IEEE 754 floating point arithmetic, but this resistance is futile.
Here are the diff lines related to the variable v_rand_b:
- v_rand_b := random()::numeric * 2^62::numeric;
+ v_rand_b := trunc(random() * 2^30)::bigint << 32 | trunc(random() * 2^32)::bigint;I think the functions are simpler now (and maybe faster).
Thank you for letting me know! 👍
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Hi @productdevbook
The UUIDv7 generator here is in accordance with draft-14 section 5.7.
The only exception is that the
random()function is not a CSPRNG generator, as you may have noticed.You have to install
pgcryptoto have access to crypto functions.Thanks for asking.