Thread: insert rule not firing on insert with exists subselect

Hello all,

We have run into what appears to be a problem with rules and subselects in
postgres 7.4.1.  We have boiled it down to the following test case.  If
anyone has any thoughts as to why this would be happening, we would
appreciate feedback.

We have tested on 7.3.4, 7.3.6 and 7.4.1 and all exhibit the same behavior.
Test case one tries to populate table2 from table1 with records that are not
in table2 already.  Table2 gets populated correctly, but table3 does not as
one would expect with the rule on table2.

The second test case creates a temporary table to hold the values in table2
and the same statement is run to populate table2 except the exists statement
runs off of the temp table.  This works as expected.  The values get inserted
into table2 and table3.

As a side note, we rewrote this test case with a trigger and that worked as
expected.  Due to the complication of replacing a large number of rules, we
are hoping there is an answer as to why this is not working that is simpler
the rewriting as triggers.

Thanks,

-Chris

-- test case 1 - with exists subselect on same table
--               Fails to insert items into table3
-- cleanup
drop table table1;
drop table table2;
drop table table3;

-- set up stuff
create table table1 (col1 int);
create table table2 (col1 int);
create table table3 (col1 int);
insert into table1 (col1) values (1);
insert into table1 (col1) values (2);
insert into table1 (col1) values (3);
insert into table1 (col1) values (3);
insert into table1 (col1) values (4);
insert into table1 (col1) values (4);
insert into table1 (col1) values (4);
insert into table1 (col1) values (5);
create rule mycopyrule as on insert to table2 do insert into table3 (col1)
values (new.col1);

-- failing test
insert into table2 (col1) values (4); -- works
select count(*) from table3;
insert into table2 (col1) select col1 from table1 where not exists (select 1
from table2 where table2.col1 = table1.col1); -- doesn't work
select count(*) from table3; -- Should be 6, shows 1

-- test case 2 - w/o exists subselect on same table
--               Inserts records from table2 into table3
-- cleanup
drop table table1;
drop table table2;
drop table table2_hold;
drop table table3;

-- set up stuff
create table table1 (col1 int);
create table table2 (col1 int);
create table table3 (col1 int);
insert into table1 (col1) values (1);
insert into table1 (col1) values (2);
insert into table1 (col1) values (3);
insert into table1 (col1) values (3);
insert into table1 (col1) values (4);
insert into table1 (col1) values (4);
insert into table1 (col1) values (4);
insert into table1 (col1) values (5);
create rule mycopyrule as on insert to table2 do insert into table3 (col1)
values (new.col1);

-- passing test
insert into table2 (col1) values (4); -- works
select count(*) from table3;
create temp table table2_hold as select * from table2;
insert into table2 (col1) select col1 from table1 where not exists (select 1
from table2_hold where table2_hold.col1 = table1.col1);
select count(*) from table3; -- Shows 6 as expected
--
Chris Kratz
Systems Analyst/Programmer
VistaShare LLC

Chris Kratz <chris.kratz@vistashare.com> writes:
> create rule mycopyrule as on insert to table2 do insert into table3 (col1)
> values (new.col1);

> insert into table2 (col1) select col1 from table1 where not exists (select 1
> from table2 where table2.col1 = table1.col1); -- doesn't work

The rule-generated query executes after the original INSERT, and can see
its results.  So by the time the rule fires, there *is* an entry in
table2 matching the table1 value, and of course the EXISTS condition
fails.

Rules are not triggers and make poor substitutes for them.  You're going
to need to use a trigger for this...

            regards, tom lane

Thanks Tom for your quick response.

At the risk of sounding really stupid, how can this be the case?  The rule is
simply on an insert into table2, insert an additional record in table3.  The
subselect on the actual insert is the one using the exists clause and is
working correctly (as far as inserting the records).  The records are being
inserted into table2 as expected.  But the rule which should have fired for
each insert doesn't happen and table3 is empty.

What am I missing?

-Chris

On Tuesday 13 April 2004 4:40 pm, Tom Lane wrote:
> Chris Kratz <chris.kratz@vistashare.com> writes:
> > create rule mycopyrule as on insert to table2 do insert into table3
> > (col1) values (new.col1);
> >
> > insert into table2 (col1) select col1 from table1 where not exists
> > (select 1 from table2 where table2.col1 = table1.col1); -- doesn't work
>
> The rule-generated query executes after the original INSERT, and can see
> its results.  So by the time the rule fires, there *is* an entry in
> table2 matching the table1 value, and of course the EXISTS condition
> fails.
>
> Rules are not triggers and make poor substitutes for them.  You're going
> to need to use a trigger for this...
>
>             regards, tom lane

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

Chris Kratz <chris.kratz@vistashare.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

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.kratz@vistashare.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