INSERT INTO ... SELECT problem

Hi everyone,

I've recently encountered a bizzare problem that manifests itself reliably
on my running copy of postgres. I have a system set up to track IPs. The 
arrangement uses two mutually-exclusive buckets, one for free IPs and
the other for used ones. There are rules set up on the used pool to
remove IPs from the free on insert, and re-add them on delete.

The structure of the tables is:

CREATE TABLE "ips_free" (       "block_id" int4 NOT NULL,       "ip" inet NOT NULL,       "contact_id" int4,
"alloc_type"int4,       PRIMARY KEY ("block_id", "ip")
 
);

CREATE TABLE "ips_used" (       "block_id" int4 NOT NULL,       "ip" inet NOT NULL,       "contact_id" int4,
"alloc_type"int4,       PRIMARY KEY ("block_id", "ip")
 
);

The applicable rule that acts on inset to ips_used is:

CREATE RULE ip_allocated_rule AS         ON INSERT         TO ips_used        DO DELETE FROM ips_free
WHEREips_free.block_id = NEW.block_id                   AND ips_free.ip       = NEW.ip;
 

When I tried to minimize the total number of queries in a data load, I
tried to get the block ID (see above for the schema definition) using
INSERT INTO ... SELECT. A query like

INSERT INTO ips_used  (  block_id,  ip,  contact_id )
SELECT block_id    , ip    , '1000' FROM ips_freeWHERE ip = '10.10.10.10'

simply reutrns with "INSERT 0 0" and in fact removes the IP from the
free bucket without adding it to the USED bucket. I really can't
explain this behavior and I'm hoping someone can shed a little bit of
light on it. 

I am running PostgreSQL 7.0.0 on sparc-sun-solaris2.7, compiled by gcc 2.95.2

Thanks


Alex


--      Alex G. Perel  -=-  AP5081
veers@disturbed.net  -=-  alex.perel@inquent.com              play  -=-  work                       
Disturbed Networks - Powered exclusively by FreeBSD
== The Power to Serve -=- http://www.freebsd.org/