Re: Range Types and extensions

On Sun, Jun 5, 2011 at 1:59 PM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> In the several talks that I've given, a common question is related to
> "multiranges" (ranges with holes). These get a little complex, and I
> don't have a complete answer. However, multiranges can be approximated
> with ordered arrays of non-overlapping, non-adjacent ranges. If someone
> wants to take it upon themselves to develop a set of operators here,
> that would be great -- but without ANYRANGE the operators would be
> unmanageable.
>
> 2. Documentation and Tests
> --------------------------
> Let's say we take a minimalist view, and only have ANYRANGE and CREATE
> TYPE ... AS RANGE in core; and leave the rest as an extension.
>
> What exactly would the documentation say? I think it would be even more
> hypothetical and abstract than the documentation for Exclusion
> Constraints. So, there is a certain documentation advantage to having at
> least enough functionality to allow someone to try out the feature.
>
> And the tests for such a minimalist feature would be a significant
> challenge -- what do we do there? Get pg_regress to load the extension
> from PGXN?
>
>
> 3. Quality
> ----------
> PostgreSQL has a great reputation for quality, and for good reason. But
> extensions don't follow the same quality-control standards; and even if
> some do, there is no visible stamp of approval. So, to ask someone to
> use an extension means that they have to evaluate the quality for
> themselves, which is a pretty high barrier.
>
> Since PGXN (thanks David Wheeler) and EXTENSIONs (thanks Dmitri) solve
> many of the other issues, quality control is one of the biggest ones
> remaining. I still get questions about when the temporal type will be
> "in core", and I think this is why.
>
> I don't think this is a good excuse to put it in core though. We need to
> solve this problem, and the best way to start is by getting
> well-reviewed, high-quality extensions out there.
>
>
> 4. Future work -- RANGE KEY, RANGE FOREIGN KEY, RANGE MERGE JOIN, etc.
> ---------------------------------
> There are a few aspects of range types that aren't in the first patch,
> but are fairly obvious follow-up additions. These will require some
> knowledge about ranges in the backend, like finding the "overlaps"
> operator for a range. The current patch provides this knowledge by
> providing a built-in overlaps operator for ANYRANGE. This would be a
> non-issue if we had a good type interface system (that works on
> polymorphic types) -- we could just have a built-in "range" interface,
> and the range extension could add "&&" as the range interface's overlaps
> operator for the type ANYRANGE.
>
> =================================
>
> So, where on this spectrum should range types fall? I think the most
> minimalist would be to only support #1 (and the necessary type IO
> functions); and leave all other functions, operators, and opclasses to
> an extension. That has a lot of appeal, but I don't think we can ignore
> the challenges above.
>
> On the other hand, trying to make it a complete feature in core has
> challenges as well. For instance, even with Range Types, Exclusion
> Constraints aren't practical out-of-the-box unless we also have
> BTree-GiST in core. So there's a snowball effect.
>
> There might also be some middle ground, where its like the minimalist
> approach, but with a few very basic constructors and accessors. That
> would at least make it easier to test, but then to be actually useful
> (with index support, operators, fancy functions, etc.) you'd need the
> extension.
>
> Thoughts?

ISTM (I haven't followed all the lead up so apologies if this is
already covered) a range is a 3rd pseudo 'container' type (the other
two being composites and arrays). Do you see:

*) being able to make arrays of ranges/ranges of arrays?
*) range of composites?

I vote for at minimum the type itself and ANYRANGE to be in core.
>From there you could make it like arrays where the range type is
automatically generated for each POD type. I would consider that for
sure on basis of simplicity in user-land unless all the extra types
and operators are a performance hit.

A clean and highly usable implementation in the type system in the
spirit of arrays would be fantastic. I'm particularly interested in
hypothetical constructor/destructor and in/out mechanics...an 'unnest'
like function, a range(a,b,c) that does as row(a,b,c) does, etc,
especially if you can work it out so that everything is not hammered
through textual processing.

merlin