Thread: Problems with RULE

Problems with RULE

From
"Jens Hartwig"
Date:
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 *
FROMt_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

Re: Problems with RULE

From
dev@archonet.com
Date:
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.

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

No help I'm afraid, but I encountered something similar the other day on 
7.1b3

CREATE RULE ... AS ON UPDATE TO ... WHERE ... DO INSTEAD UPDATE ...

Didn't run, but removing the WHERE did. I had thought someone had raised 
this recently, but looking through the list I can't see it now, so maybe 
it's a real bug. Is there any mention of this is the CHANGES file in 
beta4?

I'll try and check this end whether it applies to all rule-types if you 
add a WHERE.

- Richard Huxton


Re: Problems with RULE

From
dev@archonet.com
Date:
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 | bbb4 | 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 | aaa2 | bbb3 | ccc4 | ddd
(4 rows)

richardh=> select * from voo;a |  b
---+-----2 | bbb4 | 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 | ddd99 | zzz98 | yyy98 | yyy
(7 rows)

richardh=> select * from voo;a  |  b
----+----- 2 | bbb 4 | ddd98 | yyy98 | 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 | ddd99 | zzz98 | yyy98 | yyy
(7 rows)

richardh=> select * from voo;a  |  b
----+----- 2 | bbb 4 | ddd98 | yyy98 | 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 | ddd99 | zzz98 | yyy98 | yyy99 | zzz99 | zzz98 | yyy98 | yyy
(11 rows)

richardh=> select * from voo;a  |  b
----+----- 2 | bbb 4 | ddd98 | yyy98 | yyy98 | yyy98 | 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.



Re: Problems with RULE

From
Tom Lane
Date:
"Jens Hartwig" <jens.hartwig@t-systems.de> writes:
> 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!

You'd probably have better luck doing this with a trigger.  With this
rule, the DELETE query expands into two operations, which can be written
as:

UPDATE t_xyz SET deleted = true
WHERE id IN (SELECT old.id FROM t_xyz old WHERE old.id = 1 AND old.deleted = false);

DELETE FROM t_xyz WHERE id = 1 AND NOT (deleted = false);

The problem is that the second query can see the results of the first.
Unfortunately, while that's bad for this example, it's necessary for
other more-useful examples.  So I do not think this is a bug.

In my experience, anything you want to do that can be expressed as
an operation or condition on an individual target tuple of an
INSERT/UPDATE/DELETE is best done in a trigger, for reasons of both
performance and understandability.  Rules are good for things that
involve conditions on multiple tuples.
        regards, tom lane


AW: Problems with RULE

From
"Jens Hartwig"
Date:
JH:
> > [...]
> > 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.
> > [...]
TL:
> In my experience, anything you want to do that can be expressed as
> an operation or condition on an individual target tuple of an
> INSERT/UPDATE/DELETE is best done in a trigger, for reasons of both
> performance and understandability.  Rules are good for things that
> involve conditions on multiple tuples.
> [...]

I am afraid, that I do not really understand this: if I insert one record in
a view there also is only ONE tuple involved, isn't it? By the way, I admit
that my example is not really useful :-) It should only demonstrate the use
of rules for the book I am writing on.

Further I do not understand the following:

> You'd probably have better luck doing this with a trigger.  With this
> rule, the DELETE query expands into two operations, which can
> be written
> as:
>
> UPDATE t_xyz SET deleted = true
> WHERE id IN
>   (SELECT old.id FROM t_xyz old WHERE old.id = 1 AND
> old.deleted = false);
>
> DELETE FROM t_xyz WHERE id = 1 AND NOT (deleted = false);

What would have happened, if I executed an unconditional DELETE?
 => DELETE FROM t_xyz;

Which statement would have been generated by PostgreSQL in this case?

Best Regards, Jens

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

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



AW: Problems with RULE

From
"Jens Hartwig"
Date:
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.



Re: Problems with RULE

From
"Richard Huxton"
Date:
From: "Jens Hartwig" <jens.hartwig@t-systems.de>

> Hello Richard,
>
> this was a very precise analysis - thanks for the effort you made!

Precisely wrong in this case. My mistakes have some of the finest tolerances
in the world.  8-)

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

Yep - thanks Tom (another item for my notebook). I _think_ I understand
Tom's explanation of your case - does this make sense?

You have CREATE RULE r1 ON t_xyz WHERE old.deleted=false ...

So - if you issue "DELETE FROM t_xyz" you'd want two things to happen:

1. where deleted is false set it to true
2. where deleted was true delete the record

So - PG rewrites the query into two parts:

DELETE FROM t_xyz WHERE old.deleted=false
DELETE FROM t_xyz WHERE NOT(old.deleted=false)

Unfortunately, the changes from the first part are visible to the second
part so you end up marking everything for deletion then deleting it.

Of course in your case you were selecting id=1 so it wasn't so obvious.

I think that's what's happening here. Unfortunately, setting DEBUG_PRINT_xxx
doesn't seem to show any detail, do I can't show a trace.

Of course, with a trigger you can have an IF..THEN..ELSE to make sure you
control the order of execution.

- Richard Huxton



Re: AW: Problems with RULE

From
Tom Lane
Date:
"Jens Hartwig" <jens.hartwig@t-systems.de> writes:
> What would have happened, if I executed an unconditional DELETE?
>   => DELETE FROM t_xyz;
> Which statement would have been generated by PostgreSQL in this case?

Unfortunately, I didn't keep the prior discussion, so I don't remember
exactly what the rule was.  But the general idea for conditional rules
is that we generate

rule-action
WHERE rule-action's-own-conditions     AND rule-condition     AND conditions-from-original-query

(repeat for each action of each relevant rule) and then if we didn't
find any relevant unconditional INSTEAD rules, we generate

original-query-action
WHERE conditions-from-original-query     AND NOT (conditions-of-conditional-INSTEAD-rules)

There's a more extensive discussion in the Programmer's Guide,
http://www.postgresql.org/devel-corner/docs/postgres/rules.html
        regards, tom lane


AW: Problems with RULE

From
"Jens Hartwig"
Date:
> [...]
> So - if you issue "DELETE FROM t_xyz" you'd want two things to happen:
>
> 1. where deleted is false set it to true
> 2. where deleted was true delete the record
>
> So - PG rewrites the query into two parts:
>
> DELETE FROM t_xyz WHERE old.deleted=false
> DELETE FROM t_xyz WHERE NOT(old.deleted=false)
> [...]

Oooooh ... that's it! I hit my head against the wall :-)

Thank you very much!

Best regards, Jens Hartwig

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

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