PostgreSQL index array of int4range using GIN - custom operator class

Question

Here is my table:

CREATE TABLE
        mytable
        (
                id      INT NOT NULL PRIMARY KEY,
                val     int4range[]
        );

I want to index the val column:

CREATE INDEX
    ix_mytable_val
ON      mytable
USING   GIN (INT4RANGE(val, '[]'));   // error, as is GIN(val)

I came up with the following:

CREATE OPERATOR CLASS gin_int4range_ops
DEFAULT FOR TYPE int4range[] USING gin AS
OPERATOR        1       <(anyrange,anyrange),
OPERATOR        2       <=(anyrange,anyrange),
OPERATOR        3       =(anyrange,anyrange),
OPERATOR        4       >=(anyrange,anyrange),
OPERATOR        5       >(anyrange,anyrange),
FUNCTION        1       lower(anyrange),
FUNCTION        2       upper(anyrange),
FUNCTION        3       isempty(anyrange),
FUNCTION        4       lower_inc(anyrange),
FUNCTION        5       upper_inc(anyrange);

But when I try to create the index, it fails (error below). However, if I call the create from within a DO $$ block, it executes.

If the create index executed, I get the error on INSERT INTO instead.

"ERROR: cache lookup failed for type 1"

I also tried this:

OPERATOR        1       &&(anyrange,anyrange),
OPERATOR        2       <@(anyrange,anyrange),
OPERATOR        3       @>(anyrange,anyrange),
OPERATOR        4       =(anyrange,anyrange),

In order to try and solve this, I have rebooted PG, the machine, and vacuumed the DB. I believe there is an error in the CREATE OPERATOR code.

If I can index an array of custom type of (int, int4range), that would be even better.

I've spent quite some time (a full day) wading through documentation, forums, etc., but can find nothing that really helps me to understand how to solve this (i.e. create a working custom operator class).

revoua · Answer 1 · 2013-07-29T08:21:52.943

2

You need to CREATE OPERATOR CLASS based on Range Functions and Operators, for example:

   CREATE OPERATOR CLASS gin_int4range_ops
    DEFAULT FOR TYPE int4range[] USING gin AS
        OPERATOR        1       =(anyrange,anyrange),
        FUNCTION        1       lower(anyrange),
        FUNCTION        2       upper(anyrange),
        FUNCTION        3       isempty(anyrange),
        FUNCTION        4       lower_inc(anyrange),
        FUNCTION        5       upper_inc(anyrange);

Now you can CREATE INDEX:

CREATE INDEX ix_mytable4_vhstore_low
ON mytable USING gin (val gin_int4range_ops);

Check also:
Operator Classes and Operator Families
CREATE OPERATOR CLASS

The following query shows all defined operator classes:

SELECT am.amname AS index_method,
       opc.opcname AS opclass_name
    FROM pg_am am, pg_opclass opc
    WHERE opc.opcmethod = am.oid
    ORDER BY index_method, opclass_name;

This query shows all defined operator families and all the operators included in each family:

SELECT am.amname AS index_method,
       opf.opfname AS opfamily_name,
       amop.amopopr::regoperator AS opfamily_operator
    FROM pg_am am, pg_opfamily opf, pg_amop amop
    WHERE opf.opfmethod = am.oid AND
          amop.amopfamily = opf.oid
    ORDER BY index_method, opfamily_name, opfamily_operator;

edited Jul 29 '13 at 08:21

answered Jul 29 '13 at 08:16

revoua

2,044
1
21
28

Thanks much! When I tried to create the index, I got "ERROR: cache lookup failed for type 1". I did restart the server. Any ideas? – IamIC Jul 29 '13 at 09:21
I've tried everything I can think of, but still get that error. – IamIC Jul 29 '13 at 11:07
Not really. I'm running Windows, not Linux. I've done a full reboot, and a full vacuum with analyze. Is it possibly something to do with the code itself? – IamIC Jul 29 '13 at 11:14
If I put the CREATE INDEX inside a DO $$ block, then it will run. But in this case, INSERT INTO throws the same error. – IamIC Jul 29 '13 at 11:28
According to [Table 35-6. GIN Array Strategies](http://www.postgresql.org/docs/9.2/static/xindex.html) current operator class functions are not correct. – revoua Jul 29 '13 at 11:59
I edited it as per the link I reference, but it still throws. – IamIC Jul 29 '13 at 12:08

PostgreSQL index array of int4range using GIN - custom operator class

1 Answers1