qa: INET data type

[vivekmenezes]

No description provided.

[nvanbenschoten]

I started off by making a simple schema that included a user id as a UUID and an INET address:

CREATE TABLE users (
    id   UUID PRIMARY KEY,
    addr INET
)
CREATE INDEX ON users (addr);

I then created a program to populate this table. I wanted to use a driver that supported the
INET type properly. I first looked at https://github.com/sfackler/rust-postgres, but INET is
not supported by that driver because of sfackler/rust-postgres#161.
I then looked at https://github.com/lib/pq, but of course INET was not supported because the
library is pretty bare-bones. Finally, I found that https://github.com/jackc/pgx supports
the data type natively by mapping it to a Go net.IPNet.
This is just one of many reasons why I think we should switch to pgx.

Using the driver, I wrote this small program. It worked as intended and I did not run in to
a single problem:

package main

import (
	"errors"
	"log"
	"net"

	"github.com/jackc/pgx"
	"github.com/satori/go.uuid"
)

func addInet(conn *pgx.Conn, id uuid.UUID, addr net.IPNet) error {
	commandTag, err := conn.Exec("INSERT INTO users VALUES ($1, $2)", id, addr)
	if err != nil {
		return err
	}
	if commandTag.RowsAffected() != 1 {
		return errors.New("No row found to delete")
	}
	return nil
}

func queryInets(conn *pgx.Conn) error {
	rows, err := conn.Query("SELECT * FROM users")
	if err != nil {
		return err
	}
	defer rows.Close()

	log.Println("found rows")
	for rows.Next() {
		var id uuid.UUID
		var addr net.IPNet
		if err = rows.Scan(&id, &addr); err != nil {
			return err
		}
		log.Printf(" %s -> %s\n", id, addr)
	}
	return rows.Err()
}

func main() {
	conn, err := pgx.Connect(pgx.ConnConfig{
		Host:     "localhost",
		Port:     26257,
		Database: "inettest",
		User:     "root",
	})
	if err != nil {
		log.Fatal("error connecting to the database: ", err)
	}

	id := uuid.NewV4()
	addr := net.IPNet{
		IP:   net.IPv4(12, 13, 14, 15),
		Mask: net.IPv4Mask(255, 255, 0, 0),
	}
	if err := addInet(conn, id, addr); err != nil {
		log.Fatal("error adding inet: ", err)
	}
	log.Printf("inserted new IPv4 row\n\n")

	id = uuid.NewV4()
	addr = net.IPNet{
		IP: net.ParseIP("2001:db8::68"),
	}
	if err := addInet(conn, id, addr); err != nil {
		log.Fatal("error adding inet: ", err)
	}
	log.Printf("inserted new IPv6 row\n\n")

	if err := queryInets(conn); err != nil {
		log.Fatal("error fetching inets: ", err)
	}
}

I then ran some queries against PG to determine which functions it supports for the INET
data type:

nathan=# select proname, proargtypes from pg_proc where 'inet'::regtype = ANY (proargtypes);
       proname        |     proargtypes
----------------------+---------------------
 hashinet             | 869
 inet_out             | 869
 network_eq           | 869 869
 network_lt           | 869 869
 network_le           | 869 869
 network_gt           | 869 869
 network_ge           | 869 869
 network_ne           | 869 869
 network_larger       | 869 869
 network_smaller      | 869 869
 network_cmp          | 869 869
 network_sub          | 869 869
 network_subeq        | 869 869
 network_sup          | 869 869
 network_supeq        | 869 869
 network_overlap      | 869 869
 abbrev               | 869
 set_masklen          | 869 23
 family               | 869
 network              | 869
 netmask              | 869
 masklen              | 869
 broadcast            | 869
 host                 | 869
 text                 | 869
 hostmask             | 869
 cidr                 | 869
 inetnot              | 869
 inetand              | 869 869
 inetor               | 869 869
 inetpl               | 869 20
 int8pl_inet          | 20 869
 inetmi_int8          | 869 20
 inetmi               | 869 869
 inet_same_family     | 869 869
 inet_merge           | 869 869
 inet_gist_consistent | 2281 869 21 26 2281
 inet_gist_same       | 869 869 2281
 max                  | 869
 min                  | 869
 inet_send            | 869
(41 rows)

I verified that all of the functions that we support return the same results as Postgres. There are
functions that we are missing though. These are already documented in #18846. I also verified that
all operators introduced in #18847 match their PG equivalents.

The only issues I ran into during this process were:

#22390 (fixed in #22397)
#23850

This demonstrates how well this was all implemented by @LEGO and @mwistrom!