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

Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> I am updating the syntax to be:

> CONSTRAINT foo_constr
> EXCLUSION (a <op>, ...) { USING method | INDEX foo_idx };

I'm still acutely uncomfortable with using CONSTRAINT syntax for this.
It is not a constraint per standard, because it's not going to be
displayable in information_schema. Furthermore, by extending
standardized syntax you run the risk of being blindsided by future
additions to the standard.

> ... Peter had the following concern:

> "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." [1]

> I don't think that's a serious problem,

That objection is completely bogus. pg_dump does not, and AFAIR never
has, promised to emit everything in the CREATE TABLE command. It's far
more efficient and practical to emit indexes and constraints as separate
commands later, after the data has been loaded. In the case of say
foreign key constraints, it's absolutely necessary to do it separately,
else you can't implement circular constraint references. Besides, we
already have many cases where indexes have to be emitted separately
because they don't fit into the CONSTRAINT syntax: expression indexes
and nondefault opclasses to name two.

The point about being able to support multiple constraints with one
index is kind of interesting, but I don't actually think that that's
so useful that it should override all other considerations about what
syntax we should pick. I think we should drop the whole thing and
just treat this as an extension to the CREATE INDEX syntax.

regards, tom lane