Unable to create bloom index

Question

I'm new to bloom indexes. I'm referring https://habr.com/en/company/postgrespro/blog/452968/ link to learn about new indexes.

When I was trying to create bloom index on own test table, I got below error:

SQL Error [42704]: ERROR: data type bigint has no default operator class for access method "bloom"
Hint: You must specify an operator class for the index or define a default operator class for the data 
type.

No doubt, because in my table I have a column where I'm using bigint datatype and the same column I'm including in my index creation.

To avoid that error, I tried to create my own class for bigint datatype. Like below:

CREATE OPERATOR CLASS bigint_ops
DEFAULT FOR TYPE int USING bloom AS
OPERATOR  1  =(bigint,bigint),
FUNCTION  1  hashbigint;

and I got below error:

   SQL Error [42883]: ERROR: could not find a function named "hashbigint"

Any help to avoid this error will be much appreciated.

AdamKG_16361 · Answer

The hashing function for bigint is hashint8, not hashbigint. I found this by running the query in the post you linked and filtering to where the type is 'bigint'.

testdb=# with t0 as (select distinct
       opc.opcintype::regtype::text,
       amop.amopopr::regoperator,
       ampr.amproc
  from pg_am am, pg_opclass opc, pg_amop amop, pg_amproc ampr
 where am.amname = 'hash'
   and opc.opcmethod = am.oid
   and amop.amopfamily = opc.opcfamily
   and amop.amoplefttype = opc.opcintype
   and amop.amoprighttype = opc.opcintype
   and ampr.amprocfamily = opc.opcfamily
   and ampr.amproclefttype = opc.opcintype
order by opc.opcintype::regtype::text) select * from t0 where opcintype='bigint';
 opcintype |     amopopr      |  amproc  
-----------+------------------+----------
 bigint    | =(bigint,bigint) | hashint8
(1 row)

There's also an error in your CREATE OPERATOR statement; it needs to be DEFAULT FOR TYPE bigint, not int.

testdb=# create extension bloom;
CREATE EXTENSION
testdb=# CREATE OPERATOR CLASS bigint_ops
DEFAULT FOR TYPE int USING bloom AS
OPERATOR  1  =(bigint,bigint),
FUNCTION  1  hashint8;
ERROR:  could not make operator class "bigint_ops" be default for type pg_catalog.int4
DETAIL:  Operator class "int4_ops" already is the default.
testdb=# CREATE OPERATOR CLASS bigint_ops
DEFAULT FOR TYPE bigint USING bloom AS
OPERATOR  1  =(bigint,bigint),
FUNCTION  1  hashint8;
CREATE OPERATOR CLASS
testdb=#

Unable to create bloom index

1 Answers