Re: Range Types and extensions - Mailing list pgsql-hackers

On Sun, Jun 5, 2011 at 1:59 PM, Jeff Davis <pgsql@j-davis.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