Constraint exclusion with box and integer

From: Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Constraint exclusion with box and integer
Date: 2011-01-21 18:36:53
Message-ID: AANLkTinWsr_cWdKhfcuxtWCsnwJ+XPbB9ArO53P-LWHZ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Daniele Varrazzo 2011-01-21 18:48:43 Re: Constraint exclusion with box and integer
Previous Message Robert Haas 2011-01-21 17:54:53 Re: [HACKERS] Large object corruption during 'piped' pg_restore