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 ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
