Re: [HACKERS] about RULES - Mailing list pgsql-hackers

>
> Hi all,
>
> I have a question about rules. I create a rule to insert data into a
> view that works fine,
> but when I add a row to the view, PostgreSQL replies INSERT 0 0 if the
> row is added and it replies
> INSERT oid# 1 if the row is not added.
> I have the same behavior with RULES on UPDATE and on DELETE.

    Anything is fine and behaves as (I) expected :-)

    I  know, it's a little irritating. The reply you're seeing is
    the result of the last processed query. Your rule ...

>
> Here the example:
>
>
> create rule "_RIT_vista" as on insert to vista
>     where new.job='SALESMAN'
>     do instead
>     insert into emp (empno,ename,job) values
> (new.empno,new.ename,new.job);
> CREATE

     ... is a conditional one (has  a  rule  qualification  WHERE
    new.job = 'SALESMAN').  In this case, the rule system split's
    your query into one that  inserts  into  "emp"  if  rule-qual
    matched  and  another  that  inserts into "vista" if not. The
    query to insert into "emp" is executed first,  the  one  that
    inserts into "vista" second.

>
> insert into vista values ('8900','MANUEL','SALESMAN');
> INSERT 0 0        <<<<<<<<<<------------------------- Why it replies
> INSERT 0 0 if it adds the row ?
> ^^^^^^^^^^^

    1 row inserted into "emp", 0 rows inserted into "vista".

>
> select * from vista;
> empno|ename     |job
> -----+----------+------------
>  8900|MANUEL    |SALESMAN
> (1 row)
>
> insert into vista values ('8901','JOSE','PROGRAMMER');
> INSERT 144991 1    <<<<<<<<<------------------------- Why it replies
> INSERT oid 1 if it adds no row?
> ^^^^^^^^^^^^^^^

    0 rows inserted into "emp", 1 row inserted into "vista"!

>
> select * from vista;
> empno|ename     |job
> -----+----------+------------
>  8900|MANUEL    |SALESMAN
> (1 row)
>
> select oid,* from emp;
>    oid|ename     |empno|job         |hiredate|sal|comm|deptno|level|mgr
> ------+----------+-----+------------+--------+---+----+------+-----+---
> 144990|MANUEL    | 8900|SALESMAN    |        |   |    |      |     |
> (1 row)
>

    Now  you  wonder  where Postgres left 'JOSE' - eh? He's there
    and all information is kept. But you're  unable  to  see  it,
    because  on  SELECT  from "vista", the rewrite system changes
    your query to something that displays from "emp". But if  you
    take  a look into the database directory, you'll see that the
    file "vista" lost it's zero-size. So there is data!

    You can't update, delete or see him, as long as the _RETvista
    rule  is  active.  Any query that needs to scan "vista" get's
    rewritten to scan from "emp".

    It's a problem of a general-purpose  production  rule  system
    like  the one Postgres has. It does not (and IMHO should not)
    check, whether the summary of all defined rules makes  sense.
    Such a check would make it impossible to setup many qualified
    rules, because they have circular dependencies that cannot be
    matched when you created the first ones.

    In  the case of a qualified instead rule, the parsetree get's
    splitted.  One with the qual, one with the negated  qual.  If
    you  only  want  'SALESMAN's,  you must put the qualification
    into the INSERT action of the rule as

    create rule "_INSvista" as on insert to vista
        do instead
        insert into emp select new.empno, new.ename, new.job
        where new.job='SALESMAN';

    This is an unqualified instead rule, where the ACTION filters
    the  data.  This time, the rewrite system will throw away the
    original query and only the INSERT INTO emp ... is left,  but
    it  will  never  insert  anything than row's of SALESMAN. All
    others are silently thrown away.

    Changing from INSERT ... VALUES to INSERT ... SELECT  is  the
    way  to  make  the  parser  happy  about the WHERE clause. On
    parsetree level, there is no difference between them.

    Well, I expect more questions now - so come on.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #