CREATE EXTENSION sequential_uuids;
-- uuid_sequence_nextval
CREATE SEQUENCE s;
-- invalid block size
SELECT uuid_sequence_nextval('s'::regclass, 0, 1);
ERROR:  block size must be a positive integer
SELECT uuid_sequence_nextval('s'::regclass, -1, 1);
ERROR:  block size must be a positive integer
-- invalid block count
SELECT uuid_sequence_nextval('s'::regclass, 1, 0);
ERROR:  number of blocks must be a positive integer
SELECT uuid_sequence_nextval('s'::regclass, 1, -1);
ERROR:  number of blocks must be a positive integer
CREATE TABLE uuid_tmp AS SELECT uuid_sequence_nextval('s'::regclass, 256, 65536)::text AS val FROM generate_series(1,10000) s(i);
-- there should be 10k distinct UUID values (collisions unlikely)
SELECT COUNT(DISTINCT val) FROM uuid_tmp;
 count 
-------
 10000
(1 row)

-- there should be 40 blocks (each up to 256 values)
SELECT COUNT(DISTINCT substring(val, 1, 4)) FROM uuid_tmp;
 count 
-------
    40
(1 row)

-- there should be two blocks that are not exactly 256 values - the first one
-- (because sequences start at 1) and the last one (not fully generated yet)
WITH x AS (SELECT substring(val, 1, 4) AS block, count(*) AS cnt FROM uuid_tmp GROUP BY 1)
SELECT * FROM x WHERE cnt != 256;
 block | cnt 
-------+-----
 0027  |  16
(1 row)

DROP SEQUENCE s;
DROP TABLE uuid_tmp;
-- try with a larger block
CREATE SEQUENCE s;
CREATE TABLE uuid_tmp AS SELECT uuid_sequence_nextval('s'::regclass, 65536, 256)::text AS val FROM generate_series(1,200000) s(i);
-- there should be 200k distinct UUID values (collisions unlikely)
SELECT COUNT(DISTINCT val) FROM uuid_tmp;
 count  
--------
 200000
(1 row)

-- there should be 4 blocks (each up to 65536 values)
SELECT COUNT(DISTINCT substring(val, 1, 2)) FROM uuid_tmp;
 count 
-------
     4
(1 row)

-- there should be two blocks that are not exactly 256 values - the first one
-- (because sequences start at 1) and the last one (not fully generated yet)
SELECT substring(val, 1, 2) AS block, count(*) AS cnt FROM uuid_tmp GROUP BY 1;
 block |  cnt  
-------+-------
 00    | 65536
 01    | 65536
 02    | 65536
 03    |  3392
(4 rows)

DROP SEQUENCE s;
DROP TABLE uuid_tmp;
-- try with tiny block, to make sure we generate prefix correctly
CREATE SEQUENCE s;
CREATE TABLE uuid_tmp AS SELECT uuid_sequence_nextval('s'::regclass, 100, 100)::text AS val FROM generate_series(1,1000) s(i);
-- there should be 200k distinct UUID values (collisions unlikely)
SELECT COUNT(DISTINCT val) FROM uuid_tmp;
 count 
-------
  1000
(1 row)

-- there should be 4 blocks (each up to 65536 values)
SELECT COUNT(DISTINCT substring(val, 1, 2)) FROM uuid_tmp;
 count 
-------
    10
(1 row)

-- there should be two blocks that are not exactly 256 values - the first one
-- (because sequences start at 1) and the last one (not fully generated yet)
SELECT substring(val, 1, 2) AS block, count(*) AS cnt FROM uuid_tmp GROUP BY 1 ORDER BY 1;
 block | cnt 
-------+-----
 00    | 100
 01    | 100
 02    | 100
 03    | 100
 04    | 100
 05    | 100
 06    | 100
 07    | 100
 08    | 100
 09    | 100
(10 rows)

DROP SEQUENCE s;
DROP TABLE uuid_tmp;
-- try wrapping the block count
CREATE SEQUENCE s;
CREATE TABLE uuid_tmp AS SELECT uuid_sequence_nextval('s'::regclass, 256, 256)::text AS val FROM generate_series(1,67000) s(i);
-- there should be 256 blocks
WITH x AS (SELECT substring(val, 1, 2) AS block, count(*) AS cnt FROM uuid_tmp GROUP BY 1 ORDER BY 1)
SELECT * FROM x WHERE cnt != 256;
 block | cnt 
-------+-----
 00    | 512
 01    | 512
 02    | 512
 03    | 512
 04    | 512
 05    | 440
(6 rows)

DROP SEQUENCE s;
DROP TABLE uuid_tmp;
-- uuid_time_nextval
CREATE TABLE uuid_tmp (val text);
INSERT INTO uuid_tmp SELECT uuid_time_nextval(1, 256);
SELECT pg_sleep(2);
 pg_sleep 
----------
 
(1 row)

INSERT INTO uuid_tmp SELECT uuid_time_nextval(1, 256);
SELECT COUNT(DISTINCT substring(val, 1, 2)) FROM uuid_tmp;
 count 
-------
     2
(1 row)

DROP TABLE uuid_tmp;