Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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).

share|improve this question
1  
There is something weird in your question - I can run the CREATE OPERATOR CLASS statement successfully on my 9.2 test DB. –  dezso Jul 30 '13 at 8:05
    
Yes, the create works. But try and run the CREATE INDEX command, or insert into the DB. I edited. –  IanC Jul 30 '13 at 8:09
2  
Please try to indicat which steps are working and which are failing. And exactly what is the index definition which sometimes fails. ('Sometimes' means that you don't always start from the same point...) –  dezso Jul 30 '13 at 8:47
    
Good point. Done. –  IanC Jul 30 '13 at 8:52
    
ERROR: 42883: function int4range(int4range[], unknown) does not exist –  dezso Jul 30 '13 at 9:40
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.