Обсуждение: 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@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, Pennsylvania19073
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, Pennsylvania19073
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 inetminus 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