Extending range type operators to cope with elements

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
15 messages Options
Reply | Threaded
Open this post in threaded view
|

Extending range type operators to cope with elements

Esteban Zimanyi
Dear all

While developing MobilityDB we needed to extend the range type operators so they cope with elements. In the same way that currently the range types support both
- @> contains range/element
- <@ element/range is contained by
we extended the left (<<), overleft (&<), right (>>), and overright (&>) operators so they can cope with both elements and ranges at the left- or right-hand side. These can be seen in github
https://github.com/ULB-CoDE-WIT/MobilityDB/blob/master/src/rangetypes_ext.c

If you think that these extensions could be useful for the community at large, I can prepare a PR. Please let me know.

Esteban
Reply | Threaded
Open this post in threaded view
|

Re: Extending range type operators to cope with elements

Tomas Vondra-4
On Fri, Sep 13, 2019 at 08:50:18AM +0200, Esteban Zimanyi wrote:

>Dear all
>
>While developing MobilityDB we needed to extend the range type operators so
>they cope with elements. In the same way that currently the range types
>support both
>- @> contains range/element
>- <@ element/range is contained by
>we extended the left (<<), overleft (&<), right (>>), and overright (&>)
>operators so they can cope with both elements and ranges at the left- or
>right-hand side. These can be seen in github
>https://github.com/ULB-CoDE-WIT/MobilityDB/blob/master/src/rangetypes_ext.c
>
>If you think that these extensions could be useful for the community at
>large, I can prepare a PR. Please let me know.
>

Well, we don't really use pull requests, but other than that I don't see
why not to at least consider such improvement.

I'm not a heavy user or range types, so I can't really judge how useful
that is in practice, but it seems like a fairly natural extension of the
existing operators. I mean, if I understand it correctly, the proposed
behavior is equal to treating the element as a "collapsed range".


regards

--
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


Reply | Threaded
Open this post in threaded view
|

Re: Extending range type operators to cope with elements

Corey Huinker

>- @> contains range/element
>- <@ element/range is contained by


I'm not a heavy user or range types, so I can't really judge how useful
that is in practice, but it seems like a fairly natural extension of the
existing operators. I mean, if I understand it correctly, the proposed
behavior is equal to treating the element as a "collapsed range".

I used to give a talk on ranges and partitioning, prior to postgresql getting native partitioning (see: https://wiki.postgresql.org/images/1/1b/Ranges%2C_Partitioning_and_Limitations.pdf )
In that talk, I mention the need for exactly these operators, specifically for an extension called range_partitioning which had some logic for "If I were to insert a row with this value, what partition would it end up in?" which allowed for a subsequent COPY operation directly to that partition. That logic essentially binary-searched a series of ranges, so it needed an "elem <@ range" as well as << and >>.

Yes, constructing a collapsed range was the work-around I used in the absence of real functions.

That extension has been replaced by real table partitioning and the planner itself now does similar logic for partition pruning.

So yes, I've had a need for those operators in the past. What I don't know is whether adding these functions will be worth the catalog clutter.

Reply | Threaded
Open this post in threaded view
|

Re: Extending range type operators to cope with elements

Esteban Zimanyi-2

So yes, I've had a need for those operators in the past. What I don't know is whether adding these functions will be worth the catalog clutter.

The operators are tested and running within MobilityDB. It concerns lines 231-657 for the C code in file
and lines 32-248 for the SQL code in file

Since you don't really use PR, please let me know whether I can be of any help.

Regards

Esteban

--
------------------------------------------------------------
Prof. Esteban Zimanyi
Department of Computer & Decision Engineering  (CoDE) CP 165/15   
Universite Libre de Bruxelles           
Avenue F. D. Roosevelt 50               
B-1050 Brussels, Belgium                
fax: + 32.2.650.47.13
tel: + 32.2.650.31.85
e-mail: [hidden email]
Internet: http://code.ulb.ac.be/
------------------------------------------------------------
Reply | Threaded
Open this post in threaded view
|

Fwd: Extending range type operators to cope with elements

Esteban Zimanyi
> So yes, I've had a need for those operators in the past. What I don't know is whether adding these functions will be worth the catalog clutter.

The operators are tested and running within MobilityDB. It concerns lines 231-657 for the C code in file

Since you don't really use PR, please let me know whether I can be of any help.

Regards
Esteban

--
------------------------------------------------------------
Prof. Esteban Zimanyi
Department of Computer & Decision Engineering  (CoDE) CP 165/15   
Universite Libre de Bruxelles           
Avenue F. D. Roosevelt 50               
B-1050 Brussels, Belgium                
fax: + 32.2.650.47.13
tel: + 32.2.650.31.85
e-mail: [hidden email]
Internet: http://code.ulb.ac.be/
------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Fwd: Extending range type operators to cope with elements

David Fetter
On Sun, Sep 15, 2019 at 04:30:52PM +0200, Esteban Zimanyi wrote:

> > So yes, I've had a need for those operators in the past. What I don't
> know is whether adding these functions will be worth the catalog clutter.
>
> The operators are tested and running within MobilityDB. It concerns lines
> 231-657 for the C code in file
> https://github.com/ULB-CoDE-WIT/MobilityDB/blob/master/src/rangetypes_ext.c
>
> and lines 32-248 for the SQL code in file
> https://github.com/ULB-CoDE-WIT/MobilityDB/blob/master/src/sql/07_rangetypes_ext.in.sql
>
> Since you don't really use PR, please let me know whether I can be of
> any help.

It's not done by pull request at this time. Instead, it is done by sending
patches to this mailing list.

http://wiki.postgresql.org/wiki/Development_information
http://wiki.postgresql.org/wiki/Submitting_a_Patch
https://wiki.postgresql.org/wiki/So,_you_want_to_be_a_developer%3F
http://www.interdb.jp/pg/

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 | Threaded
Open this post in threaded view
|

Re: Fwd: Extending range type operators to cope with elements

Esteban Zimanyi
On Tue, Sep 17, 2019 at 5:18 AM David Fetter <[hidden email]> wrote:
> It's not done by pull request at this time. Instead, it is done by sending
> patches to this mailing list.

Dear all

You will find enclosed the patch that extends the range type operators so they cope with elements.

Any comments most welcome.

Esteban

zimanyi.patch (45K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Fwd: Extending range type operators to cope with elements

Esteban Zimanyi
In reply to this post by David Fetter
Dear all

After a long time (as you can imagine, this year everything has been upside down ...), you will find enclosed the patch for extending the range operators so they can cope with range <op> element and element <op> range in addition to the existing range <op> range.

Best regards

Esteban

------------------------------------------------------------
Prof. Esteban Zimanyi
Department of Computer & Decision Engineering  (CoDE) CP 165/15   
Universite Libre de Bruxelles           
Avenue F. D. Roosevelt 50               
B-1050 Brussels, Belgium                
fax: + 32.2.650.47.13
tel: + 32.2.650.31.85
e-mail: [hidden email]
Internet: http://cs.ulb.ac.be/members/esteban/
------------------------------------------------------------

On Tue, Sep 17, 2019 at 5:18 AM David Fetter <[hidden email]> wrote:
On Sun, Sep 15, 2019 at 04:30:52PM +0200, Esteban Zimanyi wrote:
> > So yes, I've had a need for those operators in the past. What I don't
> know is whether adding these functions will be worth the catalog clutter.
>
> The operators are tested and running within MobilityDB. It concerns lines
> 231-657 for the C code in file
> https://github.com/MobilityDB/MobilityDB/blob/master/src/rangetypes_ext.c
>
> and lines 32-248 for the SQL code in file
> https://github.com/ULB-CoDE-WIT/MobilityDB/blob/master/src/sql/07_rangetypes_ext.in.sql
>
> Since you don't really use PR, please let me know whether I can be of
> any help.

It's not done by pull request at this time. Instead, it is done by sending
patches to this mailing list.

http://wiki.postgresql.org/wiki/Development_information
http://wiki.postgresql.org/wiki/Submitting_a_Patch
https://wiki.postgresql.org/wiki/So,_you_want_to_be_a_developer%3F
http://www.interdb.jp/pg/

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

range-ext.patch (48K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Fwd: Extending range type operators to cope with elements

Tom Lane-2
Esteban Zimanyi <[hidden email]> writes:
> After a long time (as you can imagine, this year everything has been upside
> down ...), you will find enclosed the patch for extending the range
> operators so they can cope with range <op> element and element <op> range
> in addition to the existing range <op> range.

Cool.  Please add this to the open commitfest list [1] to ensure we don't
lose track of it.

                        regards, tom lane

[1] https://commitfest.postgresql.org/30/


Reply | Threaded
Open this post in threaded view
|

Re: Extending range type operators to cope with elements

Georgios Kokolatos
Hi,

thank you for your contribution.

I did notice that the cfbot [1] is failing for this patch.
Please try to address the issues if you can for the upcoming commitfest.

Cheers,
//Georgios

[1] http://cfbot.cputube.org/esteban-zimanyi.html
Reply | Threaded
Open this post in threaded view
|

Re: Extending range type operators to cope with elements

Tomas Vondra-4
Hi,

On Fri, Oct 30, 2020 at 04:01:27PM +0000, Georgios Kokolatos wrote:
>Hi,
>
>thank you for your contribution.
>
>I did notice that the cfbot [1] is failing for this patch.
>Please try to address the issues if you can for the upcoming commitfest.
>

I took a look at the patch today - the regression failure was trivial,
the expected output for one query was added to the wrong place, a couple
lines off the proper place. Attached is an updated version of the patch,
fixing that.

I also reviewed the code - it seems pretty clean and in line with the
surrounding code in rangetypes.c. Good job Esteban! I'll do a bit more
review next week, and I'll see if I can get it committed.

regards

--
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

0001-range-ext-20201030.patch (37K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Extending range type operators to cope with elements

Anastasia Lubennikova
On 31.10.2020 01:08, Tomas Vondra wrote:

> Hi,
>
> On Fri, Oct 30, 2020 at 04:01:27PM +0000, Georgios Kokolatos wrote:
>> Hi,
>>
>> thank you for your contribution.
>>
>> I did notice that the cfbot [1] is failing for this patch.
>> Please try to address the issues if you can for the upcoming commitfest.
>>
>
> I took a look at the patch today - the regression failure was trivial,
> the expected output for one query was added to the wrong place, a couple
> lines off the proper place. Attached is an updated version of the patch,
> fixing that.
>
> I also reviewed the code - it seems pretty clean and in line with the
> surrounding code in rangetypes.c. Good job Esteban! I'll do a bit more
> review next week, and I'll see if I can get it committed.
>
> regards
>

CFM reminder. Just in case you forgot about this thread)
The commitfest is heading to the end. Tomas, will you have time to push
this patch?

The patch still applies and passes all cfbot checks. I also took a quick
look at the code and everything looks good to me.

--
Anastasia Lubennikova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company



Reply | Threaded
Open this post in threaded view
|

Re: Extending range type operators to cope with elements

Justin Pryzby
In reply to this post by Tomas Vondra-4
On Fri, Oct 30, 2020 at 11:08:19PM +0100, Tomas Vondra wrote:

> Hi,
>
> +      <row>
> +       <entry role="func_table_entry"><para role="func_signature">
> +        <type>anyelement</type> <literal>&gt;&gt;</literal> <type>anyrange</type>
> +        <returnvalue>boolean</returnvalue>
> +       </para>
> +       <para>
> +        Is the element strictly right of the element?
> +       </para>

should say "of the range" ?

> +++ b/src/backend/utils/adt/rangetypes.c

> + /* An empty range is neither left nor right any other range */
> + /* An empty range is neither left nor right any element */
> + /* An empty range is neither left nor right any other range */
> + /* An empty range is neither left nor right any element */
> + /* An empty range is neither left nor right any element */
> + /* An empty range is neither left nor right any element */

I these comments should all say ".. left nor right OF any ..."

--
Justin


Reply | Threaded
Open this post in threaded view
|

Re: Extending range type operators to cope with elements

Ibrar Ahmed-4


On Sun, Feb 28, 2021 at 1:36 AM Justin Pryzby <[hidden email]> wrote:
On Fri, Oct 30, 2020 at 11:08:19PM +0100, Tomas Vondra wrote:
> Hi,
>
> +      <row>
> +       <entry role="func_table_entry"><para role="func_signature">
> +        <type>anyelement</type> <literal>&gt;&gt;</literal> <type>anyrange</type>
> +        <returnvalue>boolean</returnvalue>
> +       </para>
> +       <para>
> +        Is the element strictly right of the element?
> +       </para>

should say "of the range" ?

> +++ b/src/backend/utils/adt/rangetypes.c

> +     /* An empty range is neither left nor right any other range */
> +     /* An empty range is neither left nor right any element */
> +     /* An empty range is neither left nor right any other range */
> +     /* An empty range is neither left nor right any element */
> +     /* An empty range is neither left nor right any element */
> +     /* An empty range is neither left nor right any element */

I these comments should all say ".. left nor right OF any ..."

--
Justin



This patch set no longer applies.

http://cfbot.cputube.org/patch_32_2747.log

Can we get a rebase? 

I am marking the patch "Waiting on Author"

--

Ibrar Ahmed
Reply | Threaded
Open this post in threaded view
|

Re: Extending range type operators to cope with elements

David Steele
On 3/4/21 6:11 AM, Ibrar Ahmed wrote:
>
> This patch set no longer applies.
>
> http://cfbot.cputube.org/patch_32_2747.log 
> <http://cfbot.cputube.org/patch_32_2747.log>
>
> Can we get a rebase?
>
> I am marking the patch "Waiting on Author"

This patch needs updates and a rebase and there has been no new patch
six months, so marking Returned with Feedback.

Please resubmit to the next CF when you have a new patch.

Regards,
--
-David
[hidden email]