-
-
Save fabiolimace/515a0440e3e40efeb234e12644a6a346 to your computer and use it in GitHub Desktop.
/* | |
* 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 (UUIDv6). | |
* | |
* Referencies: | |
* - https://github.com/uuid6/uuid6-ietf-draft | |
* - https://github.com/ietf-wg-uuidrev/rfc4122bis | |
* | |
* MIT License. | |
* | |
* Tags: uuid guid uuid-generator guid-generator generator time order rfc4122 rfc-4122 | |
*/ | |
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 numeric := null; | |
v_gregorian_t numeric := null; | |
v_clock_sequence_and_node numeric := null; | |
v_gregorian_t_hex_a varchar := null; | |
v_gregorian_t_hex_b varchar := null; | |
v_clock_sequence_and_node_hex varchar := null; | |
v_output_bytes bytea := null; | |
c_100ns_factor numeric := 10^7::numeric; | |
c_epoch numeric := -12219292800::numeric; -- RFC-4122 epoch: '1582-10-15' | |
c_version bit(64) := x'0000000000006000'; -- RFC-4122 version: b'0110...' | |
c_variant bit(64) := x'8000000000000000'; -- RFC-4122 variant: b'10xx...' | |
begin | |
v_time := extract(epoch from p_timestamp); | |
v_gregorian_t := (v_time - c_epoch) * c_100ns_factor; | |
v_clock_sequence_and_node := random()::numeric * 2^62::numeric; | |
v_gregorian_t_hex_a := lpad(to_hex((div(v_gregorian_t, 2^12::numeric)::bigint)), 12, '0'); | |
v_gregorian_t_hex_b := lpad(to_hex((mod(v_gregorian_t, 2^12::numeric)::bigint::bit(64) | c_version)::bigint), 4, '0'); | |
v_clock_sequence_and_node_hex := lpad(to_hex((v_clock_sequence_and_node::bigint::bit(64) | c_variant)::bigint), 16, '0'); | |
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; | |
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, 10)) | |
-- 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}$'); | |
-- | |
/* | |
* 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 with Unix Epoch UUID (UUIDv7). | |
* | |
* Referencies: | |
* - https://github.com/uuid6/uuid6-ietf-draft | |
* - https://github.com/ietf-wg-uuidrev/rfc4122bis | |
* | |
* MIT License. | |
* | |
* Tags: uuid guid uuid-generator guid-generator generator time order rfc4122 rfc-4122 | |
*/ | |
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 numeric := null; | |
v_unix_t numeric := null; | |
v_rand_a numeric := null; | |
v_rand_b numeric := null; | |
v_unix_t_hex varchar := null; | |
v_rand_a_hex varchar := null; | |
v_rand_b_hex varchar := null; | |
v_output_bytes bytea := null; | |
c_milli_factor numeric := 10^3::numeric; -- 1000 | |
c_micro_factor numeric := 10^6::numeric; -- 1000000 | |
c_scale_factor numeric := 4.096::numeric; -- 4.0 * (1024 / 1000) | |
c_version bit(64) := x'0000000000007000'; -- RFC-4122 version: b'0111...' | |
c_variant bit(64) := x'8000000000000000'; -- RFC-4122 variant: b'10xx...' | |
begin | |
v_time := extract(epoch from p_timestamp); | |
v_unix_t := trunc(v_time * c_milli_factor); | |
v_rand_a := ((v_time * c_micro_factor) - (v_unix_t * c_milli_factor)) * c_scale_factor; | |
v_rand_b := random()::numeric * 2^62::numeric; | |
v_unix_t_hex := lpad(to_hex(v_unix_t::bigint), 12, '0'); | |
v_rand_a_hex := lpad(to_hex((v_rand_a::bigint::bit(64) | c_version)::bigint), 4, '0'); | |
v_rand_b_hex := lpad(to_hex((v_rand_b::bigint::bit(64) | c_variant)::bigint), 16, '0'); | |
v_output_bytes := decode(v_unix_t_hex || v_rand_a_hex || v_rand_b_hex, 'hex'); | |
return encode(v_output_bytes, '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}$'); | |
-- | |
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)
For postgres 9.2 compatibility you need to cast the following two values explicitly:
Otherwise you'll get