Thread: Bug #771: rewrite rules on update or insert do not report errors

marc persuy (marc.persuy@wanadoo.fr) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
rewrite rules on update or insert do not report errors

Long Description
If you try to use rules to implement update and insert on views, these
rules do not report any error neither on insert nor on update action.

therefore, they are far less usefull.



Sample Code
for instance:
  here is a sample table to store customers or suppliers reference.
  table is shared by many users, each one having his/her own datas, defined by a view: tiers

the tiers view shows all datas from user and his/her partners, is supposed to allow update of his/her
own datas.

regrettably enough, when you try to update the tiers view, you never get any error, even
if you try to insert a record with an already existing primary key, or any undoable or forbidden operation.

-- Table: tiers_sys

CREATE TABLE tiers_sys (
num_tiers int4 DEFAULT nextval('num_Tseq'::text) NOT NULL,
owner_tag int4,
valid bool DEFAULT 't'::bool,
name varchar(100),
is_customer bool,
is_supplier bool,
country varchar(2),
dunsid varchar(13),
iban varchar(35),
vat_number varchar(20),
status varchar(5),
creation_date timestamp without time zone,
creat_user int4,
update_date timestamp without time zone,
update_user int4,
CONSTRAINT tiers_pkey PRIMARY KEY (num_tiers)
) WITH OIDS;

CREATE VIEW "tiers" AS SELECT * FROM tiers_sys
WHERE (tiers_sys.owner_tag = ANY (SELECT partners.owner_tag FROM partners));


CREATE RULE tiers_ins as on insert to tiers
DO INSTEAD insert into tiers_sys(owner_tag,
name, is_customer,is_supplier,country,dunsid,iban,vat_number,status
) values (
NEW.owner_tag, NEW.name, NEW.is_customer,NEW.is_supplier,NEW.country,
NEW.dunsid, NEW.iban, NEW.vat_number, NEW.status
);


CREATE RULE tiers_upd as on update to tiers
DO INSTEAD update  tiers_sys set
valid = NEW.valid  ,name = NEW.name ,
is_customer = NEW.is_customer,is_supplier = NEW.is_supplier ,
country = NEW.country,dunsid = NEW.dunsid,iban = NEW.iban ,vat_number = NEW.vat_number,status=NEW.status ,
where  num_tiers = OLD.num_tiers;



No file was uploaded with this report

pgsql-bugs@postgresql.org writes:
> If you try to use rules to implement update and insert on views, these
> rules do not report any error neither on insert nor on update action.

I see nothing in here that should raise an error.

> the tiers view shows all datas from user and his/her partners, is
> supposed to allow update of his/her own datas.

You seem to be thinking that the system should somehow intuit that
that's the behavior you want.  If you want it to act that way, you need
to code the rules to enforce it.  There is no magic to a rule: it does
what you tell it to do.

            regards, tom lane

"marc persuy" <marc.persuy@wanadoo.fr> writes:
> what I mean is if I use a rule to execute any action,
> the rule should report an error to the client if, for any reason, this
> action fails.

It does.

> inserting into my_view will always return a sucess condition,
> even if you try to use it to insert a record with an already existing
> primary key.

Oh?

regression=# create table foo (f1 int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'foo_pkey' for table 'foo'
CREATE TABLE
regression=# create view v as select * from foo;
CREATE VIEW
regression=# create rule v_ins as on insert to v do instead
regression-# insert into foo values(new.f1);
CREATE RULE
regression=# insert into v values (1);
INSERT 149486 1
regression=# insert into v values (1);
ERROR:  Cannot insert a duplicate key into unique index foo_pkey
regression=#

I don't see a problem.


            regards, tom lane