2

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

1
  • From the names I guess(!) they are used for hash indexes and hash partitioning Jul 11, 2021 at 9:56

1 Answer 1

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.