Re: BUG #5203: Rule affecting more than one row is only fired once, when there is no reference to the row.

On Fri, Nov 20, 2009 at 10:11 AM, Marcel Wieland
<marcel(dot)wieland(at)fondsnet(dot)de> wrote:
>
> The following bug has been logged online:
>
> Bug reference:      5203
> Logged by:          Marcel Wieland
> Email address:      marcel(dot)wieland(at)fondsnet(dot)de
> PostgreSQL version: 8.2
> Operating system:   Linux
> Description:        Rule affecting more than one row is only fired once,
> when there is no reference to the row.
> Details:
>
> BEGIN;
>
> -- Create testing Tables
> CREATE TABLE footable (
>    name char
> );
> CREATE TABLE bartable (
>    foo char
> );
>
> -- Insert testing Values
> INSERT INTO footable (name) VALUES('a'), ('b');
>
> -- RULE without row-reference
> CREATE OR REPLACE RULE foorule AS ON UPDATE TO footable DO
>    INSERT INTO bartable (foo) SELECT * FROM (SELECT 'a' UNION SELECT 'b')
> AS x;
>
> -- Query fires Rule
> UPDATE footable SET name = name;
> -- Result
> SELECT * FROM bartable;
>
> -- Reset
> DELETE FROM bartable;
>
> -- RULE with row-reference
> CREATE OR REPLACE RULE foorule AS ON UPDATE TO footable DO
>    INSERT INTO bartable (foo) SELECT * FROM (SELECT 'a' UNION SELECT 'b')
> AS x WHERE old.name = old.name;
>
> -- Query fires Rule
> UPDATE footable SET name = name;
> -- Result
> SELECT * FROM bartable;
>
> -- Cleanup
> DROP TABLE footable;
> DROP TABLE bartable;
>
> ROLLBACK;

You haven't really said what the actual behavior is and what behavior
you expected, so it's a bit difficult to judge whether the behavior is
wrong, or whether your expectations are wrong. However, based on the
subject line, I'm guessing that you may be misunderstanding how rules
work. Rules operate on a query level, not on a tuple level. If you
add a DO ALSO rule, you're just constructing a new query which gets
executed in addition to the original query. If you want something
that gets fired for every tuple, you probably want a trigger.

In my experience, triggers are always better than rules for solving
all real-world problems.

...Robert