Re: range_agg - Mailing list pgsql-hackers

On Tue, Jul 9, 2019 at 8:51 AM David Fetter <david@fetter.org> wrote:
> > - A multirange type is an extra thing you get when you define a range
> > (just like how you get a tstzrange[]). Therefore....
> > - I don't need separate commands to add/drop multirange types. You get
> > one when you define a range type, and if you drop a range type it gets
> > dropped automatically.
>
> Yay for fewer manual steps!

Thanks for taking a look and sharing your thoughts!

> > - You can have a multirange[].
>
> I can see how that would fall out of this, but I'm a little puzzled as
> to what people might use it for. Aggregates, maybe?

I don't know either, but I thought it was standard to define a T[] for
every T. Anyway it doesn't seem difficult.

> > - You can cast from a multirange to an array. The individual ranges
> > are always sorted in the result array.
>
> Is this so people can pick individual ranges out of the multirange,
> or...?

Yes. I want this for foreign keys actually, where I construct a
multirange and ask for just its first range.

> Speaking of casts, it's possible that a multirange is also a
> range. Would it make sense to have a cast from multirange to range?

Hmm, that seems strange to me. You don't cast from an array to one of
its elements. If we have subscripting, why use casting to get the
first element?

> > - You can cast from an array to a multirange but it will error if
> > there are overlaps (or not?).
>
> An alternative would be to canonicalize into non-overlapping ranges.
> There's some precedent for this in casts to JSONB. Maybe a function
> that isn't a cast should handle such things.

I agree it'd be nice to have both.

> > - Interesting functions:
> >   - multirange_length
>
> Is that the sum of the lengths of the ranges?  Are we guaranteeing a
> measure in addition to ordering on ranges now?

Just the number of disjoint ranges in the multirange.

> > - You can subscript a multirange like you do an array (? This could be
> > a function instead.)
>
> How would this play with the generic subscripting patch in flight?

I'm not aware of that patch but I guess I better check it out. :-)

> > - operators:
> >     - mr * r = r
> >     - r * mr = r
>
> Shouldn't the two above both yield multirange ? For example, if I
> understand correctly,

You're right! Thanks for the correction.

> >   - comparison
> >     - mr = mr
> >     - mr @> x
>
> x is in the domain of the (multi)range?

Yes. It's the scalar base type the range type is based on. I had in
mind the math/ML convention of `x` for scalar and `X` for
vector/matrix.

> >   - inverse operator?:
> >     - the inverse of {"[1,2)"} would be {"[null, 1)", "[2, null)"}.
>
> Is that the same as ["(∞, ∞)"] - {"[1,2)"}?

Yes.

> I seem to recall that the
> usual convention (at least in math) is to use intervals that are
> generally represented as open on the infinity side, but that might not
> fit how we do things.

I think it does, unless I'm misunderstanding?

> >     - not sure we want this or what the symbol should be. I don't like
> > -mr as an inverse because then mr - mr != mr ++ -mr.
>
> !mr , perhaps?

I like that suggestion. Honestly I'm not sure we even want an inverse,
but it's so important theoretically we should at least consider
whether it is appropriate here. Or maybe "inverse" is the wrong word
for this, or there is a different meaning it should have.

Thanks,
Paul