Re: operator exclusion constraints - Mailing list pgsql-hackers

On Sun, 2009-11-01 at 22:42 +0000, Simon Riggs wrote:
> After reading the docs in the patch I don't believe you're going to all
> this trouble to ensure two circles don't overlap. Can you give some
> better examples of what you're trying to achieve and why anyone else
> would care? (I'm busy, so are others).

Non-overlapping periods of time. I couldn't document that, because the
PERIOD type doesn't exist in core (yet).

> I can probably guess, but my feeling is I shouldn't have to. I feel like
> this might be a truly great feature, but I'm worried that either it
> isn't at all or it is and yet will be overlooked. Does this project link
> in with other planned developments in various plugins? 

Absolutely:
http://archives.postgresql.org/pgsql-hackers/2009-10/msg01813.php

> The current patch writes the syntax like this
>   EXCLUSION USING gist (c CHECK WITH &&)
> makes it look like a table constraint, yet it clearly refers to a single
> column. That looks very clumsy to read, to my eyes.

It is a table constraint, and you can specify multiple columns. I don't
see much point in allowing this as a column constraint, because that's
not the typical case.

Most of the time, there will be two columns like: EXCLUSION(room_number CHECK WITH =, during CHECK WITH &&)

In other words, usually there is both a resource and a period of time
for the reservation. It is of course possible to use it for a column
constraint, and I'll add syntax if there's demand for it.

> The syntax be easier to read if it was stated as a comparison
> e.g. in the circle example
>   CHECK ( NOT (NEW.c && c)) USING GIST
> where NEW is the incoming row.
> This is similar to the way I would write the constraint if I wanted to
> ensure the values in two columns did not match/overlap etc
>   CHECK ( NOT (col1 && col2))
> and is also not such a radical departure from existing SQL Standard
> syntax.

We've already had very extensive discussion about the syntax. Your idea
is interesting, but I agree with Tom that it's not ideal, either. NEW
might be OK, but Tom's observation about the new meaning of "c" (ranging
over the entire table) is a compelling problem.

Consider: CHECK ( NOT (NEW.c && c OR c && d))

The right side of the OR could either mean "c overlaps d" or "forall c,
d: c overlaps d". I can't come up with a way to treat "c" consistently
between the left and right side of the OR (put another way, is "c" free
or bound?).

We could allow subselects in CHECK, but it's difficult to infer from
arbitrary queries what I can enforce with an operator exclusion
constraint, and what I can't.

If you want to re-open the syntax discussion, we can (right is better
than soon). However, it is late in the cycle, so I'll need something
very clear quite soon if this is going to make it into 8.5.

Personally I think the current syntax is pretty good.

Regards,Jeff Davis