Re: operator exclusion constraints [was: generalized index constraints]

Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> I would still really like to decouple this from CREATE INDEX because of
> two reasons:
> 1. Cannot support multiple constraints per index very easily. I think
> this is a significant feature.
> 2. Must decide to make constraint at the same time as making the
> index, and once it's there, you can't remove it without dropping
> the index.

I don't actually find either of those arguments to be credible in the
least. I don't think that people will find it useful to enforce
multiple constraints with one index, and I don't believe that they'll
design an index without knowledge of the constraint they will enforce
with it. The closest precedent we have is the UNIQUE constraint.
How often have we had requests to add or drop UNIQUE in an existing
index? Maybe there were more than zero, but not by a lot.

As an example of why I don't believe the first item, consider something
like
create index ... (a = , b = )
(or whatever the syntax is to exclude equality on each column
separately). Yeah, it will work, but have you considered the efficiency
implications? Searching such an index for b, independently of a, is
going to suck to such an extent that you'd be *far* better off building
two separate indexes. We do not have, and are unlikely ever to have,
index types in which a search that doesn't constrain the first index
column is efficient.

regards, tom lane