rules bug? - Mailing list pgsql-hackers
From | Brook Milligan |
---|---|
Subject | rules bug? |
Date | |
Msg-id | 199904280654.AAA23244@trillium.nmsu.edu Whole thread Raw |
List | pgsql-hackers |
I have created a table/view/rule combination (see script below) that should enable insertion into the view in a manner that pretty much parallels Jan's documentation of the rule system. I think the only feature that differs is that the underlying table should maintain a unique combination of fields. As a result, the insert rule has been modified from the docs to try to prevent insertion if the combination already exists in the table. A unique index can be added to the table as well, but that does not effect the bug I think I've uncovered. All works well when individual INSERT commands are used; even duplicates are silently ignored as expected. If I use a INSERT INTO ... SELECT to do the insertion (again with duplicates), however, I get one of two responses depending on whether or not there is a unique index on the underlying table: - no unique index: all duplicates get inserted into the table, an indication that the condition imposed within the rule isnot being obeyed. - with a unique index: the error message below occurs and nothing is inserted into the table, again an indication that thecondition is not being obeyed. ERROR: Cannot insert a duplicate key into a unique index Clearly, something different (and incorrect) occurs for INSERT INTO .. SELECT compared with just INSERT. If the same rules are being used, why are the duplicates ignored for INSERT but not for INSERT INTO ... SELECT? Is this a bug in the rule system or in my rules? Thanks 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) -- uncomment to induce "duplicate key" errors ); 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'); -- 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'; -- ignored by rule select * from surveys order by survey_date, name; delete from survey_data; drop table X; create table X (survey_date date,name text ); insert into X (survey_date, name) values ('1999-02-14', 'Me'); insert into X (survey_date, name) values ('1999-02-15', 'Me'); insert into X (survey_date, name) values ('1999-02-14', 'You'); insert into X (survey_date, name) values ('1999-02-14', 'You'); -- NOT ignored by rule insert into X (survey_date, name) values ('1999-02-15', 'You'); insert into X (survey_date, name) values ('1999-02-15', 'You'); -- NOT ignored by rule -- if unique index on underlying table, then none of these inserts succeed -- otherwise all of them do, including the duplicates insert into surveys (survey_date, name) select survey_date, name from X; drop table X; select * from surveys order by survey_date, name;
pgsql-hackers by date: