Well, I thought the explanation (visibility of rows) was clear and
that I understood rules. My tests make me more confused, though,
because it doesn't seem to explain everything.
Here is the relevant rule:
create rule surveys_ins as on insert to surveys do instead insert into survey_data (survey_date, name)
selectnew.survey_date, new.name where not exists (select * from survey_data d where d.survey_date = new.survey_date
andd.name = new.name);
The intent is to prevent insertion when the information (survey_date,
name combination) already exists in the table. My earlier problem was
that if an INSERT INTO ... SELECT contained duplicates the entire
query would be aborted. The suggestion was that this was because rows
inserted by the rule are invisible to the subselect within the rule
action; as a result, the insert would proceed for a duplicate row and
be rejected by the underlying unique index.
This implies (to me) that if the INSERT INTO ... SELECT contains no
duplicates but does duplicate entries already in the table (which
should be visible to the subselect), then the duplicate to-be-inserted
rows should be filtered out by the rule action.
This does not happen (see script below). What is wrong here? my
understanding of how subselects work in rules? the rule system?
something else?
Thanks again for your help.
Cheers,
Brook
===========================================================================
drop sequence survey_data_id_seq;
drop table survey_data;
create table survey_data
(id serial,survey_date date not null,name text not null,unique (survey_date, name)
);
drop view surveys;
create view surveys as
select id, survey_date, name from survey_data;
create rule surveys_ins as on insert to surveys
do instead
insert into survey_data (survey_date, name)select new.survey_date, new.name where not exists(select * from survey_data
dwhere d.survey_date = new.survey_date and d.name = new.name);
insert into surveys (survey_date, name) values ('1999-02-14', 'Me');
insert into surveys (survey_date, name) values ('1999-02-15', 'Me');
insert into surveys (survey_date, name) values ('1999-02-14', 'You');
insert into surveys (survey_date, name) values ('1999-02-14', 'You'); -- correctly ignored by rule
insert into surveys (survey_date, name) values ('1999-02-15', 'You');
insert into surveys (survey_date, name) select '1999-02-15', 'You'; -- correctly ignored by rule
select * from surveys order by id;
drop table X;
create table X
(survey_date date,name text,unique (survey_date, name)
);
insert into X (survey_date, name) values ('1999-02-18', 'Us'); -- new
insert into X (survey_date, name) values ('1999-02-14', 'Us'); -- new
insert into X (survey_date, name) values ('1999-02-18', 'Me'); -- new
insert into X (survey_date, name) values ('1999-02-14', 'Me'); -- duplicates table entry
-- with unique index on underlying table, this does not succeed
-- even though the duplicates should already be visible to the rule action condition
-- and therefore filtered out
insert into surveys (survey_date, name) select survey_date, name from X;
select * from surveys order by id;
-- try again without duplicate
delete from X;
insert into X (survey_date, name) values ('1999-02-18', 'Us'); -- new
insert into X (survey_date, name) values ('1999-02-14', 'Us'); -- new
insert into X (survey_date, name) values ('1999-02-18', 'Me'); -- new
-- this succeeds
insert into surveys (survey_date, name) select survey_date, name from X;
select * from surveys order by id;
drop table X;
