Re: WIP: generalized index constraints

On Tue, 2009-09-15 at 13:48 -0400, Robert Haas wrote:
> 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.

Yes. And it's slightly more complicated for multi-column constraints:

For all tuples A in the index with attributes 1 to N, there exists no
tuple B such that:
A1 op1 B1 AND
A2 op2 B2 AND
...
AN op2 BN

If all operators are "=", and the index implements searching on
equality, it's semantically equivalent to a unique index.

>
> 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.

My current example uses "overlaps", but I will expand the documentation
to provide more clarity.

> 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.

The first is a good example, and actually I think that could be an
add-on to my patch without much difficulty.

The second can't be enforced with an index in nearly the same way
because deleting a tuple could violate the constraint. Also, it seems
like it would be hard to express that kind of constraint. But I agree
that, in principle, it is an index-enforceable constraint.

> 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.

As far as the name goes, the best I've got so far are "index
constraints" and "generalized index constraints". I'm happy to change
the name if you have a reasonable suggestion.

I don't think the word "generalized" implies that it can do absolutely
anything possible. For the list discussion, I think it's appropriate to
use the term "generalized", because my patch generalizes index
constraints. However, I agree that we shouldn't use that too much in the
code/docs because someone might think of something more general later.

Regards,
Jeff Davis