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@disturbed.net -=- alex.perel@inquent.com
play -=- work
Disturbed Networks - Powered exclusively by FreeBSD
== The Power to Serve -=- http://www.freebsd.org/
Alex Perel <veers@webhosting.com> writes:
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;
INSERT INTO ips_used
(
block_id,
ip,
contact_id
)
SELECT block_id
, ip
, '1000'
FROM ips_free
WHERE ip = '10.10.10.10'
Hmm. The rule will generate a query along these lines:
DELETE FROM ips_free
FROM ips_free ipsfree2
WHERE ips_free.block_id = ipsfree2.block_id
AND ips_free.ip = ipsfree2.ip
AND ipsfree2.ip = '10.10.10.10';
(I'm using ipsfree2 to convey the idea of a self-join similar to
"SELECT FROM ips_free, ips_free ipsfree2" ... I don't believe the
above is actually legal syntax for DELETE.)
This ends up deleting all your ips_free entries for ip = '10.10.10.10',
which seems to be what you want ... but I think the query added by
the rule is executed before the actual INSERT, which leaves you with
nothing to insert.
There's been some debate in the past about whether an ON INSERT rule
should fire before or after the INSERT itself. I lean to the "after"
camp myself, which would fix this problem for you. However, you are
treading right on the hairy edge of circular logic here. You might want
to think about using a trigger rather than a rule to do the deletes.
regards, tom lane
On Tue, 5 Dec 2000, Tom Lane wrote:
Hmm. The rule will generate a query along these lines:
DELETE FROM ips_free
FROM ips_free ipsfree2
WHERE ips_free.block_id = ipsfree2.block_id
AND ips_free.ip = ipsfree2.ip
AND ipsfree2.ip = '10.10.10.10';(I'm using ipsfree2 to convey the idea of a self-join similar to
"SELECT FROM ips_free, ips_free ipsfree2" ... I don't believe the
above is actually legal syntax for DELETE.)This ends up deleting all your ips_free entries for ip = '10.10.10.10',
which seems to be what you want ... but I think the query added by
the rule is executed before the actual INSERT, which leaves you with
nothing to insert.There's been some debate in the past about whether an ON INSERT rule
should fire before or after the INSERT itself. I lean to the "after"
camp myself, which would fix this problem for you. However, you are
treading right on the hairy edge of circular logic here. You might want
to think about using a trigger rather than a rule to do the deletes.
Thanks for the clarification - this is kind of what I suspected as
well, though I really don't understand the backend well enough to have a
clear picture. I would think that the SELECT takes place first, and the
results are passed to the INSERT at which time the rule fires but the results
of the SELECT are still in memory. I'm certainly wrong, but that's kind
of along the lines of what I was thinking would happen.
In any case, I solved the problem by splitting the SELECT off into a
seperate query and got rid of the headaches that way.
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/