Re: WIP: generalized index constraints

On Mon, 2009-07-06 at 07:30 -0700, David Fetter wrote:
> > It would be useful to see a real example of what this can be used
> > for.
>
> Constraints like "these intervals can't overlap" would be one. It's
> handy in calendaring applications, for example.

Exactly, you already know my use case ;) My goal is a "temporal key",
where you can't have overlapping intervals of time, e.g. the constraint
"nobody can be two places at the same time".

> > I think it will be useful to separate the concepts of a constraint
> > from the concept of an index. It seems possible to have a UNIQUE
> > constraint that doesn't help at all in locating rows, just in
> > proving that the rows are unique.
>
> Interesting idea. Are you thinking of this in terms of things the
> planner can do once it knows a set is all distinct values, or...?

I think that's an orthogonal idea.

It's a good idea though, I would like the planner to be smarter about
those kinds of things. A simple example is that if a table has a
non-partial unique constraint anywhere, then "select * from foo union
select * from foo" can be transformed into "select * from
foo" (eliminating the expensive union).

Regards,
Jeff Davis