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

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply