Thread: 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.
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
insert into vista values ('8900','MANUEL','SALESMAN');
INSERT 0 0 <<<<<<<<<<------------------------- Why it replies
INSERT 0 0 if it adds the row ?
^^^^^^^^^^^
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?
^^^^^^^^^^^^^^^
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)
Any ideas ?
-Jose'-
>
> 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) #
> > 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'; I see that rules is more complex than I thougth. I tried your example Jan but it doesn't work... create rule "_INSvista" as on insert to vista do instead insert into emp select new.empno, new.ename, new.job where new.job='SALESMAN'; ERROR: Type of empno does not match target column ename -Jose'-
Jose' Soares ha scritto:
> >
> > 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';
>
> I see that rules is more complex than I thougth.
>
> I tried your example Jan but it doesn't work...
>
> create rule "_INSvista" as on insert to vista
> do instead
> insert into emp select new.empno, new.ename, new.job
> where new.job='SALESMAN';
> ERROR: Type of empno does not match target column ename
>
> -Jose'-
I had no reply to this message probably because a problem with my mail.
I'm trying to make a view updatable using triggers, INSERT works fine
but UPDATE/DELETE
doesn't because the WHERE condition is evaluated FALSE all the time.
Here an example:
create table emp ( empno int, ename char(10), job char(12), hiredate date, sal
money, comm int, deptno int, level int, mgr int
);
CREATE
insert into emp values (8900,'MANUEL','SALESMAN',CURRENT_DATE,'$2,000');
INSERT 149844 1
create table vista as select empno, ename, job from emp where job='SALESMAN';
CREATE
drop function add_vista();
create function add_vista() returns opaque as '
begin raise notice ''trigger fired: % on %'',tg_when,tg_op; return null;
end;
' language 'plpgsql';
create trigger t_add_vista before insert or update or delete on vista for each row execute procedure add_vista();
delete from vista where ename='MANUEL';
NOTICE: trigger fired: BEFORE on DELETE
DELETE 0
delete from vista ;
NOTICE: trigger fired: BEFORE on DELETE
DELETE 0
--And now VISTA becames a view:.................
CREATE RULE "_RETvista" AS ON SELECT TO "vista" DO INSTEAD SELECT "empno", "ename", "job" FROM "emp" WHERE "job" =
'SALESMAN'::"bpchar";
CREATE
select * from vista;
empno|ename |job
-----+----------+------------8900|MANUEL |SALESMAN
(1 row)
-- The where condition is all the time evaluated as FALSE...
delete from vista where ename='MANUEL'; --why this condition isn't true
?
DELETE 0
delete from vista ;
NOTICE: trigger fired: BEFORE on DELETE
DELETE 0
--Is there a way to make views updatable ?
-Jose'-
> > I tried your example Jan but it doesn't work...
> >
> > create rule "_INSvista" as on insert to vista
> > do instead
> > insert into emp select new.empno, new.ename, new.job
> > where new.job='SALESMAN';
> > ERROR: Type of empno does not match target column ename
> >
> > -Jose'-
>
> I had no reply to this message probably because a problem with my mail.
Sorry,
I just wrote it down quickly. Of cause the insert action
must be a valid statement. In the above case I assume, the
table 'emp' didn't had the empno first. When looking at the
schema of emp you gave below, the rule should read:
create rule "_INSvista" as on insert to vista
do instead
insert into emp (empno, ename, job)
values (new.empno, new.ename, new.job)
where new.job = 'SALESMAN';
>
> I'm trying to make a view updatable using triggers, INSERT works fine
> but UPDATE/DELETE
> doesn't because the WHERE condition is evaluated FALSE all the time.
> Here an example:
A trigger could never work for UPDATE or DELETE. A trigger is
only fired when there is actually a row in a table to get
updated or deleted. Views don't (or at least shouldn't)
contain any data, so there is never a row to fire them.
>
> create table emp (
> empno int,
> ename char(10),
> job char(12),
> hiredate date,
> sal money,
> comm int,
> deptno int,
> level int,
> mgr int
> );
> CREATE
>
> [...]
>
> -- The where condition is all the time evaluated as FALSE...
>
> delete from vista where ename='MANUEL'; --why this condition isn't true
> ?
> DELETE 0
The rule system redirected the scan for the DELETE from
'vista' to a scan from 'emp' because vista is a view on emp.
The resulting query is a scan from emp who's result tupels
should be deleted from vista - a whole lot of nonsens and
thus absolutely nothing happens.
>
> delete from vista ;
> NOTICE: trigger fired: BEFORE on DELETE
> DELETE 0
>
>
> --Is there a way to make views updatable ?
Read section 8 of the programmers manual to understand all
the details of the rewrite rule system. Then do it with
rules.
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) #