Re: WIP: generalized index constraints

On Tue, 2009-09-15 at 12:37 -0400, Robert Haas wrote:
> Instead of calling these generalized index constraints, I wonder if we
> oughtn't to be calling them something like "don't-overlap constraints"
> (that's a bad name, but something along those lines). They're not
> really general at all, except compared to uniqueness constraints (and
> they aren't called generalized unique-index constraints, just
> generalized index constraints).

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.

Don't all of these have the same effect at the end?

CREATE TABLE data (a int UNIQUE);

CREATE TABLE data (a int);
CREATE UNIQUE INDEX data_a_idx ON data USING btree (a);

CREATE TABLE data (a int);
CREATE INDEX data_a_idx ON data USING btree (a);
ALTER TABLE data ADD CONSTRAINT a_unique (a =) USING INDEX data_a_idx;

Which brings me to two concerns about the syntax.

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.

If you look at the example from the documentation,

CREATE TABLE circles(c circle);
CREATE INDEX circles_idx ON circles USING gist (c);
ALTER TABLE circles ADD CONSTRAINT circles_idx_constr (c &&) USING INDEX
circles_idx;

the only variable pieces of information that need to be provided to the
table are the index type and the operator. So why not just write it
like this

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

and let that create the required index.

And then traditional unique constraints would fit into this as well:

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

The other problem I see with the syntax is that

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.