1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
|
CREATE EXTENSION "uuid-ossp";
SELECT uuid_nil();
uuid_nil
--------------------------------------
00000000-0000-0000-0000-000000000000
(1 row)
SELECT uuid_ns_dns();
uuid_ns_dns
--------------------------------------
6ba7b810-9dad-11d1-80b4-00c04fd430c8
(1 row)
SELECT uuid_ns_url();
uuid_ns_url
--------------------------------------
6ba7b811-9dad-11d1-80b4-00c04fd430c8
(1 row)
SELECT uuid_ns_oid();
uuid_ns_oid
--------------------------------------
6ba7b812-9dad-11d1-80b4-00c04fd430c8
(1 row)
SELECT uuid_ns_x500();
uuid_ns_x500
--------------------------------------
6ba7b814-9dad-11d1-80b4-00c04fd430c8
(1 row)
-- some quick and dirty field extraction functions
-- this is actually timestamp concatenated with clock sequence, per RFC 4122
CREATE FUNCTION uuid_timestamp_bits(uuid) RETURNS varbit AS
$$ SELECT ('x' || substr($1::text, 15, 4) || substr($1::text, 10, 4) ||
substr($1::text, 1, 8) || substr($1::text, 20, 4))::bit(80)
& x'0FFFFFFFFFFFFFFF3FFF' $$
LANGUAGE SQL STRICT IMMUTABLE;
CREATE FUNCTION uuid_version_bits(uuid) RETURNS varbit AS
$$ SELECT ('x' || substr($1::text, 15, 2))::bit(8) & '11110000' $$
LANGUAGE SQL STRICT IMMUTABLE;
CREATE FUNCTION uuid_reserved_bits(uuid) RETURNS varbit AS
$$ SELECT ('x' || substr($1::text, 20, 2))::bit(8) & '11000000' $$
LANGUAGE SQL STRICT IMMUTABLE;
CREATE FUNCTION uuid_multicast_bit(uuid) RETURNS bool AS
$$ SELECT (('x' || substr($1::text, 25, 2))::bit(8) & '00000001') != '00000000' $$
LANGUAGE SQL STRICT IMMUTABLE;
CREATE FUNCTION uuid_local_admin_bit(uuid) RETURNS bool AS
$$ SELECT (('x' || substr($1::text, 25, 2))::bit(8) & '00000010') != '00000000' $$
LANGUAGE SQL STRICT IMMUTABLE;
CREATE FUNCTION uuid_node(uuid) RETURNS text AS
$$ SELECT substr($1::text, 25) $$
LANGUAGE SQL STRICT IMMUTABLE;
-- Ideally, the multicast bit would never be set in V1 output, but the
-- UUID library may fall back to MC if it can't get the system MAC address.
-- Also, the local-admin bit might be set (if so, we're probably inside a VM).
-- So we can't test either bit here.
SELECT uuid_version_bits(uuid_generate_v1()),
uuid_reserved_bits(uuid_generate_v1());
uuid_version_bits | uuid_reserved_bits
-------------------+--------------------
00010000 | 10000000
(1 row)
-- Although RFC 4122 only requires the multicast bit to be set in V1MC style
-- UUIDs, our implementation always sets the local-admin bit as well.
SELECT uuid_version_bits(uuid_generate_v1mc()),
uuid_reserved_bits(uuid_generate_v1mc()),
uuid_multicast_bit(uuid_generate_v1mc()),
uuid_local_admin_bit(uuid_generate_v1mc());
uuid_version_bits | uuid_reserved_bits | uuid_multicast_bit | uuid_local_admin_bit
-------------------+--------------------+--------------------+----------------------
00010000 | 10000000 | t | t
(1 row)
-- timestamp+clock sequence should be monotonic increasing in v1
SELECT uuid_timestamp_bits(uuid_generate_v1()) < uuid_timestamp_bits(uuid_generate_v1());
?column?
----------
t
(1 row)
SELECT uuid_timestamp_bits(uuid_generate_v1mc()) < uuid_timestamp_bits(uuid_generate_v1mc());
?column?
----------
t
(1 row)
-- Ideally, the node value is stable in V1 addresses, but OSSP UUID
-- falls back to V1MC behavior if it can't get the system MAC address.
SELECT CASE WHEN uuid_multicast_bit(uuid_generate_v1()) AND
uuid_local_admin_bit(uuid_generate_v1()) THEN
true -- punt, no test
ELSE
uuid_node(uuid_generate_v1()) = uuid_node(uuid_generate_v1())
END;
case
------
t
(1 row)
-- In any case, V1MC node addresses should be random.
SELECT uuid_node(uuid_generate_v1()) <> uuid_node(uuid_generate_v1mc());
?column?
----------
t
(1 row)
SELECT uuid_node(uuid_generate_v1mc()) <> uuid_node(uuid_generate_v1mc());
?column?
----------
t
(1 row)
SELECT uuid_generate_v3(uuid_ns_dns(), 'www.widgets.com');
uuid_generate_v3
--------------------------------------
3d813cbb-47fb-32ba-91df-831e1593ac29
(1 row)
SELECT uuid_generate_v5(uuid_ns_dns(), 'www.widgets.com');
uuid_generate_v5
--------------------------------------
21f7f8de-8051-5b89-8680-0195ef798b6a
(1 row)
SELECT uuid_version_bits(uuid_generate_v4()),
uuid_reserved_bits(uuid_generate_v4());
uuid_version_bits | uuid_reserved_bits
-------------------+--------------------
01000000 | 10000000
(1 row)
SELECT uuid_generate_v4() <> uuid_generate_v4();
?column?
----------
t
(1 row)
|
