HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

PostgreSQL: what are purpose of functions uuid_hash(uuid) and uuid_hash_extended(uuid, bigint)

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
postgresqlfunctionswhatareanduuid_hash_extendedbigintuuidpurposeuuid_hash

Problem

Functions uuid_hash and uuid_hash_extended

Exploring 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 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.