Re: operator exclusion constraints

On Sun, 2009-11-01 at 15:42 -0800, Jeff Davis wrote:

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

Now that's a great example.

Looks like the classic "don't allow the same room to be booked more than
once at the same time".

It bothers me that we would have completely separate syntax for this
feature as opposed to normal SQL. It also doesn't make it easy to
interpret from the business statement to the implementation. Notice that
the "," above means "AND". How would we use an OR conditional? How would
we express the wish to use a partial index? 

How would I express a bidding rule: "Only allow bids that are better
than the highest bid so far"

EXCLUSION (item CHECK WITH =, bid_price CHECK WITH <)

Did I get the ">" the right way around?

How would I specify a tree that has only 2 down branches at any node,
'left' and 'right'?

> If you want to re-open the syntax discussion, we can 

I don't *want* to and I don't want to derail a good feature. But we'll
be looking at this for years and believe me if you introduce even a
minor bump you'll hear it repeated endlessly.

> Personally I think the current syntax is pretty good.

The feature sounds great, regrettably the syntax doesn't seem very
clean, as an objective observer. I apologise if this causes you trouble,
I have no axe to grind here. Not sure that if we submitted this to SQL
Standard committee that it would be accepted as is.

(You made a few other points which I regrettably skimmed over in my
reply).

-- Simon Riggs           www.2ndQuadrant.com