Re: INSERT INTO ... SELECT problem

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(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/