Thread

rules bug?

Brook Milligan <brook@trillium.nmsu.edu> — 1999-04-28T06:54:45Z

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 is not being
  obeyed.

- with a unique index:  the error message below occurs and nothing is
  inserted into the table, again an indication that the condition 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 d where 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;