I have an IP Address allocation system that uses a networks table like
so:
CREATE TABLE "networks" ( "netblock" cidr, "router" integer, "interface" character varying(256),
"dest_ip" inet, "mis_token" character(16), "assigned_date" date, "assigned_by" character
varying(256), "justification_now" integer, "justification_1yr" integer, "cust_asn" integer,
"comments"character varying(2048), "other_reference" character varying(256), "parent_asn" integer,
"status"integer, "purpose" integer, "last_update_by" character varying(256), "last_update_at"
timestampwith time zone, "customer_reference" integer
);
When I go looking for an available netblock, I do the following query:
BEGIN TRANSACTION;
SELECT host(netblock),masklen(netblock),netblock,netmask(netblock)
FROM networks
WHERE parent_asn=xxxx AND
status=get_status_code('available') AND
masklen(netblock) = xxx FOR UPDATE LIMIT 1;
(if this fails, we go looking for a /24 to bust up, and if we can find
that we add new available rows for that, and retry this query).
get_status_code is a function to look up a number based on text in
another table (not marked cacheable at the moment, but should it be? )
My questions are:
1) if this code is running twice for the same size block what will
happen ?
2) what can I do to make this more efficient?
the table will contain ~5000 rows to begin with.
LER
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
