Received: from actarg.com (host25.actarg.com [209.180.91.25] (may be forged)) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0PHRVq76388 for ; Thu, 25 Jan 2001 12:27:31 -0500 (EST) (envelope-from kyle@actarg.com) Received: from chi.actarg.com (IDENT:root@chi.actarg.com [192.168.2.16]) by actarg.com (8.9.3/8.9.3) with ESMTP id KAA13058; Thu, 25 Jan 2001 10:27:26 -0700 Received: from actarg.com (IDENT:kyle@localhost [127.0.0.1]) by chi.actarg.com (8.9.3/8.9.3) with ESMTP id KAA21644; Thu, 25 Jan 2001 10:27:39 -0700 Message-ID: <3A706208.AD6AB9@actarg.com> Date: Thu, 25 Jan 2001 10:27:36 -0700 From: Kyle Organization: Action Target Inc. X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.12-20 i686) X-Accept-Language: en MIME-Version: 1.0 To: Tom Lane CC: Jan Wieck , pgsql-sql@postgresql.org Subject: Re: Rule not invoked in 7.1 References: <200101251321.IAA02253@jupiter.greatbridge.com> Content-Type: multipart/mixed; boundary="------------02781DDADCB71FFBA4FB3C25" X-Archive-Number: 200101/357 X-Sequence-Number: 1030 This is a multi-part message in MIME format. --------------02781DDADCB71FFBA4FB3C25 Content-Type: multipart/alternative; boundary="------------F3ED4B7F6540DD1E0E329179" --------------F3ED4B7F6540DD1E0E329179 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Jan Wieck wrote: > Tom Lane wrote: > > Kyle 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. --------------F3ED4B7F6540DD1E0E329179 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Jan Wieck wrote:
Tom Lane wrote:
> Kyle <kyle@actarg.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.
 
  --------------F3ED4B7F6540DD1E0E329179-- --------------02781DDADCB71FFBA4FB3C25 Content-Type: text/x-vcard; charset=us-ascii; name="kyle.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for Kyle Content-Disposition: attachment; filename="kyle.vcf" begin:vcard n:Bateman;Kyle x-mozilla-html:FALSE org:Action Target Inc. adr:;;;;;; version:2.1 email;internet:kyle@actarg.com title:President x-mozilla-cpt:;0 fn:Kyle Bateman end:vcard --------------02781DDADCB71FFBA4FB3C25--