Thread: Mis-firing of rules with a WHERE condition

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

Richard Huxton (dev@archonet.com) writes:
> Jens Hartwig posted a question to pgsql-sql today (2001-03-06)
> regarding rules with where conditions. It seems to be a bug and
> applies to all rule-types.

AFAICT this is not a bug but is operating as designed.  The message you
are getting:
> richardh=> insert into voo values (99,'zzz');
> ERROR:  Cannot insert into a view without an appropriate rule

is a runtime check that insists that the view have at least one
unconditional DO INSTEAD rule.  It's OK to have conditional rules too
(INSTEAD or not doesn't matter) --- but there must be an unconditional
one, else there is no certainty that the undefined operation of
inserting into the view won't occur.

If you want the default to be that nothing happens, fine: add
CREATE RULE voo_ins_default AS ON INSERT TO voo DO INSTEAD NOTHING

and then do the useful work in conditional rules.  But you gotta have
the unconditional rule as a backstop.

This runtime check is new in 7.1.  In 7.0, the undefined operation of
inserting into the view will actually occur if you are careless enough
to let it.  The effective result is that the inserted tuples disappear
(I'll let you consult the archives to learn where they really go);
that's mystified many people, including me when I first got burnt by it.

I haven't had time to look closely at Jens' complaint, but I suspect
that he is using 7.0 and is getting burnt by the undefined case.
        regards, tom lane