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" timestamp with 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

Sun, 27 May 2001 12:37:05 -0500
Larry Rosenman <ler@lerctr.org>
query/locking/efficiency question