AW: Problems with RULE - Mailing list pgsql-sql

Hello Richard,

this was a very precise analysis - thanks for the effort you made!
Nevertheless the Tom's explanation of the behaviour in case of views was
sufficient for me. But still I don't understand the behaviour in my case ...

Best regards, Jens

PS: I use 7.1b4

-----------------------------------------------------

T-Systems
Projektleiter
debis Systemhaus GEI GmbH
Hausanschrift: Eichhornstraße 3, 10785 Berlin
Postanschrift: 10785 Berlin
Telefon: (004930) 25 54-32 82
Telefax: (004930) 25 54-31 87
Mobiltelefon: (0170) 167 26 48
E-Mail: jens.hartwig@t-systems.de
Internet: http://www.t-systems.de


> -----Ursprüngliche Nachricht-----
> Von: dev@archonet.com [mailto:dev@archonet.com]
> Gesendet: Dienstag, 6. März 2001 19:10
> An: Jens Hartwig
> Cc: PSQL-Sql (E-Mail)
> Betreff: Re: [SQL] Problems with RULE
>
>
> On 3/6/01, 7:11:48 AM, Jens Hartwig <jens.hartwig@t-systems.de> wrote
> regarding [SQL] Problems with RULE:
>
> > Hello all,
>
> > I tried to implement the following rule: if someone wants
> to delete a
> record
> > from a table t_xyz (id integer, deleted boolean) the record
> should get a
> > delete-flag (deleted = true). When this "pre-deleted"
> record is deleted
> for
> > the next time it should be physically deleted from the database.
>
> Jens - more info
>
> Definitely a bug if my testing is correct (see below) - I'll file a
> report on it and include your example too (hope that's OK)
>
> - Richard Huxton
>
> -- OK define a table foo with data and a view voo showing
> -- even-numbered entries
> --
> richardh=> create table foo (a int, b text);
> CREATE
> richardh=> insert into foo values (1,'aaa');
> INSERT 1287580 1
> richardh=> insert into foo values (2,'bbb');
> INSERT 1287581 1
> richardh=> insert into foo values (3,'ccc');
> INSERT 1287582 1
> richardh=> insert into foo values (4,'ddd');
> INSERT 1287583 1
> richardh=> create view voo as select * from foo where (a % 2)=0;
> CREATE
> richardh=> select * from voo;
>  a |  b
> ---+-----
>  2 | bbb
>  4 | ddd
> (2 rows)
>
> -- Now define an insert rule with a where on voo
> --
> richardh=> CREATE RULE voo_ins_rule AS ON INSERT TO voo WHERE
> (NEW.a % 2)=0
> DO INSTEAD INSERT INTO foo VALUES (NEW.a, NEW.b);
> CREATE
> richardh=> insert into voo values (99,'zzz');
> ERROR:  Cannot insert into a view without an appropriate rule
> richardh=> insert into voo values (98,'yyy');
> ERROR:  Cannot insert into a view without an appropriate rule
> richardh=> select * from foo;
>  a |  b
> ---+-----
>  1 | aaa
>  2 | bbb
>  3 | ccc
>  4 | ddd
> (4 rows)
>
> richardh=> select * from voo;
>  a |  b
> ---+-----
>  2 | bbb
>  4 | ddd
> (2 rows)
>
> -- OK: rule wasn't accepted, so lets add another rule to voo
> without a
> where
> --
> richardh=> CREATE RULE voo_ins_rule2 AS ON INSERT TO voo DO
> INSTEAD INSERT
> INTO
> foo VALUES (NEW.a, NEW.b);
> CREATE
> richardh=> insert into voo values (99,'zzz');
> INSERT 1287602 1
> richardh=> insert into voo values (98,'yyy');
> INSERT 1287604 1
> richardh=> select * from foo;
>  a  |  b
> ----+-----
>   1 | aaa
>   2 | bbb
>   3 | ccc
>   4 | ddd
>  99 | zzz
>  98 | yyy
>  98 | yyy
> (7 rows)
>
> richardh=> select * from voo;
>  a  |  b
> ----+-----
>   2 | bbb
>   4 | ddd
>  98 | yyy
>  98 | yyy
> (4 rows)
>
> -- So: looks like either rule2 executes twice or both fire.
> -- Is it because we have a second rule?
> --
> richardh=> drop rule voo_ins_rule2;
> DROP
> richardh=> CREATE RULE voo_ins_rule3 AS ON INSERT TO voo
> WHERE (NEW.a %
> 2)=1 DO
> INSTEAD INSERT INTO foo VALUES (NEW.a, NEW.b);
> CREATE
> richardh=> insert into voo values (99,'zzz');
> ERROR:  Cannot insert into a view without an appropriate rule
> richardh=> insert into voo values (98,'yyy');
> ERROR:  Cannot insert into a view without an appropriate rule
> richardh=> select * from foo;
>  a  |  b
> ----+-----
>   1 | aaa
>   2 | bbb
>   3 | ccc
>   4 | ddd
>  99 | zzz
>  98 | yyy
>  98 | yyy
> (7 rows)
>
> richardh=> select * from voo;
>  a  |  b
> ----+-----
>   2 | bbb
>   4 | ddd
>  98 | yyy
>  98 | yyy
> (4 rows)
>
> -- No: it must be the lack of where on rule2
> -- Let's put rule2 back in and see what executes now
> --
> richardh=> CREATE RULE voo_ins_rule2 AS ON INSERT TO voo DO
> INSTEAD INSERT
> INTO
> foo VALUES (NEW.a, NEW.b);
> CREATE
> richardh=> insert into voo values (99,'zzz');
> INSERT 1287608 1
> richardh=> insert into voo values (98,'yyy');
> INSERT 1287610 1
> richardh=> select * from foo;
>  a  |  b
> ----+-----
>   1 | aaa
>   2 | bbb
>   3 | ccc
>   4 | ddd
>  99 | zzz
>  98 | yyy
>  98 | yyy
>  99 | zzz
>  99 | zzz
>  98 | yyy
>  98 | yyy
> (11 rows)
>
> richardh=> select * from voo;
>  a  |  b
> ----+-----
>   2 | bbb
>   4 | ddd
>  98 | yyy
>  98 | yyy
>  98 | yyy
>  98 | yyy
> (6 rows)
>
> -- OK: so it looks like rules with "WHERE" don't execute until
> -- there is a rule that fires unconditionally, when
> -- the "WHERE" is recognised and applies accordingly.