Re: Rule not invoked in 7.1

Jan Wieck wrote:

> Tom Lane wrote:
> > Kyle <kyle(at)actarg(dot)com> writes:
> > > ERROR: Cannot update a view without an appropriate rule.
> >
> > 7.1 insists that you provide an *unconditional* DO INSTEAD rule
> > for a view. What do you think was happening on your old database
> > when the "where old.status = 'appr'" clause wasn't satisfied?
> > Nothing good I'm afraid.
>
> No harm in the UPDATE case, because so far there aren't any
> tuples in the view that could be affected by the still
> executed original query. But in an INSERT case, it would let
> tuples through into the views heap file.
>
> > If you really do need conditional rules, you can satisfy the check
> > by writing one unconditional DO INSTEAD NOTHING rule and then one
> > or more conditional non-INSTEAD rules. But you should think carefully
> > about what you expect to happen when you use a conditional rule.
>

I'm using the view as a way of restricting a single class of users to only
update tuples that have a certain status in the table. Isn't this
essentially what a "dynamic view" is?

If someone happens to know the primary key of a record they should not be
able to access, and they try to update it, I would like the backend to
ignore the query (or better yet, raise an exception but I haven't figured
out how to do that). If the status is correct, the update should proceed.

I've inserted the dummy do nothing rule as follows:

create view pay_req_v_prl as select
empl_id,wdate,seq,hours,hot,proj,entby,paytyp,status,poinum,added,rate,otrate,appby,gross,rgross,cost,ttype,expnum,oid
as _oid from pay_req;

create rule pay_req_v_prl_upnull as on update to pay_req_v_prl do instead
nothing;
create rule pay_req_v_prl_update as on update to pay_req_v_prl
where old.status = 'appr' do instead
update pay_req set status = new.status, gross = new.gross, cost =
new.cost,
ttype = new.ttype, expnum = new.expnum, rgross = new.rgross, hot =
new.hot
where empl_id = old.empl_id and wdate = old.wdate and seq = old.seq;

This seems to work now when I do:

psql ati -c "update pay_req_v_prl set gross = 90.09 where empl_id = 1010
and wdate = '2001-01-08' and seq = 1;"

You see any problems with this method?

BTW, the update still returns UPDATE 0 from psql even though a record was
updated. I've never quite figured out why views with rules do this.

I've also done some testing on 7.1 for that nasty thing in 7.0 where you
had to give select,update privs to a table referenced by a foreign key. So
far, looks good. I was able to reference a table that the user didn't have
privs to at all. I think that is the desired behavior.

Good work guys! 7.1 is looking good.