patternsqlMinor
PostgreSQL: what are purpose of functions uuid_hash(uuid) and uuid_hash_extended(uuid, bigint)
Viewed 0 times
postgresqlfunctionswhatareanduuid_hash_extendedbigintuuidpurposeuuid_hash
Problem
Functions
Exploring postgresql I discovered 2 functions that looks interesting:
proname
func_desc
args_def
args
rettype
uuid_hash
hash
uuid
{uuid}
integer
uuid_hash_extended
hash
uuid, bigint
{uuid, bigint}
bigint
Let's try to use them:
uuid_hash
uuid_hash_extended
uuid_hash
uuid_hash_extended
1353656403
-6859010066814654381
1620818621
-4122998508949357891
DB Feedle: https://www.db-fiddle.com/f/8CG8HaFn6D2mLKLk3LTwx2/0
Hm.
Looks interesting and maybe has posssible uses.
Switching postgresql versions I noticed that
Looking into postgresql documentation - I found nothing.
Web search - nothing too.
Question
What is the purpose of those functions?
For what they could be used?
UPDATE
A. The answer https://dba.stackexchange.com/a/295517/82983 is accepted. The source code of function looks very simple
B. Interesting usage found in https://stackoverflow.com/a/66848699/1168212
uuid_hash and uuid_hash_extendedExploring postgresql I discovered 2 functions that looks interesting:
SELECT
p.proname ,
obj_description(p.oid) AS func_desc,
pg_get_function_arguments(p.oid) AS args_def,
string_to_array(pg_get_function_identity_arguments(p.oid), ','::text) AS args,
pg_get_function_result(p.oid) AS rettype
FROM
pg_catalog.pg_proc P
WHERE
proname ~~ 'uuid_hash%'
proname
func_desc
args_def
args
rettype
uuid_hash
hash
uuid
{uuid}
integer
uuid_hash_extended
hash
uuid, bigint
{uuid, bigint}
bigint
Let's try to use them:
SELECT
uuid_hash(uuid_nil()),
uuid_hash_extended(uuid_nil(), 0),
uuid_hash(MD5('hello')::uuid),
uuid_hash_extended(MD5('hello')::uuid, 0);
uuid_hash
uuid_hash_extended
uuid_hash
uuid_hash_extended
1353656403
-6859010066814654381
1620818621
-4122998508949357891
DB Feedle: https://www.db-fiddle.com/f/8CG8HaFn6D2mLKLk3LTwx2/0
Hm.
Looks interesting and maybe has posssible uses.
Switching postgresql versions I noticed that
uuid_hash_extended appeared in PostgreSQL 11.Looking into postgresql documentation - I found nothing.
Web search - nothing too.
Question
What is the purpose of those functions?
For what they could be used?
UPDATE
A. The answer https://dba.stackexchange.com/a/295517/82983 is accepted. The source code of function looks very simple
B. Interesting usage found in https://stackoverflow.com/a/66848699/1168212
Solution
They are used for
hash index support
It's possible to track dependencies through pg_amproc/pg_opclass/pg_opfamily system catalogs, but this is not as obvious compared to the comment.
Since they are not documented for direct call by user, PostgreSQL developers community may decide to change/replace/remove such functions in new major release.
hash index method. As clearly mentioned in source code:hash index support
It's possible to track dependencies through pg_amproc/pg_opclass/pg_opfamily system catalogs, but this is not as obvious compared to the comment.
Since they are not documented for direct call by user, PostgreSQL developers community may decide to change/replace/remove such functions in new major release.
Context
StackExchange Database Administrators Q#295508, answer score: 4
Revisions (0)
No revisions yet.