Re: WIP: generalized index constraints

On Tue, Sep 15, 2009 at 1:28 PM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> On Tue, 2009-09-15 at 13:16 -0400, Robert Haas wrote:
>> Uhh.... so what happens if I create an index constraint using the
>> +(integer, integer) operator?
>
> You can use any operator that has an index search strategy. Overlaps is
> probably the most useful, but you could imagine other operators, like a
> bi-directional containment operator (either LHS is contained in RHS, or
> vice-versa).
>
> You can also get creative and have a "similarity" operator that
> determines whether two tuples are "too similar". As long as it is
> symmetric, the feature will work.

So it allows us to create constraints of the following form?

For all A in the index, there exists no B in the index such that the
given operator (which must be a binary operator returning boolean)
holds of A and B.

If that's correct, I think we should definitely at least mention the
word "overlap" somewhere in the documentation, because that's what
people are going to want to use it for, and it's hard to conceptualize
without examples, at least for me. You may already be doing this, I
haven't read the patch.

Also, there are certainly other things you could want to do that can't
be handled by this approach. Perhaps you'd like to create a
constraint that a given value can appear at most twice, or a two
column index (A, B) such that for any A the smallest value of B is
less than A. These are certainly less common requirements than what
you're talking about here, and I don't think it's important to try to
support them - at least not at this point - but the word "generalized"
doesn't give me a clue that I won't be able to do those things but I
will be able to make an index that prevents my users from handing out
duplicate IP blocks.

...Robert