SEO

Ilya Kovalenko posted some code at in a thread starting at

http://archives.postgresql.org/pgsql...4/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


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

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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

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

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match