Re: operator exclusion constraints [was: generalized index constraints]

On Sun, 2009-09-20 at 17:54 -0400, Tom Lane wrote:
> Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> > 1. Constraint syntax, part of CREATE/ALTER TABLE:
>
> > [CONSTRAINT <name>] EXCLUSION (<expr> OPERATOR <op>, ...)
>
> Have you actually built this grammar? I don't think it avoids the
> problem, because OPERATOR is possible within a_expr.
>
> Also, don't forget the possibility of wanting a nondefault opclass.
> (I'm wondering a bit if anyone will want a WHERE clause, too, though
> adding that later shouldn't pose any big syntactic obstacles.)

I suppose I should just allow any index_elem. The only way I was able to
make the grammar for that work is by using a reserved keyword. The
possibilities that make the most sense to me are:

index_elem WITH any_operator
index_elem WITH OPERATOR any_operator
index_elem CHECK any_operator
index_elem CHECK OPERATOR any_operator

Do any of these look acceptable?

Also, I should allow for a tablespace, as well. Because it's specified
with UNIQUE as "USING INDEX TABLESPACE foo", to be consistent I need to
move the "USING method" ahead like so:

CONSTRAINT <name> EXCLUSION [USING method]
(<index_elem> CHECK <op>, ...)
[USING INDEX TABLESPACE <tablespacename>]
[DEFERRABLE | NOT DEFERRABLE ]
[ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

Having the method before the attribute list makes it more consistent
with CREATE INDEX, as well.

> That's really a separate issue, but I think we need to do something to
> make it more consistent. My first thought is that anything made
> via CONSTRAINT syntax ought to be copied by LIKE INCLUDING CONSTRAINTS,
> while LIKE INCLUDING INDEXES should copy anything you made via CREATE
> INDEX.

Works for me.

> But note this assumes that there is a clear distinction between
> the two. The constraint-depending-on-index design that you started
> with would not permit such a rule, or at least it would mean that
> INCLUDING CONSTRAINTS EXCLUDING INDEXES would have failure cases.

Sounds reasonable. If we decide to support that kind of thing in the
future, we can handle that case somehow (an error seems reasonable to
me).

Regards,
Jeff Davis