Thread

1. Rules with Conditions: Still Doesn't Work (Bug Rpt)

   Joel Burton <jburton@scw.org> — 2000-12-04T16:16:59Z

   (sorry for cross-posting a bug report directly to pgsql-
   hackers, but it started here, and I know it's still a 
   topic of conversation.)
   
   About a week ago, I posted to pgsql-bugs and pgsql-
   hackers a VIEW that shows all user table fields 
   w/comment, if any, as well as 2 RULES that tried to 
   allow for updates to this view that would either (as 
   appropriate) INSERT to pg_description, or UPDATE to 
   pg_description.
   
   Tom Lane pointed out that, given the current handling of 
   conditional rules, this should be one rule, with two 
   actions.
   
   For, this either fails to work, or in some circumstances,
   or in some circumstances, crashes the backend.
   
   The problems are demonstrated below, with non-system 
   tables. I show a few possible attempts at a workable 
   solutions, but all either:
   
   - insert nothing
   - complain about "JOIN qualification may not refer
   -- to other relations"
   - drop connection to backend
   
   Is this an actual bug? Am I missing something still?
   
   Thanks!
   
   ------------------------------------------
   
   -- following is a simplified case that uses non-system
   -- tables
   -- updating existing records works, but not update-
   -- this becomes insert
   
   create table p (pid int not null primary key, txt text);
   insert into p values (1,'parent1');
   insert into p values (2,'parent2');
   
   create table c (pid int, descrip text);
   insert into c values (1,'descrip1');
   
   create view v as select p.pid, txt, descrip from p left 
   outer join c on p.pid=c.pid;
   
   create rule r as
    on update to v do instead 
     ( insert into c (pid, descrip) select old.pid, 
   new.descrip where old.descrip isnull; 
       update c set descrip=new.descrip where 
   c.pid=old.pid; 
     );
   
   update v set descrip='test' where pid=1;
   select * from v;                    -- works fine
   update v set descrip='test' where pid=2;
   select * from c;                    -- doesn't work?
   
   
   -- perhaps the problem is that the update fails on new
   -- records, since there was no old pid?
   -- try with slightly different rule (update is
   -- different):
   drop rule r;
   create rule r as 
    on update to v do instead 
     ( insert into c (pid, descrip) select old.pid, 
   new.descrip where old.descrip isnull; 
       update c set descrip=new.descrip where 
   c.pid=new.pid; );
   
   update v set descrip='test' where pid=2;
   -- gets error "ERROR:  JOIN qualification may not refer
   -- to other relations"
   
   
   -- try again w/no update action at all, just see if the
   -- insert action should work:
   drop rule r;
   create rule r as 
    on update to v do instead 
     ( insert into c (pid, descrip) select old.pid, 
   new.descrip );
   
   update v set descrip='test' where pid=2;
   -- inserts w/pid=0, so old.pid obviously <> 2 (?)
   
   
   -- try again w/new.pid instead of old.pid
   drop rule r;
   create rule r as 
    on update to v do instead 
     ( insert into c (pid, descrip) select new.pid, 
   new.descrip );
   
   update v set descrip='test' where pid=2;
   -- gets error "ERROR:  JOIN qualification may not refer
   -- to other relations"
   
   
   -- try w/o select but w/values
   drop rule r; 
   create rule r as 
    on update to v do instead 
     ( insert into c (pid, descrip) values( new.pid, 
   new.descrip ) );
   
   update v set descrip='test' where pid=2;
   -- gets error "backend closed the channel unexpectedly."
   
   
   -- try w/o "new', but with current
   drop rule r; 
   create rule r as 
    on update to v do instead 
     ( insert into c (pid, descrip) values( current.pid, 
   current.descrip ) );
   
   update v set descrip='test' where pid=2;
   -- gets error "backend closed the channel unexpectedly."
   
   
   -- try w/different view using UNION rather than OUTER
   -- JOIN
   drop view v;
   create view v as 
   select p.pid, txt, descrip 
   from p join c on p.pid=c.pid 
   union 
   select p.pid, txt, null 
   from p 
   where not exists (select * from c where c.pid=p.pid);
   
   -- recreate rule that didn't work because of "JOIN
   -- qualification"
   drop rule r; 
   create rule r as 
    on update to v do instead 
     ( insert into c (pid, descrip) select new.pid, 
   new.descrip );
   
   update v set descrip='test' where pid=2;
   -- gets error "ERROR:  JOIN qualification may not refer
   -- to other relations"
   
   
   -- try w/subselected view rather than UNION or OUTER JOIN
   create view v as 
   select p.pid, txt, (select descrip from c where 
   c.pid=p.pid) as descrip 
   from p;
   
   -- recreate rule that didn't work because of "JOIN
   -- qualification"
   drop rule r; 
   create rule r as 
    on update to v do instead 
     ( insert into c (pid, descrip) select new.pid, 
   new.descrip );
   
   update v set descrip='test' where pid=2;
   -- gets error "ERROR:  JOIN qualification may not refer
   -- to other relations"
   
   
   
   
   
   
   
   
   
   
   --
   Joel Burton, Director of Information Systems -*- jburton@scw.org
   Support Center of Washington (www.scw.org)
   
   

2. Re: Rules with Conditions: Still Doesn't Work (Bug Rpt)

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-12-05T04:29:47Z

   "Joel Burton" <jburton@scw.org> writes:
   > The problems are demonstrated below, with non-system tables.
   
   Wow, what a great bunch of test cases!
   
   You've exposed a number of bugs in the current sources.  I'm working
   on fixes, but have run out of steam for today...
   
   			regards, tom lane
   
   

3. Re: Rules with Conditions: Still Doesn't Work (Bug Rpt)

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-12-05T19:21:08Z

   "Joel Burton" <jburton@scw.org> writes:
   > Tom Lane pointed out that, given the current handling of 
   > conditional rules, this should be one rule, with two 
   > actions.
   > For, this either fails to work, or in some circumstances,
   > or in some circumstances, crashes the backend.
   
   It turns out that all of these failures come from the same place,
   which is that I had broken the handling of INSERT ... SELECT actions
   inside rules.
   
   I have committed a patch that appears to fix the problem.
   I think that Marc has already tagged beta1, however, so the patch
   will not be in the beta1 tarball due to appear Real Soon Now.
   You can pull current sources from the CVS server, or grab a nightly
   snapshot tarball after tonight.
   
   Thanks for the detailed, easy-to-reproduce bug example!  These sorts of
   reports are by far the nicest from a developer's point of view ;-)
   
   			regards, tom lane
   
   

4. Re: Rules with Conditions: Still Doesn't Work (Bug Rpt)

   Joel Burton <jburton@scw.org> — 2000-12-05T20:26:37Z

   > It turns out that all of these failures come from the same place,
   > which is that I had broken the handling of INSERT ... SELECT actions
   > inside rules.
   > 
   > I have committed a patch that appears to fix the problem.
   > I think that Marc has already tagged beta1, however, so the patch will
   > not be in the beta1 tarball due to appear Real Soon Now. You can pull
   > current sources from the CVS server, or grab a nightly snapshot
   > tarball after tonight.
   
   Fast work! (Boy, I sure wish I had a commercial Oracle/M$ contract 
   instead. I'm sure they could have done it faster ;-) ). Thanks!
   
   Just to be clear (as much for other as for me): this should fix the 
   syntax as my (more recent) bug report submitted? That is, one non-
   conditional rule that tries to handle both inserting and updating 
   together, as opposed to 2 different conditional rules that, together, 
   try to handle all cases.
    
   > Thanks for the detailed, easy-to-reproduce bug example!  These sorts
   > of reports are by far the nicest from a developer's point of view ;-)
   
   My Mom always said I loved finding problems. :-)
   
   
   
   --
   Joel Burton, Director of Information Systems -*- jburton@scw.org
   Support Center of Washington (www.scw.org)
   
   

5. Re: Rules with Conditions: Still Doesn't Work (Bug Rpt)

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-12-05T20:42:54Z

   "Joel Burton" <jburton@scw.org> writes:
   > Just to be clear (as much for other as for me): this should fix the 
   > syntax as my (more recent) bug report submitted? That is, one non-
   > conditional rule that tries to handle both inserting and updating 
   > together, as opposed to 2 different conditional rules that, together, 
   > try to handle all cases.
   
   Right.  The unconditional rule definitely *should* work; you were
   just running into bugs in rewriting INSERT...SELECT actions, which
   really had nothing to do with whether the rule is conditional or not.
   (Had you written INSERT ... VALUES, you'd have seen no problem.)
   
   I'm not convinced yet whether the system should accept conditional
   rules to implement views or not.
   
   BTW, the following hack seems to work fine (as of current sources
   anyway) if you are really intent on a bunch of conditional rules:
   make an unconditional "do instead nothing" plus as many conditional
   add-on rules as you want.  For example,
   
   create view foo as select * from int4_tbl;
   
   select * from foo;
        f1
   -------------
              0
         123456
        -123456
     2147483647
    -2147483647
   (5 rows)
   
   insert into foo values (23);
   ERROR:  Cannot insert into a view without an appropriate rule
   
   -- Just to keep the rewriter from complaining:
   create rule foo1 as on insert to foo do instead nothing;
   
   create rule foo2 as on insert to foo where new.f1 > 100
   do insert into int4_tbl values(new.f1);
   
   create rule foo3 as on insert to foo where new.f1 < 0
   do insert into int4_tbl values(- new.f1);
   
   insert into foo values (123);
   INSERT 145131 1
   insert into foo values (-1000);
   INSERT 145132 1
   insert into foo values (10);
   INSERT 0 0
   
   select * from foo;
        f1
   -------------
              0
         123456
        -123456
     2147483647
    -2147483647
            123
           1000
   (7 rows)
   
   This also gives you well-defined behavior if none of the conditional
   rules fire: nothing happens.
   
   			regards, tom lane