Re: WIP: generalized index constraints

On Tue, 2009-07-07 at 14:57 -0400, Tom Lane wrote:
> I don't think this even approximates the need --- in particular it's not
> clear what the semantics of combination across different index columns
> are. I assume you've hot-wired it so that several BTEqualStrategyNumber
> columns will work like a normal multicolumn uniqueness constraint (IOW
> it's okay as long as at least one column is NULL or isn't equal). But
> I'm not at all sure that's what I'd want for some other operator type.

If any input attributes are NULL, the constraint automatically succeeds
(I think that follows the SQL philosophy). Perhaps we should be able to
override this behavior somehow?

Now, for each attribute where a constraint is defined, it becomes a new
scan key used in the index lookup to enforce the constraint. So, the
more attributes in the constraint, the more permissive the constraint
(just like UNIQUE).

I can't think of another good interpretation of the constraint. If a
constraint on (x,y) meant "x is unique, and y is unique", it would be
hard to scan the index for y's uniqueness. If you want to say that both
are independently unique, I believe that requires two indexes, and so it
would probably be best to just require the constraints to be specified
separately. Thoughts?

> Also, what happens if you want to use the same index to support more
> than one logical constraint? This is impossible if you put the
> information into pg_index, I think.
>

I like the idea to store the information outside of pg_index. It means
that I don't have to build the index and check the constraint at the
same time. It would be more like adding a CHECK constraint.

Regards,
Jeff Davis