From: David Fetter <david@fetter.org>
To: Paul Jungwirth <pj@illuminatedcomputing.com>
Cc: Pgsql Hackers <pgsql-hackers@postgresql.org>
Subject: Re: range_agg
Date: Sat, 4 May 2019 03:41:47 +0200
Message-ID: <20190504014147.GC28936@fetter.org> (raw)
In-Reply-To: <16d71dc8-34cf-5ebd-1ce5-ccd93c0a14f9@illuminatedcomputing.com>
References: <16d71dc8-34cf-5ebd-1ce5-ccd93c0a14f9@illuminatedcomputing.com>
On Fri, May 03, 2019 at 03:56:41PM -0700, Paul Jungwirth wrote:
> Hello,
>
> I wrote an extension to add a range_agg function with similar behavior to
> existing *_agg functions, and I'm wondering if folks would like to have it
> in core? Here is the repo: https://github.com/pjungwir/range_agg
>
> I'm also working on a patch for temporal foreign keys, and having range_agg
> would make the FK check easier and faster, which is why I'd like to get it
> added. But also it just seems useful, like array_agg, json_agg, etc.
>
> One question is how to aggregate ranges that would leave gaps and/or
> overlaps.
This suggests two different ways to extend ranges over aggregation:
one which is a union of (in general) disjoint intervals, two others
are a union of intervals, each of which has a weight. Please pardon
the ASCII art.
The aggregation of:
[1, 4)
[2, 5)
[8, 10)
could turn into:
{[1,5), [8, 10)} (union without weight)
{{[1,2),1}, {[2,4),2}, {[4,5),1}, {[8,10),1}} (strictly positive weights which don't (in general) cover the space)
{{[1,2),1}, {[2,4),2}, {[4,5),1}, {[5,8),0}, {[8,10),1}} (non-negative weights which guarantee the space is covered)
There is no principled reason to choose one over the others.
> What do people think? I plan to work on a patch regardless, so that I can
> use it for temporal FKs, but I'd appreciate some feedback on the "user
> interface".
I think the cases above, or at least the first two of them, should be
available. They could be called range_agg, weighted_range_agg, and
covering_range_agg.
Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: david@fetter.org, pj@illuminatedcomputing.com
Subject: Re: range_agg
In-Reply-To: <20190504014147.GC28936@fetter.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox