PolarDB:ip4r (network address storage)

Applicability

The following versions of PolarDB for PostgreSQL (Compatible with Oracle) are supported:

• Oracle syntax compatibility 2.0 (minor engine version 2.0.14.9.13.0 or later)

• Oracle syntax compatibility 1.0 (minor engine version 2.0.11.9.36.0 or later)

Background information

ip4r is a third-party extension supported by PolarDB for PostgreSQL (Compatible with Oracle) that provides data types for storing IPv4 and IPv6 addresses. Unlike PostgreSQL's built-in network data types such as inet/cidr, ip4r supports index scans for the contains operator >>=. In addition, ip4r has the following features compared to the built-in network data types of PostgreSQL:

• Clearer semantics: It distinguishes between a network block and a specific IP address within that block.

• Lower overhead: PostgreSQL uses variable-length data types to support IPv6 data. This creates significant overhead in scenarios where you only need to store IPv4 data. ip4r uses fixed-length data types for single addresses.

Data types

ip4r provides a variety of data types for storing and representing IPv4 or IPv6 data:

Single-address data types and type conversions

The following three data types are used to store single IP addresses:

• ip4: The input must be in the nnn.nnn.nnn.nnn format. The data is stored as a 32-bit unsigned integer.

• ip6: The input must be in the standard hexadecimal representation of an IPv6 address. The data is stored as two 64-bit values.

• ipaddress: The input must be in either the ip4 or ip6 format.

ip4r provides type conversions for these three data types. The rules are as follows:

Address-range data types and type conversions

The following three data types are used to store IP address ranges:

• ip4r: Stores an IPv4 address range. For example, 192.0.2.100-192.0.2.200. 192.0.2.0/24 is equivalent to 192.0.2.0-192.0.2.255.

• ip6r: Stores an IPv6 address range. For example, 2001::1234-2001::2000:0000. 2001::/112 is equivalent to 2001::-2001::ffff.

• iprange: The input must be in either the ip4r or ip6r format.

ip4r provides type conversions for these three data types. The rules are as follows:

Usage

Create the extension

CREATE EXTENSION ip4r;

Create a test table and import data

CREATE TABLE ipranges (r iprange, r4 ip4r, r6 ip6r);
INSERT INTO ipranges
    SELECT r, null, r
    FROM (
        SELECT ip6r(regexp_replace(ls, E'(....(?!$))', E'\\1:', 'g')::ip6,
               regexp_replace(substring(ls FOR n + 1) || substring(us FROM n + 2),
               E'(....(?!$))', E'\\1:', 'g')::ip6) AS r
        FROM (
            SELECT md5(i || ' lower 1') AS ls,
                   md5(i || ' upper 1') AS us,
                   (i % 11) + (i/11 % 11) + (i/121 % 11) AS n
            FROM generate_series(1,13310) i) s1) s2;

Create a GiST index

CREATE INDEX ipranges_r ON ipranges USING gist (r);

Use the contains operator

EXPLAIN (COSTS OFF) SELECT * FROM ipranges WHERE r >>= '5555::' ORDER BY r;

The following result is returned:

                     QUERY PLAN                      
-----------------------------------------------------
 Sort
   Sort Key: r
   ->  Bitmap Heap Scan on ipranges
         Recheck Cond: (r >>= '5555::'::iprange)
         ->  Bitmap Index Scan on ipranges_r
               Index Cond: (r >>= '5555::'::iprange)
(6 rows)

Uninstall the extension

DROP EXTENSION ip4r;