Re: contrib/xinetops for 8.1 "patch" - Mailing list pgsql-patches

Stephen R. van den Berg wrote:
> I've been meaning to send it in for a while now, IMHO it could be
> made part of the main distribution as well, but contrib is fine
> if not everyone likes it.
>
> I included the source in patch-format, since I didn't feel comfortable
> attaching a tar.gz file on this list.
> Below is an excerpt from the included README:
> -------------------------------- cut here ---------------------------------
> inet extended operations
> ~~~~~~~~~~~~~~~~~~~~~~~~
>
> This directory contains definitions for extended operators on the
> inet data type.
>
> Operators available are:
>
>    ~ & | + -
>
> It supports the "natural" arithmetic with IP addresses and integers.
> It is useful for applications which have to hand out and administer
> ranges of IP-addresses (like a Radius or DHCP server).

I have modified your original patch to add the code into the main
backend routines.  I added documentation and regression tests as well.

I created only inet functions because family() works on both inet and
cidr, so I assume we are to create just inet versions and cidr will
still work.

Patch needs a catalog version bump.

One unusual issue is because 'inet - inet' returns an int32, you can't
subtract two IPv6 addresses that differ by more than int32.

A larger problem is this:

    test=> SELECT '255.255.255.0'::inet - '1.1.1.1'::inet;
     ?column?
    -----------
     -16843265
    (1 row)

Should subtraction return int8?  Perhaps it is OK because:

    test=> select '255.255.255.0'::inet - (-16843265);
     ?column?
    ----------
     1.1.1.1
    (1 row)

We don't have an unsigned data type.  Of course we also have this
excitement:

    test=> SELECT '255.255.255.0'::inet +  1000000;
      ?column?
    ------------
     0.15.65.64
    (1 row)

so we underflow and overflow cleanly.  Not great, but it works.

--
  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
Index: doc/src/sgml/func.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.303
diff -c -c -r1.303 func.sgml
*** doc/src/sgml/func.sgml    26 Jan 2006 02:35:48 -0000    1.303
--- doc/src/sgml/func.sgml    9 Feb 2006 20:18:40 -0000
***************
*** 6787,6792 ****
--- 6787,6822 ----
          <entry>contains or equals</entry>
          <entry><literal>inet '192.168.1/24' >>= inet '192.168.1/24'</literal></entry>
         </row>
+        <row>
+         <entry> <literal>~</literal> </entry>
+         <entry>bitwise NOT</entry>
+         <entry><literal>~ inet '192.168.1.6'</literal></entry>
+        </row>
+        <row>
+         <entry> <literal>&</literal> </entry>
+         <entry>bitwise AND</entry>
+         <entry><literal>inet '192.168.1.6' & inet '0.0.0.255'</literal></entry>
+        </row>
+        <row>
+         <entry> <literal>|</literal> </entry>
+         <entry>bitwise OR</entry>
+         <entry><literal>inet '192.168.1.6' | inet '0.0.0.255'</literal></entry>
+        </row>
+        <row>
+         <entry> <literal>+</literal> </entry>
+         <entry>addition</entry>
+         <entry><literal>inet '192.168.1.6' + 25</literal></entry>
+        </row>
+        <row>
+         <entry> <literal>-</literal> </entry>
+         <entry>subtraction</entry>
+         <entry><literal>inet '192.168.1.43' - 36</literal></entry>
+        </row>
+        <row>
+         <entry> <literal>-</literal> </entry>
+         <entry>subtraction</entry>
+         <entry><literal>inet '192.168.1.43' - inet '192.168.1.19'</literal></entry>
+        </row>
        </tbody>
       </tgroup>
      </table>
Index: src/backend/utils/adt/network.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/network.c,v
retrieving revision 1.63
diff -c -c -r1.63 network.c
*** src/backend/utils/adt/network.c    7 Feb 2006 17:04:04 -0000    1.63
--- src/backend/utils/adt/network.c    9 Feb 2006 20:18:43 -0000
***************
*** 27,32 ****
--- 27,33 ----
  static int    bitncmp(void *l, void *r, int n);
  static bool addressOK(unsigned char *a, int bits, int family);
  static int    ip_addrsize(inet *inetptr);
+ static Datum internal_inetpl(inet *ip, int iarg);

  /*
   *    Access macros.
***************
*** 1250,1252 ****
--- 1251,1441 ----

      PG_RETURN_DATUM(DirectFunctionCall1(int4in, CStringGetDatum(local_port)));
  }
+
+
+ Datum
+ inetnot(PG_FUNCTION_ARGS)
+ {
+     inet       *ip = PG_GETARG_INET_P(0);
+     inet       *dst;
+
+     dst = (inet *) palloc0(VARHDRSZ + sizeof(inet_struct));
+
+     {
+         int nb = ip_addrsize(ip);
+         unsigned char    *pip = ip_addr(ip);
+         unsigned char    *pdst = ip_addr(dst);
+
+         while (nb-- > 0)
+             pdst[nb] = ~pip[nb];
+     }
+     ip_bits(dst) = ip_bits(ip);
+
+     ip_family(dst) = ip_family(ip);
+     VARATT_SIZEP(dst) = VARHDRSZ +
+         ((char *) ip_addr(dst) - (char *) VARDATA(dst)) +
+         ip_addrsize(dst);
+
+     PG_RETURN_INET_P(dst);
+ }
+
+
+ Datum
+ inetand(PG_FUNCTION_ARGS)
+ {
+     inet       *ip = PG_GETARG_INET_P(0);
+     inet       *ip2 = PG_GETARG_INET_P(1);
+     inet       *dst;
+
+     dst = (inet *) palloc0(VARHDRSZ + sizeof(inet_struct));
+
+     if (ip_family(ip) != ip_family(ip2))
+         ereport(ERROR,
+                 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+                  errmsg("mismatch in address family (%d) != (%d)",
+                         ip_family(ip), ip_family(ip2))));
+     else
+     {
+         int nb = ip_addrsize(ip);
+         unsigned char    *pip = ip_addr(ip);
+         unsigned char    *pip2 = ip_addr(ip2);
+         unsigned char    *pdst = ip_addr(dst);
+
+         while (nb-- > 0)
+             pdst[nb] = pip[nb] & pip2[nb];
+     }
+     ip_bits(dst) = Max(ip_bits(ip), ip_bits(ip2));
+
+     ip_family(dst) = ip_family(ip);
+     VARATT_SIZEP(dst) = VARHDRSZ +
+         ((char *) ip_addr(dst) - (char *) VARDATA(dst)) +
+         ip_addrsize(dst);
+
+     PG_RETURN_INET_P(dst);
+ }
+
+
+ Datum
+ inetor(PG_FUNCTION_ARGS)
+ {
+     inet       *ip = PG_GETARG_INET_P(0);
+     inet       *ip2 = PG_GETARG_INET_P(1);
+     inet       *dst;
+
+     dst = (inet *) palloc0(VARHDRSZ + sizeof(inet_struct));
+
+     if (ip_family(ip) != ip_family(ip2))
+         ereport(ERROR,
+                 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+                  errmsg("mismatch in address family (%d) != (%d)",
+                         ip_family(ip), ip_family(ip2))));
+     else
+     {
+         int nb = ip_addrsize(ip);
+         unsigned char    *pip = ip_addr(ip);
+         unsigned char    *pip2 = ip_addr(ip2);
+         unsigned char    *pdst = ip_addr(dst);
+
+         while (nb-- > 0)
+             pdst[nb] = pip[nb] | pip2[nb];
+     }
+     ip_bits(dst) = Max(ip_bits(ip), ip_bits(ip2));
+
+     ip_family(dst) = ip_family(ip);
+     VARATT_SIZEP(dst) = VARHDRSZ +
+         ((char *) ip_addr(dst) - (char *) VARDATA(dst)) +
+         ip_addrsize(dst);
+
+     PG_RETURN_INET_P(dst);
+ }
+
+
+ static Datum
+ internal_inetpl(inet *ip, int plus)
+ {
+     inet       *dst;
+
+     dst = (inet *) palloc0(VARHDRSZ + sizeof(inet_struct));
+
+     {
+         int nb = ip_addrsize(ip);
+         unsigned char    *pip = ip_addr(ip);
+         unsigned char    *pdst = ip_addr(dst);
+         int carry = 0;
+
+         while (nb-- > 0)
+         {
+             pdst[nb] = carry = pip[nb] + (plus & 0xff) + carry;
+             plus >>= 8;        /* process next byte */
+             carry >>= 8;    /* remove low byte */
+         }
+     }
+     ip_bits(dst) = ip_bits(ip);
+
+     ip_family(dst) = ip_family(ip);
+     VARATT_SIZEP(dst) = VARHDRSZ +
+         ((char *) ip_addr(dst) - (char *) VARDATA(dst)) +
+         ip_addrsize(dst);
+
+     PG_RETURN_INET_P(dst);
+ }
+
+
+ Datum
+ inetpl(PG_FUNCTION_ARGS)
+ {
+     inet   *ip = PG_GETARG_INET_P(0);
+     int32    plus = PG_GETARG_INT32(1);
+
+     return internal_inetpl(ip, plus);
+ }
+
+
+ Datum
+ inetmi_int4(PG_FUNCTION_ARGS)
+ {
+     inet   *ip = PG_GETARG_INET_P(0);
+     int32    plus = PG_GETARG_INT32(1);
+
+     return internal_inetpl(ip, -plus);
+ }
+
+
+ Datum
+ inetmi(PG_FUNCTION_ARGS)
+ {
+     inet       *ip = PG_GETARG_INET_P(0);
+     inet       *ip2 = PG_GETARG_INET_P(1);
+     int32        res = 0;
+
+     if (ip_family(ip) != ip_family(ip2))
+         ereport(ERROR,
+                 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+                  errmsg("mismatch in address family (%d) != (%d)",
+                         ip_family(ip), ip_family(ip2))));
+     else
+     {
+         int nb = ip_addrsize(ip);
+         int    byte = 0;
+         unsigned char    *pip = ip_addr(ip);
+         unsigned char    *pip2 = ip_addr(ip2);
+
+         while (nb-- > 0)
+         {
+             if (byte >= sizeof(int32))
+             {
+                 /* Error on IPv6 subtraction when bytes beyond int32 differ */
+                 if (pip[nb] != pip2[nb])
+                     ereport(ERROR,
+                             (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+                              errmsg("result out of range")));
+             }
+             else
+                 res += (pip[nb] - pip2[nb]) << (byte * 8);
+
+             byte++;
+         }
+     }
+
+     PG_RETURN_INT32(res);
+ }
Index: src/include/catalog/pg_operator.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/catalog/pg_operator.h,v
retrieving revision 1.138
diff -c -c -r1.138 pg_operator.h
*** src/include/catalog/pg_operator.h    26 Jan 2006 02:35:49 -0000    1.138
--- src/include/catalog/pg_operator.h    9 Feb 2006 20:18:45 -0000
***************
*** 653,658 ****
--- 653,667 ----
  DATA(insert OID = 934  (  ">>="    PGNSP PGUID b f 869 869     16 932        0     0      0   0   0 network_supeq - -
));
  #define OID_INET_SUPEQ_OP                934

+ DATA(insert OID = 2634 (  "~"       PGNSP PGUID l f    0 869 869 0 0 0 0 0 0 inetnot - - ));
+ DATA(insert OID = 2635 (  "&"       PGNSP PGUID b f    869 869 869 0 0 0 0 0 0 inetand - - ));
+ DATA(insert OID = 2636 (  "|"       PGNSP PGUID b f    869 869 869 0 0 0 0 0 0 inetor - - ));
+ DATA(insert OID = 2637 (  "+"       PGNSP PGUID b f    869 23 869 2638 0 0 0 0 0 inetpl - - ));
+ DATA(insert OID = 2638 (  "+"       PGNSP PGUID b f    23 869 869 2637 0 0 0 0 0 int4pl_inet - - ));
+ DATA(insert OID = 2639 (  "-"       PGNSP PGUID b f    869 23 869 0 0 0 0 0 0 inetmi_int4 - - ));
+ DATA(insert OID = 2640 (  "-"       PGNSP PGUID b f    869 869 23 0 0 0 0 0 0 inetmi - - ));
+
+
  /* case-insensitive LIKE hacks */
  DATA(insert OID = 1625 (  "~~*"   PGNSP PGUID b f  19    25    16 0 1626 0 0 0 0 nameiclike iclikesel iclikejoinsel
));
  #define OID_NAME_ICLIKE_OP        1625
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.394
diff -c -c -r1.394 pg_proc.h
*** src/include/catalog/pg_proc.h    9 Feb 2006 14:53:51 -0000    1.394
--- src/include/catalog/pg_proc.h    9 Feb 2006 20:18:50 -0000
***************
*** 2431,2436 ****
--- 2431,2451 ----
  DATA(insert OID = 2199 (  inet_server_port        PGNSP PGUID 12 f f f f s 0 23 "" _null_ _null_ _null_
inet_server_port- _null_ )); 
  DESCR("server's port number for this connection");

+ DATA(insert OID = 2627 (  inetnot            PGNSP PGUID 12 f f t f i 1 869 "869" _null_ _null_ _null_    inetnot -
_null_)); 
+ DESCR("binary NOT");
+ DATA(insert OID = 2628 (  inetand            PGNSP PGUID 12 f f t f i 2 869 "869 869" _null_ _null_ _null_    inetand
-_null_ )); 
+ DESCR("binary AND");
+ DATA(insert OID = 2629 (  inetor            PGNSP PGUID 12 f f t f i 2 869 "869 869" _null_ _null_ _null_    inetor -
_null_)); 
+ DESCR("binary OR");
+ DATA(insert OID = 2630 (  inetpl            PGNSP PGUID 12 f f t f i 2 869 "869 23" _null_ _null_ _null_    inetpl -
_null_)); 
+ DESCR("add integer to INET value");
+ DATA(insert OID = 2631 ( int4pl_inet        PGNSP PGUID 14 f f t f i 2 869 "23 869" _null_ _null_ _null_    "select
$2+ $1" - _null_ )); 
+ DESCR("add integer to INET value");
+ DATA(insert OID = 2632 (  inetmi_int4        PGNSP PGUID 12 f f t f i 2 869 "869 23" _null_ _null_ _null_
inetmi_int4- _null_ )); 
+ DESCR("subtract integer from INET value");
+ DATA(insert OID = 2633 (  inetmi            PGNSP PGUID 12 f f t f i 2 23 "869 869" _null_ _null_ _null_    inetmi -
_null_)); 
+ DESCR("subtract INET values");
+
  DATA(insert OID = 1686 ( numeric            PGNSP PGUID 12 f f t f i 1 1700 "25" _null_ _null_ _null_ text_numeric -
_null_)); 
  DESCR("(internal)");
  DATA(insert OID = 1688 ( text                PGNSP PGUID 12 f f t f i 1 25 "1700" _null_ _null_ _null_ numeric_text -
_null_)); 
Index: src/include/utils/builtins.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/utils/builtins.h,v
retrieving revision 1.272
diff -c -c -r1.272 builtins.h
*** src/include/utils/builtins.h    26 Jan 2006 02:35:50 -0000    1.272
--- src/include/utils/builtins.h    9 Feb 2006 20:18:51 -0000
***************
*** 734,739 ****
--- 734,745 ----
  extern Datum inet_client_port(PG_FUNCTION_ARGS);
  extern Datum inet_server_addr(PG_FUNCTION_ARGS);
  extern Datum inet_server_port(PG_FUNCTION_ARGS);
+ extern Datum inetnot(PG_FUNCTION_ARGS);
+ extern Datum inetand(PG_FUNCTION_ARGS);
+ extern Datum inetor(PG_FUNCTION_ARGS);
+ extern Datum inetpl(PG_FUNCTION_ARGS);
+ extern Datum inetmi_int4(PG_FUNCTION_ARGS);
+ extern Datum inetmi(PG_FUNCTION_ARGS);

  /* mac.c */
  extern Datum macaddr_in(PG_FUNCTION_ARGS);
Index: src/test/regress/expected/inet.out
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/expected/inet.out,v
retrieving revision 1.19
diff -c -c -r1.19 inet.out
*** src/test/regress/expected/inet.out    8 Oct 2004 01:45:37 -0000    1.19
--- src/test/regress/expected/inet.out    9 Feb 2006 20:18:52 -0000
***************
*** 240,244 ****
--- 240,381 ----
   192.168.1.0/26 | 192.168.1.226
  (6 rows)

+ SELECT ~i FROM inet_tbl;
+                   ?column?
+ --------------------------------------------
+  63.87.254.29/24
+  63.87.254.29
+  63.87.254.255/24
+  63.87.254.255/25
+  63.87.254.0/24
+  63.87.254.0/25
+  245.254.253.252/8
+  245.254.253.252/8
+  245.254.253.252
+  245.254.253.252/24
+  245.254.253.252/16
+  245.254.253.252/8
+  244.254.253.252/8
+  246.254.253.252/8
+  ffef:ffdc:ffff:ffff:ffff:ffff:ffff:ff0e/64
+  ffef:ffdc:ffff:ffff:ffff:ffff:ffff:0
+  ffff:ffff:ffff:ffff:ffff:ffff:fbfc:fdfe/24
+ (17 rows)
+
+ SELECT i & c FROM inet_tbl;
+     ?column?
+ ----------------
+  192.168.1.0/24
+  192.168.1.0
+  192.168.1.0/24
+  192.168.1.0/25
+  192.168.1.0/24
+  192.168.1.0/25
+  10.0.0.0/8
+  10.0.0.0
+  10.1.2.3
+  10.1.2.0/24
+  10.1.0.0/16
+  10.0.0.0/8
+  10.0.0.0/8
+  8.0.0.0/8
+  10:23::f1
+  10:23::8000
+  ::0.2.2.0
+ (17 rows)
+
+ SELECT i | c FROM inet_tbl;
+      ?column?
+ ------------------
+  192.168.1.226/24
+  192.168.1.226
+  192.168.1.0/24
+  192.168.1.0/25
+  192.168.1.255/24
+  192.168.1.255/25
+  10.1.2.3/8
+  10.1.2.3
+  10.1.2.3
+  10.1.2.3/24
+  10.1.2.3/16
+  10.1.2.3/8
+  11.1.2.3/8
+  11.1.2.3/8
+  10:23::f1
+  10:23::ffff
+  ::ffff:5.3.3.5
+ (17 rows)
+
+ SELECT i + 5000 FROM inet_tbl;
+      ?column?
+ -------------------
+  192.168.21.106/24
+  192.168.21.106
+  192.168.20.136/24
+  192.168.20.136/25
+  192.168.21.135/24
+  192.168.21.135/25
+  10.1.21.139/8
+  10.1.21.139/8
+  10.1.21.139
+  10.1.21.139/24
+  10.1.21.139/16
+  10.1.21.139/8
+  11.1.21.139/8
+  9.1.21.139/8
+  10:23::1479/64
+  10:23::1:1387
+  ::4.3.21.137/24
+ (17 rows)
+
+ SELECT i - 5000 FROM inet_tbl;
+                 ?column?
+ ----------------------------------------
+  192.167.238.90/24
+  192.167.238.90
+  192.167.237.120/24
+  192.167.237.120/25
+  192.167.238.119/24
+  192.167.238.119/25
+  10.0.238.123/8
+  10.0.238.123/8
+  10.0.238.123
+  10.0.238.123/24
+  10.0.238.123/16
+  10.0.238.123/8
+  11.0.238.123/8
+  9.0.238.123/8
+  10:22:ffff:ffff:ffff:ffff:ffff:ed69/64
+  10:23::ec77
+  ::4.2.238.121/24
+ (17 rows)
+
+ SELECT i - c FROM inet_tbl where family(i) = 4;
+  ?column?
+ -----------
+        226
+        226
+          0
+          0
+        255
+        255
+      66051
+      66051
+          0
+          3
+        515
+      66051
+   16843267
+  -16711165
+ (14 rows)
+
+ SELECT (inet '::ffff:1.2.3.4/128') - (inet '::ffff:1.1.1.1/128');
+  ?column?
+ ----------
+     66051
+ (1 row)
+
+ SELECT (inet '::ffff:1.1.1.1/128') - (inet '::1/128');
+ ERROR:  result out of range
  SET enable_seqscan TO on;
  DROP INDEX inet_idx1;
Index: src/test/regress/sql/inet.sql
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/sql/inet.sql,v
retrieving revision 1.11
diff -c -c -r1.11 inet.sql
*** src/test/regress/sql/inet.sql    8 Oct 2004 01:45:37 -0000    1.11
--- src/test/regress/sql/inet.sql    9 Feb 2006 20:18:52 -0000
***************
*** 62,67 ****
--- 62,77 ----
  SET enable_seqscan TO off;
  SELECT * FROM inet_tbl WHERE i<<'192.168.1.0/24'::cidr;
  SELECT * FROM inet_tbl WHERE i<<='192.168.1.0/24'::cidr;
+
+ SELECT ~i FROM inet_tbl;
+ SELECT i & c FROM inet_tbl;
+ SELECT i | c FROM inet_tbl;
+ SELECT i + 5000 FROM inet_tbl;
+ SELECT i - 5000 FROM inet_tbl;
+ SELECT i - c FROM inet_tbl where family(i) = 4;
+ SELECT (inet '::ffff:1.2.3.4/128') - (inet '::ffff:1.1.1.1/128');
+ SELECT (inet '::ffff:1.1.1.1/128') - (inet '::1/128');
+
  SET enable_seqscan TO on;
  DROP INDEX inet_idx1;