Extending range type operators to cope with elements

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

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

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.

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/
------------------------------------------------------------

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.

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/
------------------------------------------------------------

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

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

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:

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]https://commitfest.postgresql.org/30/ to ensure we don't
lose track of it.

regards, tom lane

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

Hi,

thank you for your contribution.

I did notice that the cfbot [1]http://cfbot.cputube.org/esteban-zimanyi.html 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

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

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

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

On Sun, Feb 28, 2021 at 1:36 AM Justin Pryzby <pryzby@telsasoft.com> 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

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&gt;

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