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
WHERE ips_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_free
WHERE 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(at)disturbed(dot)net -=- alex(dot)perel(at)inquent(dot)com
play -=- work

Disturbed Networks - Powered exclusively by FreeBSD
== The Power to Serve -=- http://www.freebsd.org/