Re: inet increment with int

Ilya Kovalenko posted some code at in a thread starting at

http://archives.postgresql.org/pgsql-hackers/2005-04/msg00417.php

which lead to the TODO item:

* Allow INET + INT4 to increment the host part of the address, or
throw an error on overflow

I think that the naively coded function attached does what is needed, e.g.,

CREATE OR REPLACE FUNCTION inet_inc(inet, int4)
RETURNS inet
AS '/tmp/inet.so','inet_inc'
LANGUAGE C STRICT;

CREATE OPERATOR + (
leftarg = inet,
rightarg = int4,
procedure = inet_inc
);

test=# select '192.168.0.1/24'::inet + 300;
ERROR: Increment (300) too big for network (/24)
test=# select '192.168.0.1/24'::inet + 254;
?column?
------------------
192.168.0.255/24
(1 row)

test=# select '192.168.0.1/24'::inet + 255;
ERROR: Increment (255) takes address (192.168.0.1) out of its network (/24)
test=# select '192.168.0.1/24'::inet + -2;
ERROR: Increment (-2) takes address (192.168.0.1) out of its network (/24)
test=# select '255.255.255.254/0'::inet + 2;
ERROR: Increment (2) takes address (255.255.255.254) out of its network (/0)

and just for fun:

create table list (
host inet
);

insert into list values ('192.168.0.1/24');
insert into list values ('192.168.0.2/24');
insert into list values ('192.168.0.4/24');
insert into list values ('192.168.0.5/24');
insert into list values ('192.168.0.6/24');
insert into list values ('192.168.0.8/24');
insert into list values ('192.168.0.9/24');
insert into list values ('192.168.0.10/24');
insert into list values ('192.168.1.1/24');
insert into list values ('192.168.1.3/24');

select host+1 from list
where host+1 <<= '192.168.1.0/24'
and not exists
( select 1
from list
where host=host+1
and host << '192.168.1.0/24' )
limit 1;

If you agree that this is the right thing, I can code it less
naively, (Ilya rightly uses ntohl/htonl), create the operator's
commutator, provide a patch which makes it a built-in, and some
obvious documentation.

Cheers,

Patrick

Patrick Welche <prlw1(at)newn(dot)cam(dot)ac(dot)uk> writes:
> * Allow INET + INT4 to increment the host part of the address, or
> throw an error on overflow

> I think that the naively coded function attached does what is needed, e.g.,

What happened to the IPv6 case? Also, I think you need to reject CIDR
inputs.

regards, tom lane

On Mon, Sep 05, 2005 at 03:02:55PM -0400, Tom Lane wrote:
> Patrick Welche <prlw1(at)newn(dot)cam(dot)ac(dot)uk> writes:
> > * Allow INET + INT4 to increment the host part of the address, or
> > throw an error on overflow
>
> > I think that the naively coded function attached does what is needed, e.g.,
>
> What happened to the IPv6 case?

My take on the thread is that the IPv6 case doesn't make sense, and the
int8 part was dropped from the TODO.

> Also, I think you need to reject CIDR inputs.

OK

Patrick

On Mon, Sep 05, 2005 at 08:10:16PM +0100, Patrick Welche wrote:
> On Mon, Sep 05, 2005 at 03:02:55PM -0400, Tom Lane wrote:
> > Patrick Welche <prlw1(at)newn(dot)cam(dot)ac(dot)uk> writes:
> > > * Allow INET + INT4 to increment the host part of the address, or
> > > throw an error on overflow
> >
> > > I think that the naively coded function attached does what is needed, e.g.,
> >
> > What happened to the IPv6 case?
>
> My take on the thread is that the IPv6 case doesn't make sense, and the
> int8 part was dropped from the TODO.
>
> > Also, I think you need to reject CIDR inputs.
>
> OK

Now with:

test=# select '192.168.0.0/24'::inet + 1;
ERROR: Trying to increment a network (192.168.0.0/24) rather than a host
test=# select '192.168.0.1/24'::inet + -1;
ERROR: Increment returns a network (192.168.0.0/24) rather than a host

Cheers,

Patrick

On 2005-09-06, Patrick Welche <prlw1(at)newn(dot)cam(dot)ac(dot)uk> wrote:
> Now with:
>
> test=# select '192.168.0.0/24'::inet + 1;
> ERROR: Trying to increment a network (192.168.0.0/24) rather than a host

What possible justification is there for this behaviour?

> test=# select '192.168.0.1/24'::inet + -1;
> ERROR: Increment returns a network (192.168.0.0/24) rather than a host

While I suspect I know where this idea came from, it is equally boneheaded
since it is making completely unwarranted assumptions about how inet
values are being used.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

On Wed, Sep 07, 2005 at 02:48:00AM -0000, Andrew - Supernews wrote:
> On 2005-09-06, Patrick Welche <prlw1(at)newn(dot)cam(dot)ac(dot)uk> wrote:
> > Now with:
> >
> > test=# select '192.168.0.0/24'::inet + 1;
> > ERROR: Trying to increment a network (192.168.0.0/24) rather than a host
>
> What possible justification is there for this behaviour?
>
> > test=# select '192.168.0.1/24'::inet + -1;
> > ERROR: Increment returns a network (192.168.0.0/24) rather than a host
>
> While I suspect I know where this idea came from, it is equally boneheaded
> since it is making completely unwarranted assumptions about how inet
> values are being used.

So, back to original version?

Comments anyone?

Patrick

Patrick Welche wrote:
>Comments anyone?

Is incrementing an inet address a valid thing to do, or is its
meaning too open to interpretation?

How about either a pair of functions, one for incrementing the
network and another for the host, or a combined function that allows
you to work with both parts in one go?

Sam

This has been saved for the 8.2 release:

http://momjian.postgresql.org/cgi-bin/pgpatches_hold

---------------------------------------------------------------------------

Patrick Welche wrote:
> Ilya Kovalenko posted some code at in a thread starting at
>
> http://archives.postgresql.org/pgsql-hackers/2005-04/msg00417.php
>
> which lead to the TODO item:
>
> * Allow INET + INT4 to increment the host part of the address, or
> throw an error on overflow
>
> I think that the naively coded function attached does what is needed, e.g.,
>
> CREATE OR REPLACE FUNCTION inet_inc(inet, int4)
> RETURNS inet
> AS '/tmp/inet.so','inet_inc'
> LANGUAGE C STRICT;
>
> CREATE OPERATOR + (
> leftarg = inet,
> rightarg = int4,
> procedure = inet_inc
> );
>
> test=# select '192.168.0.1/24'::inet + 300;
> ERROR: Increment (300) too big for network (/24)
> test=# select '192.168.0.1/24'::inet + 254;
> ?column?
> ------------------
> 192.168.0.255/24
> (1 row)
>
> test=# select '192.168.0.1/24'::inet + 255;
> ERROR: Increment (255) takes address (192.168.0.1) out of its network (/24)
> test=# select '192.168.0.1/24'::inet + -2;
> ERROR: Increment (-2) takes address (192.168.0.1) out of its network (/24)
> test=# select '255.255.255.254/0'::inet + 2;
> ERROR: Increment (2) takes address (255.255.255.254) out of its network (/0)
>
> and just for fun:
>
> create table list (
> host inet
> );
>
> insert into list values ('192.168.0.1/24');
> insert into list values ('192.168.0.2/24');
> insert into list values ('192.168.0.4/24');
> insert into list values ('192.168.0.5/24');
> insert into list values ('192.168.0.6/24');
> insert into list values ('192.168.0.8/24');
> insert into list values ('192.168.0.9/24');
> insert into list values ('192.168.0.10/24');
> insert into list values ('192.168.1.1/24');
> insert into list values ('192.168.1.3/24');
>
> select host+1 from list
> where host+1 <<= '192.168.1.0/24'
> and not exists
> ( select 1
> from list
> where host=host+1
> and host << '192.168.1.0/24' )
> limit 1;
>
>
>
> If you agree that this is the right thing, I can code it less
> naively, (Ilya rightly uses ntohl/htonl), create the operator's
> commutator, provide a patch which makes it a built-in, and some
> obvious documentation.
>
> Cheers,
>
> Patrick

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Fri, Sep 16, 2005 at 06:36:56AM -0400, Bruce Momjian wrote:
>
> This has been saved for the 8.2 release:

It isn't actually a patch for application yet ;-) It is the function in
a state that is easy to test. I take it that as I have basically had
no comments back, I will just go ahead and make a patch for the
function as a built-in...

Cheers,

Patrick

Patrick Welche wrote:
> On Fri, Sep 16, 2005 at 06:36:56AM -0400, Bruce Momjian wrote:
> >
> > This has been saved for the 8.2 release:
>
> It isn't actually a patch for application yet ;-) It is the function in
> a state that is easy to test. I take it that as I have basically had
> no comments back, I will just go ahead and make a patch for the
> function as a built-in...

Right.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

FYI, 8.2 will have this and more based on this applied patch:

Add INET/CIDR operators: and, or, not, plus int8, minus int8, and inet
minus inet.

Stephen R. van den Berg

---------------------------------------------------------------------------

Patrick Welche wrote:
> Ilya Kovalenko posted some code at in a thread starting at
>
> http://archives.postgresql.org/pgsql-hackers/2005-04/msg00417.php
>
> which lead to the TODO item:
>
> * Allow INET + INT4 to increment the host part of the address, or
> throw an error on overflow
>
> I think that the naively coded function attached does what is needed, e.g.,
>
> CREATE OR REPLACE FUNCTION inet_inc(inet, int4)
> RETURNS inet
> AS '/tmp/inet.so','inet_inc'
> LANGUAGE C STRICT;
>
> CREATE OPERATOR + (
> leftarg = inet,
> rightarg = int4,
> procedure = inet_inc
> );
>
> test=# select '192.168.0.1/24'::inet + 300;
> ERROR: Increment (300) too big for network (/24)
> test=# select '192.168.0.1/24'::inet + 254;
> ?column?
> ------------------
> 192.168.0.255/24
> (1 row)
>
> test=# select '192.168.0.1/24'::inet + 255;
> ERROR: Increment (255) takes address (192.168.0.1) out of its network (/24)
> test=# select '192.168.0.1/24'::inet + -2;
> ERROR: Increment (-2) takes address (192.168.0.1) out of its network (/24)
> test=# select '255.255.255.254/0'::inet + 2;
> ERROR: Increment (2) takes address (255.255.255.254) out of its network (/0)
>
> and just for fun:
>
> create table list (
> host inet
> );
>
> insert into list values ('192.168.0.1/24');
> insert into list values ('192.168.0.2/24');
> insert into list values ('192.168.0.4/24');
> insert into list values ('192.168.0.5/24');
> insert into list values ('192.168.0.6/24');
> insert into list values ('192.168.0.8/24');
> insert into list values ('192.168.0.9/24');
> insert into list values ('192.168.0.10/24');
> insert into list values ('192.168.1.1/24');
> insert into list values ('192.168.1.3/24');
>
> select host+1 from list
> where host+1 <<= '192.168.1.0/24'
> and not exists
> ( select 1
> from list
> where host=host+1
> and host << '192.168.1.0/24' )
> limit 1;
>
>
>
> If you agree that this is the right thing, I can code it less
> naively, (Ilya rightly uses ntohl/htonl), create the operator's
> commutator, provide a patch which makes it a built-in, and some
> obvious documentation.
>
> Cheers,
>
> Patrick

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Sat, Apr 29, 2006 at 10:24:48PM -0400, Bruce Momjian wrote:
>
> FYI, 8.2 will have this and more based on this applied patch:
>
> Add INET/CIDR operators: and, or, not, plus int8, minus int8, and inet
> minus inet.

I know, I'm already using it :-)

Thanks,

Patrick