Re: operator exclusion constraints - Mailing list pgsql-hackers

On Mon, 2009-11-02 at 07:38 +0000, Simon Riggs wrote:
> 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".

Yes, in that way, it's similar to a UNIQUE constraint, e.g.
UNIQUE (a, b). The more columns you add, the more permissive the
constraint.

> How would we use an OR conditional?

Specify multiple constraints.

> How would
> we express the wish to use a partial index? 

EXCLUSION (...) WHERE (...)

The perens are actually required around the predicate in this case, due
to syntactic problems.

> 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 <)

That would be a cool feature, unfortunately it won't work in the current
form. This constraint is only enforced on index insert -- imagine what
confusion would be caused when:

UPDATE foo SET third_column = 7 ...

If that's a HOT update, it wouldn't re-check the bid_price. If it turns
into a cold update, it would reject the update because the bid_price is
no longer the highest.

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

The above problem essentially means we only allow commutative operators,
which avoids this source of confusion.

Interestingly, reflexive operators aren't required. So, if <> is
searchable, you can have the opposite of unique: all values must be the
same. That might be interesting for something like:
 EXCLUSION(room CHECK WITH =,           during CHECK WITH &&,           student_grade CHECK WITH <>)

To ensure that a shared room isn't shared between students of different
grades. Not the most compelling use case, but I could imagine something
along these lines being useful.

Maybe a better example would involve sheep and wolves ;)

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

I'm not sure I understand this exactly. If the left or right is
explcitly a part of the tuple, I think it can be done with unique.

If not, and you're looking for a maximum of two tuples, you can see my
ill-fated extension to this feature here:

http://archives.postgresql.org/pgsql-hackers/2009-11/msg00016.php

As Tom pointed out, most use cases would not have a constant limit
throughout the table. If you do have such a use case, you can revive the
proposal.

> Not sure that if we submitted this to SQL
> Standard committee that it would be accepted as is.

There are implementation details bubbling up to the user-visible
behavior, and I share your concern. The SQL committee would never care
about these implementation details, and I wish we didn't have to,
either.

The machinism that I've employed searches (using a dirty snapshot) for
all of the physical tuples that cause a logical conflict with the
physical tuple currently being added. If found, it uses physical
information from those tuples, like visibility information, to determine
whether to wait, and on whom to wait. After waiting it may either
proceed or abort.

If we move closer to a nice, clean query to express the constraint, it
gets very difficult to tell which physical tuple is responsible for the
conflict. If we don't know what physical tuple is causing the conflict,
we have to serialize all writes.

Additionally, the more it looks like a query, the more we have to tell
users "follow this template" -- which will just lead to confusion and
disappointment for users who think we've implemented SQL ASSERT (which
we haven't).

Although the current syntax isn't great, it is declarative, and it does
allow a variety of constraints.

I certainly welcome ideas that will make a better trade-off here. At the
end, I just want a feature that can implement temporal keys.

Regards,Jeff Davis