Re: insert rule not firing on insert with exists subselect

OK, that makes sense. I guess we will be implementing some triggers.

Thanks,

-Chris

On Tuesday 13 April 2004 5:13 pm, Tom Lane wrote:
> Chris Kratz <chris(dot)kratz(at)vistashare(dot)com> writes:
> > What am I missing?
>
> You're thinking that the rule is a trigger. It's not. It's a query
> transformation mechanism that adds a new query to be executed after your
> INSERT. What actually gets executed is effectively
>
> Original query:
>
> insert into table2 (col1) select col1 from table1 where not exists
> (select 1 from table2 where table2.col1 = table1.col1);
>
> Query added by rule:
>
> insert into table3 (col1)
> select col1 from table1 where not exists
> (select 1 from table2 where table2.col1 = table1.col1);
>
> By the time the second query begins, there *is* a table2 row matching
> every row in table1, because you just got done inserting ones to match
> any that didn't have a match. So in the second query, the EXISTS test
> succeeds at every row of table1 and no rows are produced to insert into
> table3.
>
> This could be made to work if the order of the queries were reversed,
> but that isn't going to happen because it would break other uses of
> ON INSERT rules that need to be able to see the inserted row(s).
> So AFAICS you're gonna have to use a trigger.
>
> regards, tom lane

--
Chris Kratz
Systems Analyst/Programmer
VistaShare LLC
www.vistashare.com