Re: Constraint exclusion with box and integer

On Fri, Jan 21, 2011 at 9:11 PM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> On Fri, 2011-01-21 at 18:36 +0000, Daniele Varrazzo wrote:
>> => alter table commission_rate add constraint check_overlapping
>> exclude using gist (payer_id with =, box( point(extract(epoch from
>> start_date), extract(epoch from start_date)),
>> point(coalesce(extract(epoch from end_date), 'infinity'),
>> coalesce(extract(epoch from end_date),'infinity') ) ) with &&);
>> ERROR:  data type integer has no default operator class for access method "gist"
>> HINT:  You must specify an operator class for the index or define a
>> default operator class for the data type.
>>
>> Can I build a constraint check using both a box (for the range) and an
>> integer (for a fkey) or should I go back to the classic trigger + lock
>> solution?
>
> Install btree_gist, and this should work.
>
> Exclusion constraints are enforced with an index, and an index can only
> have one index access method (btree, gist, etc.). So you need to have
> one index access method that works for both "=" on integers and "&&" on
> boxes.

Yes, I see the implementation constraint...

> There's no hope of making a btree work for "&&" on boxes, so we need to
> make gist work for "=" on integers.

Ok, the btree_gist seems exactly the missing link. Will try with that.

Thanks,

-- Daniele