Thread
-
inet increment w/ int8
Ilya A. Kovalenko <shadow@oganer.net> — 2005-04-17T20:25:34Z
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:shadow@oganer.net) SpecialEQ SW section JSC Oganer-Service P.S. Treat as Public Domain -
Re: inet increment w/ int8
Ilya A. Kovalenko <shadow@oganer.net> — 2005-04-18T01:46:19Z
oops - FUNCTION inet_inc(int, int8) RETURNS inet + FUNCTION inet_inc(inet, int8) RETURNS inet
-
Re: inet increment w/ int8
Bruce Momjian <pgman@candle.pha.pa.us> — 2005-04-19T00:58:01Z
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:shadow@oganer.net) > 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 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 -
Re: inet increment w/ int8
Patrick Welche <prlw1@newn.cam.ac.uk> — 2005-04-19T10:44:54Z
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
-
Re: inet increment w/ int8
Ilya A. Kovalenko <shadow@oganer.net> — 2005-04-19T12:11:43Z
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:shadow@oganer.net)
-
Re: inet increment w/ int8
Bruce Momjian <pgman@candle.pha.pa.us> — 2005-04-19T16:03:27Z
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 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
-
Re: inet increment w/ int8
Greg Stark <gsstark@mit.edu> — 2005-04-19T16:41:34Z
Bruce Momjian <pgman@candle.pha.pa.us> 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
-
Re: inet increment w/ int8
Bruce Momjian <pgman@candle.pha.pa.us> — 2005-04-19T16:49:03Z
Greg Stark wrote: > > Bruce Momjian <pgman@candle.pha.pa.us> 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 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
-
Re: inet increment w/ int8
Greg Stark <gsstark@mit.edu> — 2005-04-19T17:14:42Z
Bruce Momjian <pgman@candle.pha.pa.us> 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
-
Re: inet increment w/ int8
Bruno Wolff III <bruno@wolff.to> — 2005-04-19T20:48:56Z
On Tue, Apr 19, 2005 at 12:03:27 -0400, Bruce Momjian <pgman@candle.pha.pa.us> 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.
-
Re: inet increment w/ int8
Ilya A. Kovalenko <shadow@oganer.net> — 2005-04-20T03:53:25Z
BM> Greg Stark wrote: >> >> Bruce Momjian <pgman@candle.pha.pa.us> 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 -
Re: inet increment w/ int8
Ilya A. Kovalenko <shadow@oganer.net> — 2005-04-20T04:30:08Z
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.
-
Re: inet increment w/ int8
Bruno Wolff III <bruno@wolff.to> — 2005-04-20T15:18:10Z
On Wed, Apr 20, 2005 at 12:30:08 +0800, "Ilya A. Kovalenko" <shadow@oganer.net> 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.