Thread: inet increment w/ int8

From:
"Ilya A. Kovalenko"
Date:

Greetings,
 I suggest function for "inet" increment w/ int8 (signed).

FUNCTION inet_inc(int, int8) RETURNS inet

Function, useful for making address pools (using also
existing "inet" compare functions to trap boundaries).

Notes: This version lets address wrap around 0-*ff boundary. Uses couple of non-POSIX functions - betoh64() and
htobe64()Tested on i386 with OpenBSD 3.7 PostgreSQL 8.0.2
 

-----------------------------------------------------
#include <sys/types.h>
#include <sys/socket.h>
#include <netinet/in.h>
#include <arpa/inet.h>

#include "postgres.h"                   /* general Postgres declarations */

#include "fmgr.h"                       /* for argument/result macros */
#include "utils/inet.h"

Datum           inet_inc(PG_FUNCTION_ARGS);

//------ stolen from backend/utils/adt/network.c --------

#define ip_family(inetptr) \       (((inet_struct *)VARDATA(inetptr))->family)
#define ip_bits(inetptr) \       (((inet_struct *)VARDATA(inetptr))->bits)
#define ip_type(inetptr) \       (((inet_struct *)VARDATA(inetptr))->type)
#define ip_addr(inetptr) \       (((inet_struct *)VARDATA(inetptr))->ipaddr)
#define ip_maxbits(inetptr) \       (ip_family(inetptr) == PGSQL_AF_INET ? 32 : 128)

static int
ip_addrsize(inet *inetptr)
{       switch (ip_family(inetptr))       {               case PGSQL_AF_INET:                       return 4;
   case PGSQL_AF_INET6:                       return 16;               default:                       return 0;
}
}
//-------------------------------------------------------

PG_FUNCTION_INFO_V1(inet_inc);

Datum
inet_inc(PG_FUNCTION_ARGS)
{  inet    *src = PG_GETARG_INET_P(0);  int64    arg = PG_GETARG_INT64(1);  inet    *dst;  uint64   wsp;

// allocate destination structure  dst = (inet *) palloc0(VARHDRSZ + sizeof(inet_struct));

// copy to destination  *((inet_struct *)VARDATA(dst)) = *((inet_struct *)VARDATA(src));
  if (ip_family(dst) == PGSQL_AF_INET)  {
// Increment v4 address w/ item truncated to 32 bits     *((uint32*)(ip_addr(dst))) =
htonl(ntohl(*((int32*)(ip_addr(dst))))+ (int32)arg);  }  else  {
 
// Increment v6 address low qword (store to workspace)     wsp = htobe64(betoh64(*((int64*)(ip_addr(dst) + 8))) + arg);
   *((uint64*)(ip_addr(dst) + 8)) = wsp;
 

// Carry/borrow high qword     if ( arg > 0 && wsp < *((uint64*)(ip_addr(src) + 8)) )     {  *((int64*)(ip_addr(dst)))
=           htobe64(betoh64(*((int64*)(ip_addr(dst)))) + 1);     }     else        if ( arg < 0 && wsp >
*((uint64*)(ip_addr(src)+ 8)) )        {  *((int64*)(ip_addr(dst))) =
htobe64(betoh64(*((int64*)(ip_addr(dst))))- 1);        }  }
 

// Return result  VARATT_SIZEP(dst) = VARHDRSZ         + ((char *) ip_addr(dst) - (char *) VARDATA(dst))         +
ip_addrsize(dst);
  PG_RETURN_INET_P(dst);

}
-----------------------------------------------------

Thank you

Ilya A. Kovalenko         (mailto:)
SpecialEQ SW section
JSC Oganer-Service

P.S. Treat as Public Domain



From:
"Ilya A. Kovalenko"
Date:

oops

- FUNCTION inet_inc(int, int8) RETURNS inet
+ FUNCTION inet_inc(inet, int8) RETURNS inet



From:
Bruce Momjian
Date:

Would you modify this so it can go in /contrib or pgfoundry?  Is there
general interest for this?

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

Ilya A. Kovalenko wrote:
>    Greetings,
> 
>   I suggest function for "inet" increment w/ int8 (signed).
> 
> FUNCTION inet_inc(int, int8) RETURNS inet
> 
> Function, useful for making address pools (using also
> existing "inet" compare functions to trap boundaries).
> 
> Notes:
>   This version lets address wrap around 0-*ff boundary.
>   Uses couple of non-POSIX functions - betoh64() and htobe64()
>   Tested on i386 with OpenBSD 3.7
>   PostgreSQL 8.0.2
> 
> -----------------------------------------------------
> #include <sys/types.h>
> #include <sys/socket.h>
> #include <netinet/in.h>
> #include <arpa/inet.h>
> 
> #include "postgres.h"                   /* general Postgres declarations */
> 
> #include "fmgr.h"                       /* for argument/result macros */
> #include "utils/inet.h"
> 
> Datum           inet_inc(PG_FUNCTION_ARGS);
> 
> //------ stolen from backend/utils/adt/network.c --------
> 
> #define ip_family(inetptr) \
>         (((inet_struct *)VARDATA(inetptr))->family)
> #define ip_bits(inetptr) \
>         (((inet_struct *)VARDATA(inetptr))->bits)
> #define ip_type(inetptr) \
>         (((inet_struct *)VARDATA(inetptr))->type)
> #define ip_addr(inetptr) \
>         (((inet_struct *)VARDATA(inetptr))->ipaddr)
> #define ip_maxbits(inetptr) \
>         (ip_family(inetptr) == PGSQL_AF_INET ? 32 : 128)
> 
> static int
> ip_addrsize(inet *inetptr)
> {
>         switch (ip_family(inetptr))
>         {
>                 case PGSQL_AF_INET:
>                         return 4;
>                 case PGSQL_AF_INET6:
>                         return 16;
>                 default:
>                         return 0;
>         }
> }
> //-------------------------------------------------------
> 
> PG_FUNCTION_INFO_V1(inet_inc);
> 
> Datum
> inet_inc(PG_FUNCTION_ARGS)
> {
>    inet    *src = PG_GETARG_INET_P(0);
>    int64    arg = PG_GETARG_INT64(1);
>    inet    *dst;
>    uint64   wsp;
> 
> // allocate destination structure
>    dst = (inet *) palloc0(VARHDRSZ + sizeof(inet_struct));
> 
> // copy to destination
>    *((inet_struct *)VARDATA(dst)) = *((inet_struct *)VARDATA(src));
> 
>    if (ip_family(dst) == PGSQL_AF_INET)
>    {
> // Increment v4 address w/ item truncated to 32 bits
>       *((uint32*)(ip_addr(dst))) =
>           htonl(ntohl(*((int32*)(ip_addr(dst)))) + (int32)arg);
>    }
>    else
>    {
> // Increment v6 address low qword (store to workspace)
>       wsp = htobe64(betoh64(*((int64*)(ip_addr(dst) + 8))) + arg);
>       *((uint64*)(ip_addr(dst) + 8)) = wsp;
> 
> // Carry/borrow high qword
>       if ( arg > 0 && wsp < *((uint64*)(ip_addr(src) + 8)) )
>       {  *((int64*)(ip_addr(dst))) =
>              htobe64(betoh64(*((int64*)(ip_addr(dst)))) + 1);
>       }
>       else
>          if ( arg < 0 && wsp > *((uint64*)(ip_addr(src) + 8)) )
>          {  *((int64*)(ip_addr(dst))) =
>                 htobe64(betoh64(*((int64*)(ip_addr(dst)))) - 1);
>          }
>    }
> 
> // Return result
>    VARATT_SIZEP(dst) = VARHDRSZ
>           + ((char *) ip_addr(dst) - (char *) VARDATA(dst))
>           + ip_addrsize(dst);
> 
>    PG_RETURN_INET_P(dst);
> 
> }
> -----------------------------------------------------
> 
> Thank you
> 
> Ilya A. Kovalenko         (mailto:)
> SpecialEQ SW section
> JSC Oganer-Service
> 
> P.S. Treat as Public Domain
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

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


From:
"Ilya A. Kovalenko"
Date:

BM> Would you modify this so it can go in /contrib or pgfoundry?  Is there
BM> general interest for this?
 Actually, I suggested to do such or similar function as internal.
PostgreSQL has inet/cidr - excellent data type and good facilities to
examine and compare inet values, but has no facilities to modify
them (i.e. get-change-return operations).
 There is place for many useful operators and functions to do but
they not invented enough yet (to supplement with existing facilites
and each other).
  Only facility that has no doubt is increment/decrement inet
address value with numeric value. It nicely supplements existing
inet compare operators (that compares two addresses as numeric
values). Also, it can be used to override "+" and "-" operators
between inet and numeric values.
 I understand, that you have more important things to do, so I write
this function, to save your time.
 Maybe, it is good idea, to implement such functions separatelly as
/contrib ... for a start ...
 Live example for inet_inc() (as "+" operator)

(PL/pgSQL)
> -- (try to) Peek address from group pool
>    SELECT next INTO next_ip FROM peer_ranges WHERE
>       group_id = (SELECT id FROM peer_groups WHERE name = $3) AND
>       next < last
>       ORDER BY first LIMIT 1;
>
> -- Return NULL if pool is empty
>    IF NOT FOUND THEN
>       RAISE NOTICE ''Group address pool is empty'';
>       RETURN NULL;
>    END IF;
>
> -- Update pool
>    UPDATE peer_ranges SET next = next_ip + 1 WHERE next = next_ip;
>
>    RETURN next_ip;

where, peer_ranges is:

CREATE TABLE peer_ranges
( group_id  bigint NOT NULL REFERENCES peer_groups (id), first     inet   NOT NULL UNIQUE, next      inet   NOT NULL
UNIQUE,last      inet   NOT NULL UNIQUE
 
);

Thank you,

Ilya A. Kovalenko          (mailto:)



From:
Patrick Welche
Date:

On Mon, Apr 18, 2005 at 08:58:01PM -0400, Bruce Momjian wrote:
> 
> Would you modify this so it can go in /contrib or pgfoundry?  Is there
> general interest for this?

I was about to sit down and write the same function yesterday, when as if
by magic this appeared. In my case it is to loop over ip numbers in a
class C looking for a free one to allocate. So, from here there is
interest..

Cheers,

Patrick


From:
Bruce Momjian
Date:

Ilya A. Kovalenko wrote:
> BM> Would you modify this so it can go in /contrib or pgfoundry?  Is there
> BM> general interest for this?
> 
>   Actually, I suggested to do such or similar function as internal.
> PostgreSQL has inet/cidr - excellent data type and good facilities to
> examine and compare inet values, but has no facilities to modify
> them (i.e. get-change-return operations).
> 
>   There is place for many useful operators and functions to do but
> they not invented enough yet (to supplement with existing facilites
> and each other).
> 
>    Only facility that has no doubt is increment/decrement inet
> address value with numeric value. It nicely supplements existing
> inet compare operators (that compares two addresses as numeric
> values). Also, it can be used to override "+" and "-" operators
> between inet and numeric values.
> 
>   I understand, that you have more important things to do, so I write
> this function, to save your time.

Agreed.  Let's implement '+/-' for 'inet + int4' and put it in the
backend as standard (I can help do the system table stuff if you give me
the C functions).  However, how do we handle cases where int4 > 255.  I
am thinking we should support only inet + inet, like this:
SELECT '1.2.3.4'::inet + '0.0.1.2'::inet;

But how do we do:
SELECT '1.2.3.255'::inet + '0.0.0.1'::inet;

I assume this becomes '1.2.4.0'.  Inet +/- inet seems the most flexible
because it allows you to add to any part of the mask, rather than just
the lower-order bytes, or trying to make sense that 256 adds like
'0.0.1.0'.

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


From:
Greg Stark
Date:

Bruce Momjian <> writes:

> am thinking we should support only inet + inet, like this:
> 
>     SELECT '1.2.3.4'::inet + '0.0.1.2'::inet;

I don't think inet+inet makes any sense.

I think inet+int4 should work by adding to the host address and overflowing if
it exceeds the network mask.

Ie, 

10.0.0.0/24   + 1 = 10.0.0.1/24
10.0.0.255/24 + 1 => overflow

Or

10.1/16 + 1      = 10.1.0.1/16
10.1/16 + 16384  = 10.1.64.0/16
10.1/16 + 65536  => overflow



-- 
greg



From:
Bruce Momjian
Date:

Greg Stark wrote:
> 
> Bruce Momjian <> writes:
> 
> > am thinking we should support only inet + inet, like this:
> > 
> >     SELECT '1.2.3.4'::inet + '0.0.1.2'::inet;
> 
> I don't think inet+inet makes any sense.
> 
> I think inet+int4 should work by adding to the host address and overflowing if
> it exceeds the network mask.
> 
> Ie, 
> 
> 10.0.0.0/24   + 1 = 10.0.0.1/24
> 10.0.0.255/24 + 1 => overflow
> 
> Or
> 
> 10.1/16 + 1      = 10.1.0.1/16
> 10.1/16 + 16384  = 10.1.64.0/16
> 10.1/16 + 65536  => overflow

So, do not overflow?  We can do that.  Another idea Tom had was creating
a function that increments/decrements the address or the network portion
of the address, and if you increment past the non-network portion that
overflows too.

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


From:
Greg Stark
Date:

Bruce Momjian <> writes:

> > Ie, 
> > 
> > 10.0.0.0/24   + 1 = 10.0.0.1/24
> > 10.0.0.255/24 + 1 => overflow
> > 
> > Or
> > 
> > 10.1/16 + 1      = 10.1.0.1/16
> > 10.1/16 + 16384  = 10.1.64.0/16
> > 10.1/16 + 65536  => overflow
> 
> So, do not overflow?  

You mean not doing modulus arithemtic? Yes. Overflow instead.

I see a use case for of generating addresses based on a sequence or some
primary key from the database.

Something like

CREATE SEQUENCE hosts_ip_seq MAXVALUE 65536;
ALTER TABLE hosts ALTER ip SET DEFAULT '10.0.0.0/16'::inet + nextval(hosts_ip_seq')

Using the primary key or some foreign key in the table would require a trigger
which would take too much work to cons up an example for.

-- 
greg



From:
Bruno Wolff III
Date:

On Tue, Apr 19, 2005 at 12:03:27 -0400, Bruce Momjian <> wrote:
> 
> Agreed.  Let's implement '+/-' for 'inet + int4' and put it in the
> backend as standard (I can help do the system table stuff if you give me
> the C functions).  However, how do we handle cases where int4 > 255.  I
> am thinking we should support only inet + inet, like this:
> 
>     SELECT '1.2.3.4'::inet + '0.0.1.2'::inet;

I don't think this operation makes much sense. Adding an integer makes
some sense, but I think the original problem would be better solved
by having a set returning function generate the possible network addresses
to be allocated and store that set in a table.

I don't think assuming everthing is a /24 is a good idea. If wrapping is
to be done, there should be some mask specified.


From:
"Ilya A. Kovalenko"
Date:

BM> Greg Stark wrote:
>> 
>> Bruce Momjian <> writes:
>> 
>> > am thinking we should support only inet + inet, like this:
>> > 
>> >     SELECT '1.2.3.4'::inet + '0.0.1.2'::inet;
>> 
>> I don't think inet+inet makes any sense.
>> 
>> I think inet+int4 should work by adding to the host address and overflowing if
>> it exceeds the network mask.
>> 
>> Ie, 
>> 
>> 10.0.0.0/24   + 1 = 10.0.0.1/24
>> 10.0.0.255/24 + 1 => overflow
>> 
>> Or
>> 
>> 10.1/16 + 1      = 10.1.0.1/16
>> 10.1/16 + 16384  = 10.1.64.0/16
>> 10.1/16 + 65536  => overflow

BM> So, do not overflow?  We can do that.  Another idea Tom had was creating
BM> a function that increments/decrements the address or the network portion
BM> of the address, and if you increment past the non-network portion that
BM> overflows too.
 Hmm, actually, you can do several functions to increase/decrease
network address with different overflow models (octet-overflow,
host part overflow, full address overflow, or without overflow
as special case), for flexibility. Another question, what model choose for '+/-' ...
 BTW, why 'inet + int4' (not int8), what about v6 ?
 Few words for 'inet + inet'. It's can be useful for IPv6 addresses
(because you don't have 128-bit numeric type, except, maybe, 'numeric'
one). But, there is another way to reach higher octets - use existing
inet_{send|receive} functions.

<disclaimer text='raw ideas and thoughts'>

Or invent something new like this:

--                     <src>  <index>          <value>
FUNCTION extract_octet(inet, integer) RETURNS integer
FUNCTION extract_word (inet, integer) RETURNS int2
FUNCTION extract_dword(inet, integer) RETURNS int4
FUNCTION extract_qword(inet, integer) RETURNS int8

--                    <src>   <index>  <value>
FUNCTION replace_octet(inet, integer, integer) RETURNS inet
FUNCTION replace_word (inet, integer, int2)    RETURNS inet
FUNCTION replace_dword(inet, integer, int4)    RETURNS inet
FUNCTION replace_qword(inet, integer, int8)    RETURNS inet

(not established with signed 'int%')

</disclaimer>

Ilya A. Kovalenko



From:
"Ilya A. Kovalenko"
Date:

GS> I see a use case for of generating addresses based on a sequence or some
GS> primary key from the database.

GS> Something like

GS> CREATE SEQUENCE hosts_ip_seq MAXVALUE 65536;
GS> ALTER TABLE hosts ALTER ip SET DEFAULT '10.0.0.0/16'::inet + nextval(hosts_ip_seq')

hmm, not quite good idea - SEQUENCEs, by design, does not rollback next
value on transation rollback, so you'll have holes on address range when
other values will break some constraints or concurrent sessions
appears.





From:
Bruno Wolff III
Date:

On Wed, Apr 20, 2005 at 12:30:08 +0800, "Ilya A. Kovalenko" <> wrote:
> GS> I see a use case for of generating addresses based on a sequence or some
> GS> primary key from the database.
> 
> GS> Something like
> 
> GS> CREATE SEQUENCE hosts_ip_seq MAXVALUE 65536;
> GS> ALTER TABLE hosts ALTER ip SET DEFAULT '10.0.0.0/16'::inet + nextval(hosts_ip_seq')
> 
> hmm, not quite good idea - SEQUENCEs, by design, does not rollback next
> value on transation rollback, so you'll have holes on address range when
> other values will break some constraints or concurrent sessions
> appears.

You are going to have to have some way of handling holes anyway. What
happens when an allocated IP address is returned? That is why I think
for most uses a table with a row for each possible allocation is the
way to go.


From:
Bruce Momjian
Date:

Patrick Welche wrote:
> On Fri, May 20, 2005 at 11:12:54PM -0400, Bruce Momjian wrote:
> > Added to TODO:
> > 
> >     * Allow INET + INT4/INT8 to increment the host part of the address, or
> >       throw an error on overflow
> > 
> > I have not heard any use-case for adding to the network value if INET,
> > and by not using it, we can have an easy operator API.
> 
> Thanks - I'll look at the code that was posted..

I modified the TODO.  I think we only need an INT4.  I realize INT8
would be for IPV6 but I can't imagine a network that has more than INT4
hosts (not part of the network address).

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


From:
Douglas McNaught
Date:

Bruce Momjian <> writes:

> I modified the TODO.  I think we only need an INT4.  I realize INT8
> would be for IPV6 but I can't imagine a network that has more than INT4
> hosts (not part of the network address).

Actually "increment the host address" isn't a well-defined concept for
IPV6.  The "host" part of the address (if you're on an Ethernet) is
generally the 64 bit MAC address.

-Doug


From:
Bruce Momjian
Date:

Douglas McNaught wrote:
> Bruce Momjian <> writes:
> 
> > I modified the TODO.  I think we only need an INT4.  I realize INT8
> > would be for IPV6 but I can't imagine a network that has more than INT4
> > hosts (not part of the network address).
> 
> Actually "increment the host address" isn't a well-defined concept for
> IPV6.  The "host" part of the address (if you're on an Ethernet) is
> generally the 64 bit MAC address.

So if the network card dies the machine has a new IPv6 address and you
just update your DNS?  Do you update your routing tables?

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


From:
Stephen Frost
Date:

* Bruce Momjian () wrote:
> Douglas McNaught wrote:
> > Bruce Momjian <> writes:
> >
> > > I modified the TODO.  I think we only need an INT4.  I realize INT8
> > > would be for IPV6 but I can't imagine a network that has more than INT4
> > > hosts (not part of the network address).
> >
> > Actually "increment the host address" isn't a well-defined concept for
> > IPV6.  The "host" part of the address (if you're on an Ethernet) is
> > generally the 64 bit MAC address.
>
> So if the network card dies the machine has a new IPv6 address and you
> just update your DNS?  Do you update your routing tables?

Generally routing isn't done to the last 48 bits (dunno where 64 bit
came from, but MAC's are 48 last I checked :).

DNS to that level would need to be changed though, yes.. :/

(I'm not exactly a big fan of this development, in fact, I think it's a
bunch of poo, but then, I don't write the standards).
Stephen

From:
"Sander Steffann"
Date:

Hi,

>> > I modified the TODO.  I think we only need an INT4.  I realize INT8
>> > would be for IPV6 but I can't imagine a network that has more than INT4
>> > hosts (not part of the network address).
>>
>> Actually "increment the host address" isn't a well-defined concept for
>> IPV6.  The "host" part of the address (if you're on an Ethernet) is
>> generally the 64 bit MAC address.
>
> So if the network card dies the machine has a new IPv6 address and you
> just update your DNS?  Do you update your routing tables?

There are standards defined for automatically determining the IPv6 address 
of a host (Stateless Address Autoconfiguration). These include a standard 
for "Privacy Extensions for Stateless Address Autoconfiguration in IPv6" 
where the host-part of the IPv6 address changes over time to make it more 
difficult to identify a single user. The net-part of the IPv6 address can be 
determined by "Router Advertisements".

By default an IPv6 address is divided as follows: first 32 bits:  ISP next 16 bits:   customer next 16 bits:   subnet
rest(64 bits): host
 

So an ISP gets a /32 from ARIN/RIPE/LACNIC/APNIC/AfriNIC, which assigns a 
/48 to a customer, which assigns a /64 to each separate network. There are 
ISPs that have so many customers that they got more than a /32, and if a 
customer needs more than 16 bits for subnets they can get a bigger block 
than a /48. This addressing scheme means that even a home-user is a customer 
and gets a /48 with 16 bits for subnetting. There are discussions going on 
about giving home users a /56 block instead, but I haven't heard a final 
decision about that yet (in the RIPE region).

From 
http://www.tcpipguide.com/free/t_IPv6InterfaceIdentifiersandPhysicalAddressMapping.htm: The IEEE has also defined a
formatcalled the 64-bit extended unique identifier, abbreviated EUI-64. It is similar to the 48-bit MAC format, except
thatwhile the OUI remains at 24 bits, the device identifier becomes 40 bits instead of 24. This provides gives each
manufacturer65,536 times as many device addresses within its OUI.
 
 A form of this format, called modified EUI-64, has been adopted for IPv6 interface identifiers. To get the modified
EUI-64interface ID for a device, you simply take the EUI-64 address and change the 7th bit from the left (the
"universal/local"or "U/L" bit) from a zero to a one.
 

Because the 7th bit is always a one with auto-configuration, addresses with 
7th bit zero are still free to be manually assigned.

I hope this helps a little...
Sander.