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.
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=#