Обсуждение: proposal: operator exclusion constraints with cardinality

I have completed the open issues (that I'm aware of) for operator
exclusion constraints:

http://archives.postgresql.org/message-id/1257101600.27737.159.camel@jdavis

I'd now like to propose an extension to that feature: cardinality
limits.

It could go something like this (syntax still open for discussion, for
illustration only):
 EXCLUSION (room     CHECK WITH =,            attendee CHECK WITH =,            during   CHECK WITH &&)   CARDINALITY
30

To mean that at most 30 attendees can be signed up for the same room at
overlapping times.

I have hacked up a basic prototype just to make sure the semantics can
work reasonably well. I haven't implemented the syntax or catalog
changes yet, but the basic semantics with a hard-coded cardinality seem
to hold up.

Thoughts?

Regards,Jeff Davis

Jeff Davis <pgsql@j-davis.com> writes:
> It could go something like this (syntax still open for discussion, for
> illustration only):

>   EXCLUSION (room     CHECK WITH =,
>              attendee CHECK WITH =,
>              during   CHECK WITH &&)
>     CARDINALITY 30

There's an old design principle that says "the only good numbers in
computer science are 0, 1, and N" -- that is, if you need to allow more
than one of something, you should have no hard-wired upper bound on
how many of them you allow.  The reason that meme comes to mind is that
I'm having difficulty seeing applications for this type of constraint.
I can certainly believe that people might like to enforce constraints
like "no more than N people are signed up for class X".  The problem is
that they won't want the *same* N for every class, and that's what this
constraint design seems to require.  What they'll want is N drawn from
some other table entry about the size of the classroom.  If you can't
support that then the design isn't fully baked yet.

(Of course, the reason UNIQUE constraints are good is that they
correspond to the number 1 ;-))
        regards, tom lane

On Sun, Nov 1, 2009 at 10:49 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Jeff Davis <pgsql@j-davis.com> writes:
>> It could go something like this (syntax still open for discussion, for
>> illustration only):
>
>>   EXCLUSION (room     CHECK WITH =,
>>              attendee CHECK WITH =,
>>              during   CHECK WITH &&)
>>     CARDINALITY 30
>
> There's an old design principle that says "the only good numbers in
> computer science are 0, 1, and N" -- that is, if you need to allow more
> than one of something, you should have no hard-wired upper bound on
> how many of them you allow.  The reason that meme comes to mind is that
> I'm having difficulty seeing applications for this type of constraint.
> I can certainly believe that people might like to enforce constraints
> like "no more than N people are signed up for class X".  The problem is
> that they won't want the *same* N for every class, and that's what this
> constraint design seems to require.  What they'll want is N drawn from
> some other table entry about the size of the classroom.  If you can't
> support that then the design isn't fully baked yet.
>
> (Of course, the reason UNIQUE constraints are good is that they
> correspond to the number 1 ;-))

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)

I'm a bit baffled by what this constraint is trying to represent,
actually.  I would have thought that the example would be room and
during constrained to a quantity of 30, and the solution would be to
have attendee.  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, but it any case it seems like you can get the
effect with an extra field - which also allows you to do things like
variable room-sizes (by setting up triggers that arrange to copy the
room size into a column of your scheduling table so that you can then
check that the attendee number is less than the room size).

...Robert

On Sun, 2009-11-01 at 22:49 -0500, Tom Lane wrote:
> you should have no hard-wired upper bound on
> how many of them you allow.

You're right. I saw something that looked easy to implement, but in
practice it wouldn't be very useful.

Regards,Jeff Davis

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