Mis-firing of rules with a WHERE condition - Mailing list pgsql-bugs

Richard Huxton (dev@archonet.com) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
Mis-firing of rules with a WHERE condition

Long Description
Jens Hartwig posted a question to pgsql-sql today (2001-03-06) regarding rules with where conditions. It seems to be a
bugand applies to all rule-types. 

My example and then Jens' original follow:

-- 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.


--
-- Start of Jens Hartwig's example (email given at bottom)
--
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.

I implemented the following rule:

  CREATE RULE r_del_xyz
  AS ON DELETE TO t_xyz WHERE (old.deleted = false)
  DO INSTEAD
    UPDATE t_xyz
    SET deleted = true
    WHERE id = old.id;

Now I tested the new rule:

  INSERT INTO t_xyz VALUES (1, false);
  INSERT INTO t_xyz VALUES (2, false);
  DELETE FROM t_xyz WHERE id = 1;
  SELECT * FROM t_xyz ;

   id | deleted
  ----+---------
    2 | f

What has happened? The rule seems to be ignored and the record was deleted!

I dropped the rule, deleted all records and recreated the rule without the
additional WHERE-Clause in the UPDATE-Statement:

  DROP RULE r_del_xyz;

  DELETE FROM t_xyz;

  CREATE RULE r_del_xyz
  AS ON DELETE TO t_xyz WHERE (old.deleted = false)
  DO INSTEAD
    UPDATE t_xyz
    SET deleted = true;

  INSERT INTO t_xyz VALUES (1, false);
  INSERT INTO t_xyz VALUES (2, false);

The same test again:

  DELETE FROM t_xyz WHERE id = 1;
  SELECT * FROM t_xyz ;

   id | deleted
  ----+---------
    2 | t

It seems to me that PostgreSQL executed the rule, but ignored the keyword
INSTEAD and deleted the record after having updated it?!

One last test with a slightly different rule (look at the WHERE-clause in
the "AS-ON"-clause):

  DROP RULE r_del_xyz;

  DELETE FROM t_xyz;

  CREATE RULE r_del_xyz
  AS ON DELETE TO t_xyz WHERE (1 = 1)
  DO INSTEAD
    UPDATE t_xyz
    SET deleted = true
    WHERE id = old.id;

  INSERT INTO t_xyz VALUES (1, false);
  INSERT INTO t_xyz VALUES (2, false);

  DELETE FROM t_xyz WHERE id = 1;
  SELECT * FROM t_xyz ;

   id | deleted
  ----+---------
    2 | f
    1 | t

  DELETE FROM t_xyz WHERE id = 1;
  SELECT * FROM t_xyz ;

Everything is alright now! Am I wrong? Is the WHERE-clause "WHERE
(old.deleted = false)" not correct? Any hints? Or it is really a bug?

Best regards, Jens Hartwig

PS: You will find the scripts in the attachment.

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

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

Sample Code
-- Create table and view
--
create table foo (a int, b text);
insert into foo values (1,'aaa');
insert into foo values (2,'bbb');
insert into foo values (3,'ccc');
insert into foo values (4,'ddd');
create view voo as select * from foo where (a % 2)=0;
--
-- Now define an insert rule with a where on voo
--
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);
--
-- and try inserts (second should work)
--
insert into voo values (99,'zzz');
insert into voo values (98,'yyy');


No file was uploaded with this report