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 bug and 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