Re: [HACKERS] about RULES

>
> 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(at)debis(dot)com (Jan Wieck) #