Re: insert rule not firing on insert with exists subselect

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