CODEWITHSUNDEEP
sqlpostgresql
Alfred Balle
Alfred Balle

Reputation: 1195

PostgreSQL IP pool

I've got the following function to get a free IP address from a pool:

CREATE OR REPLACE FUNCTION get_ip(inp_id CHARACTER(9)) RETURNS INET AS $$
    DECLARE ip_assigned INET;
    BEGIN
        ip_assigned := (COALESCE((SELECT ip FROM ips WHERE id = inp_id),
                                 (SELECT (a.ip + 1) AS ip
                                  FROM ips a LEFT JOIN LATERAL (SELECT * FROM ips b WHERE a.ip < b.ip ORDER BY b.ip ASC LIMIT 1) AS q ON true
                                  WHERE q.ip <> (a.ip + 1)
                                  ORDER BY ip ASC LIMIT 1)));

        IF NOT EXISTS (SELECT 1 FROM ips WHERE id = inp_id) AND NOT EXISTS (SELECT 1 FROM ips WHERE ip = ip_assigned) THEN
            INSERT INTO ips VALUES (ip_assigned, inp_id);
            RETURN ip_assigned;
        ELSEIF EXISTS (SELECT 1 FROM ips WHERE id = inp_id AND ip = ip_assigned) THEN
            RETURN ip_assigned;
        ELSE
            RETURN '0.0.0.0';
        END IF;

    END;
$$ LANGUAGE plpgsql;

It seems to work, but I'm unsure if there could be any need for locking the table when retreiving IP and inserting into the table.

I'm checking if the IP exists already, and if the id requiring and IP has an address. 0.0.0.0 is returned if something fails.

inp_id is the client requesting an IP, and the ips table has 2 columns; ip and id, for matching client ID and IP.

Upvotes: 1

Views: 223

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246033

Your function seems very complicated, and I am not convinced that it is doing its job at all.

It won't work if ips is empty, and you'll never get an address lower than the lowest address in the table, so you'd have to make sure that this lowest address is never deleted from the table.

Anyway, to your question:

I guess that you want to avoid that the same address is returned to concurrent callers of the function.

For that, it would be enough to create a UNIQUE constraint on ips that forbids adding the same IP address twice.

Then you should catch that error during INSERT and retry the whole operation in case of error.

Here is my version of your function. 

CREATE TABLE IF NOT EXISTS ips(
   ip inet UNIQUE NOT NULL,
   id character(9) PRIMARY KEY 
);  

CREATE OR REPLACE FUNCTION get_ip(inp_id character(9)) RETURNS inet
   LANGUAGE plpgsql STRICT AS
$$DECLARE
   min_ip inet := '192.168.0.0';
   max_ip inet := '192.168.255.255';
   new_ip inet;
BEGIN
   /* loop until we find and can insert a new address */
   LOOP
      BEGIN
         /* don't do anything if the entry already exists */
         SELECT ip INTO new_ip
         FROM ips 
         WHERE id = inp_id;

         IF new_ip IS NOT NULL THEN
            RETURN new_ip;
         END IF; 

         /* see if the lowest IP address is free */
         IF NOT EXISTS (SELECT 1 FROM ips 
                        WHERE ip = min_ip)
         THEN
            /* attempt to insert the new row */
            INSERT INTO ips (ip, id) 
            VALUES (min_ip, inp_id);

            /* return if that was successful */
            RETURN min_ip;
         END IF;    
         /* else, get the lowest IP address gap in "ips" */
         SELECT ip + 1 INTO new_ip
         FROM (SELECT ip,
                      CASE WHEN lead(ip) OVER (ORDER BY ip) = ip + 1
                           THEN FALSE
                           ELSE TRUE
                      END AS followed_by_gap
               FROM ips) subq
         WHERE followed_by_gap
         ORDER BY ip
         LIMIT 1;

         /* must not exceed maximum */
         IF new_ip > max_ip THEN
            RAISE EXCEPTION 'no free IP address found';
         END IF;
         /* if the table is still empty, use the minimum */
         IF new_ip IS NULL THEN
            new_ip := min_ip;
         END IF;

         /* attempt to insert the new row */
         INSERT INTO ips (ip, id)
         VALUES (new_ip, inp_id);

         /* return if that was successful */
         RETURN new_ip;
      EXCEPTION
         WHEN unique_violation THEN
            /* retry in another loop execution */
            NULL;
      END;
   END LOOP;
END;$$;

Even if you don't like my approach, you can see what I mean with using a loop.

Upvotes: 1

Related Questions