Обсуждение: Constraint exclusion with box and integer

Hello,

I have the typical problem of overlapping periods exclusion and I'm
trying to give constraint exclusion a go as we have recently switched
to a PG 9.0 server.

All the examples I've found check a record against the whole table.
But in practice I will have a table of resources (hotel rooms,
whatever) and independent periods for each resource. EXCLUDE allows a
list of "field WITH op" clauses, and I've checked that they are ANDed
so the record is rejected only in case all the checks return true, but
I don't see how to combine a check on a range with a check on the
resource id:

=> alter table commission_rate add constraint check_overlapping
exclude (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 box has no default operator class for access method "btree"
HINT:  You must specify an operator class for the index or define a
default operator class for the data type.

=> 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?

Thanks

-- Daniele

On Fri, Jan 21, 2011 at 6:36 PM, Daniele Varrazzo
<daniele.varrazzo@gmail.com> wrote:

> All the examples I've found check a record against the whole table.
> But in practice I will have a table of resources (hotel rooms,
> whatever) and independent periods for each resource.

Clarification: I have read the depesz article
(http://www.depesz.com/index.php/2010/01/03/waiting-for-8-5-exclusion-constraints/)
but in my problem the overlaps must be check on the combination of
three ids, so it would take a 4D box, and still I wonder if it would
be doable at all if an id was a string.

-- Daniele

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.

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

Regards,
    Jeff Davis

On Fri, Jan 21, 2011 at 9:11 PM, Jeff Davis <pgsql@j-davis.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