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
