I tried below function as which can be used as default to column. But every
time we need to created 2 sequences, 1st one takes care of the first 8
bytes and 2nd takes care of the 2nd part of the UUID. I have not tested
index and space utilization. I have to examine this. This might not be
completely unique in the nature. but still trying for the best.
CREATE OR REPLACE FUNCTION public.fnu_generate_sequential_uuid(
sequence1 text,
sequence2 text)
RETURNS uuid
LANGUAGE 'plpgsql'
COST 100.0
VOLATILE NOT LEAKPROOF
AS $function$
DECLARE
sequenceUUIDPart1 text;
randomUUIDPart2 text;
counter integer:=0;
significantByte integer:=0;
startIndex integer:=0;
endIndex integer:=0;
BEGIN
-- Get random UUID
randomUUIDPart2 := replace(( uuid_generate_v4 () :: text),'-','');
-- verify first sequence reached max count.
IF to_hex(currval(sequence1)) :: text = '7fffffffffffffff' THEN
startIndex:=0;
endIndex:=7;
-- convert sequence into 32 bit string
sequenceUUIDPart1 = rpad(to_hex(nextval(sequence2))::text, 32, '0');
ELSE
startIndex:=8;
endIndex:=15;
-- convert sequence into 32 bit string
sequenceUUIDPart1 = rpad(to_hex(nextval(sequence1))::text, 32, '0');
END IF;
RAISE NOTICE 'current Guid: %', sequenceUUIDPart1;
-- loop through the 8th byte to 16th byte, till first sequence max .
-- loop through the 0 to 7 the byte till second sequence end.
FOR counter IN startIndex..endIndex LOOP
select get_byte(decode(sequenceUUIDPart1::text,'hex'), counter) into
significantByte;
-- fill last 8 bytes with the generated random UUID values.
sequenceUUIDPart1 := encode(set_byte(decode(sequenceUUIDPart1
::text,'hex') :: bytea ,counter, significantByte ) :: bytea, 'hex') :: text;
RAISE NOTICE 'current Guid: %', sequenceUUIDPart1;
END LOOP;
return sequenceUUIDPart1 :: UUID;
EXCEPTION
WHEN OTHERS
THEN
RAISE EXCEPTION 'An error was encountered in
create_engagement_data_get_aud_area_ent_list - % -ERROR- %', sqlstate,
sqlerrm;
END
$function$;
On Wed, Oct 31, 2018 at 1:51 AM Sehrope Sarkuni <sehr...@jackdb.com> wrote:
> I came across a project for time based UUID ("tuid") a little while back:
> https://github.com/tanglebones/pg_tuid
>
> I haven't used in production but skimmed through the code a bit out of
> technical curiosity. It handles some of the expected edge cases for
> backwards clock drift and concurrent generation.
>
> The repo includes a PG extension and sample app code for generating tuids
> in a couple languages as well as a pure-SQL one (though that one uses
> random() rather than get_random_bytes() so I'd consider it more of an proof
> of concept).
>
> Regards,
> -- Sehrope Sarkuni
> Founder & CEO | JackDB, Inc. | https://www.jackdb.com/
>
>
