From: | Larry Rosenman <ler(at)lerctr(dot)org> |
---|---|
To: | PostgreSQL Hackers List <pgsql-hackers(at)postgresql(dot)org> |
Subject: | query/locking/efficiency question |
Date: | 2001-05-27 17:37:05 |
Message-ID: | 20010527123705.A21671@lerami.lerctr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
From | Date | Subject | |
---|---|---|---|
Next Message | D'Arcy J.M. Cain | 2001-05-27 19:14:27 | Re: New/old style trigger API |
Previous Message | Tom Lane | 2001-05-27 17:11:36 | Re: New/old style trigger API |