Postgres Pro
Downloads
Home   >   mailing lists

Thread: INSERT INTO ... SELECT problem

INSERT INTO ... SELECT problem

From
Alex Perel
Date:
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/

Re: INSERT INTO ... SELECT problem

From
Tom Lane
Date:
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

Re: INSERT INTO ... SELECT problem

From
Alex Perel
Date:
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/