Re: proposal: operator exclusion constraints with cardinality

On Sun, 2009-11-01 at 23:09 -0500, Robert Haas wrote:
> Following that thought, in this particular case it seems like you could do:
>
> EXCLUSION (room CHECK WITH =,
> attendee CHECK WITH =,
> foobar CHECK WITH =,
> during CHECK WITH &&)
> and then also
> CHECK (foobar >= 1 AND foobar <= 30)

...

> Since you already have attendee as part of the
> constraint, I'm a little mystified as to what the quantity of 30 is
> supposed to represent,

Yes, that was wrong, attendee shouldn't have been in the constraint.

> but it any case it seems like you can get the
> effect with an extra field

That's one way to do it, but then you have to assign numbers to all
attendees, which creates a new contention problem.

If using discrete time slots aligned to the hour, for instance, you
could increment a field in the "room" table every time an attendee was
added, and then use a CHECK constraint to limit that field. The fact
that updates follow the chain of concurrent updates makes that work
nicely.

That doesn't seem to work for general overlapping and unaligned time
periods, however.

Regards,
Jeff Davis