inet increment with int

Patrick Welche <prlw1@newn.cam.ac.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@newn.cam.ac.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@newn.cam.ac.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@newn.cam.ac.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@newn.cam.ac.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@candle.pha.pa.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@candle.pha.pa.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