Thread: Extending range type operators to cope with elements

Extending range type operators to cope with elements

From
Esteban Zimanyi
Date:
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

Re: Extending range type operators to cope with elements

From
Tomas Vondra
Date:
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 



Re: Extending range type operators to cope with elements

From
Corey Huinker
Date:

>- @> 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.

Re: Extending range type operators to cope with elements

From
Esteban Zimanyi
Date:

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: ezimanyi@ulb.ac.be
Internet: http://code.ulb.ac.be/
------------------------------------------------------------

Fwd: Extending range type operators to cope with elements

From
Esteban Zimanyi
Date:
> 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: ezimanyi@ulb.ac.be
Internet: http://code.ulb.ac.be/
------------------------------------------------------------

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

From
David Fetter
Date:
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



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

From
Esteban Zimanyi
Date:
On Tue, Sep 17, 2019 at 5:18 AM David Fetter <david@fetter.org> 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
Attachment

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

From
Esteban Zimanyi
Date:
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: ezimanyi@ulb.ac.be
Internet: http://cs.ulb.ac.be/members/esteban/
------------------------------------------------------------

On Tue, Sep 17, 2019 at 5:18 AM David Fetter <david@fetter.org> 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
Attachment

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

From
Tom Lane
Date:
Esteban Zimanyi <ezimanyi@ulb.ac.be> 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/



Re: Extending range type operators to cope with elements

From
Georgios Kokolatos
Date:
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

Re: Extending range type operators to cope with elements

From
Tomas Vondra
Date:
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

Attachment

Re: Extending range type operators to cope with elements

From
Anastasia Lubennikova
Date:
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




Re: Extending range type operators to cope with elements

From
David Steele
Date:
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
david@pgmasters.net