PostgreSQL IP池

Question

我有以下函数从池中获得一个免费的IP地址：

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;

这似乎是可行的，但我不确定在收回IP并插入到表中时是否需要锁定表。

我正在检查IP是否已经存在，以及标识所需的IP是否有一个地址。如果发生故障，则返回0.0.0.0。

inp_id是请求IP的客户机，ips表有2列：ip和id，用于匹配客户机ID和IP。

Answer 1

你的职能似乎很复杂，我不相信它是在做它的工作。

如果ips是空的，那么它就不能工作，而且您永远也不会得到低于表中最低地址的地址，所以您必须确保这个最低地址永远不会从表中删除。

不管怎么说，关于你的问题：

我想您希望避免将相同的地址返回给函数的并发调用方。

为此，在UNIQUE上创建一个ips约束就足够了，该约束禁止两次添加相同的IP地址。

然后，您应该在INSERT期间捕获该错误，并在发生错误时重试整个操作。

这是我对你的功能的看法。

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;$$;

即使您不喜欢我的方法，您也可以理解我使用循环的意思。