Re: WIP: generalized index constraints

On Wed, 2009-09-16 at 10:14 +0300, Peter Eisentraut wrote:
> What they should be called is generalized unique constraints, without
> reference to "index". Because what they generalize is the operator by
> which uniqueness is determined.

How about GUC, for short? ;-)

Do you think that Tomás's suggestion of "proximity constraints" would
satisfy your requirement on the basis that "proximity" is a more general
form of the word "unique"?

> First, we have so far been fairly consistent to document that unique
> indexes are an implementation detail of unique constraints and should
> usually not be used directly. This new approach basically reverses that
> and forces you to define your constraints by means of implementation
> details rather than a logical description. There is nothing in this
> feature that makes it strikingly different from the existing constraint
> types in a way that would prevent a reasonable syntax for defining the
> constraint at table definition time. Another problem this would lead to
> is that a say dump of a table definition wouldn't actually contain all
> the constraints that apply to the table anymore, because there might be
> additional stuff such as this that can't be expressed that way.

Those are all good points. I think ultimately we need to support this at
table creation time and make index specification optional.

We do need to allow the user to specify the index, however. An important
use case of this feature is defining an index on (A, B, C) and using it
to enforce UNIQUE(A,B) and UNIQUE(A,C).

> CREATE TABLE circles (c circle UNIQUE ON gist &&);

Should we use USING instead of ON to be consistent with CREATE INDEX?

Also, right now a UNIQUE by itself creates a btree. Let's say the user
has btree_gist installed and they declare (a =, b =) without specifying
the AM. Which one do we choose?

I suppose we can come up with a default order, like btree, hash, gist,
gin; and just choose the first one with a matching opclass.

Also, we should decide whether UNIQUE(a,b) should be shorthand for (a =,
b =), or whether they should be treated differently somehow. If so,
we'll need to come up with some kind of rules for how it determines that
UNIQUE chooses to use a btree with indisunique enforcement; and we need
to come up with a way to force it to choose the new enforcement
mechanism in my patch if the user wants to.

> CREATE TABLE data (a int UNIQUE ON btree =);

I think we should provide some way for the user to specify what
enforcement mechanism is used when multiple options are possible. In
this example should it use the existing mechanism or the new mechanism?

> ALTER TABLE circles ADD CONSTRAINT circles_idx_constr (c &&) USING INDEX
> circles_idx;
>
> doesn't seem very intuitive about what is actually being constrained.
> For a while I was thinking that it was constraining the table to values
> that are in the index or something. So using a word such as UNIQUE
> would help explain what is going on.

I'm still uncomfortable using the word UNIQUE. I see that you are taking
an approach similar to ORDER BY ... USING. However, I'm concerned
because we're using a special case word to describe the general feature,
and I think that's confusing.

On the other hand, it's hard to argue that (a &&, b =) is intuitive, so
I'll acquiesce if you get some agreement from someone else.

Regards,
Jeff Davis