Re: 9.3 Pre-proposal: Range Merge Join

On Sun, 2012-04-15 at 23:18 -0700, Darren Duncan wrote:
> Your proposal makes me think of something similar which might be useful,
> INclusion constraints. As "exclusion constraints" might be thought of like a
> generalization of unique/key constraints, "inclusion constraints" are like a
> generalization of foreign key constraints. The "inclusion constraints"
> basically allow some comparison operator other than is-equal to test if values
> in one table match values in another table, and the constraint allows the former
> if the test results in true. An example of said inclusion test is whether the
> range in one table is contained in a range in another table. I assume/hope
> that, similarly, now that we have range types in 9.2, that the existing
> "exclusion constraints" can be used with range comparison operators.

Yes, Range Foreign Keys are one of the loose ends for Range Types that
I'd like to wrap up.

> As to your actual proposal, it sounds like a generalization of the relational
> join or set intersection operator where instead of comparing sets defined in
> terms of an enumeration of discrete values we are comparing sets defined by a
> range, which conceptually have infinite values depending on the data type the
> range is defined over. But if we're doing this, then it would seem to make
> sense to go further and see if we have set analogies for all of our relational
> or set operators, should we want to do work with non-discrete sets.
>
> Now this sounds interesting in theory, but I would also assume that these could
> be implemented by an extension in terms of existing normal relational operators,
> where each range value is a discrete value, combined with operators for unioning
> or differencing etc ranges. A relation of ranges effectively can represent a
> discontinuous range; in that case, the empty discontinuous range is also
> canonically representable by a relation with zero tuples.
>
> Jeff, I get the impression your proposal is partly about helping performance by
> supporting this internally, rather than one just defining it as a SQL function,
> am I right?

Per my proposal, the problem statement is that it's "slow", so speeding
it up is really the entire proposal ;)

Extending the syntax might be interesting as well, but I don't have a
proposal for that.

Regards,
Jeff Davis