Thread: BUG #5202: Rule affecting more than one row is only fired once with LIMIT 1

The following bug has been logged online:

Bug reference:      5202
Logged by:          Marcel Wieland
Email address:      marcel.wieland@fondsnet.de
PostgreSQL version: 8.2
Operating system:   Linux
Description:        Rule affecting more than one row is only fired once with
LIMIT 1
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 with LIMIT 1
CREATE OR REPLACE RULE foorule AS ON UPDATE TO footable DO
    INSERT INTO bartable (foo) SELECT name FROM footable WHERE name =
old.name LIMIT 1;

-- Query fires Rule
UPDATE footable SET name = name;
-- Result
SELECT * FROM bartable;

-- Reset
DELETE FROM bartable;

-- RULE without LIMIT 1
CREATE OR REPLACE RULE foorule AS ON UPDATE TO footable DO
    INSERT INTO bartable (foo) SELECT name FROM footable WHERE name =
old.name;

-- Query fires Rule
UPDATE footable SET name = name;
-- Result
SELECT * FROM bartable;

-- Cleanup
DROP TABLE footable;
DROP TABLE bartable;

ROLLBACK;

On Fri, Nov 20, 2009 at 9:58 AM, Marcel Wieland
<marcel.wieland@fondsnet.de> wrote:
>
> The following bug has been logged online:
>
> Bug reference: =A0 =A0 =A05202
> Logged by: =A0 =A0 =A0 =A0 =A0Marcel Wieland
> Email address: =A0 =A0 =A0marcel.wieland@fondsnet.de
> PostgreSQL version: 8.2
> Operating system: =A0 Linux
> Description: =A0 =A0 =A0 =A0Rule affecting more than one row is only fire=
d once with
> LIMIT 1
> Details:
>
> BEGIN;
>
> -- Create testing Tables
> CREATE TABLE footable (
> =A0 =A0name char
> );
> CREATE TABLE bartable (
> =A0 =A0foo char
> );
>
> -- Insert testing Values
> INSERT INTO footable (name) VALUES('a'), ('b');
>
> -- RULE with LIMIT 1
> CREATE OR REPLACE RULE foorule AS ON UPDATE TO footable DO
> =A0 =A0INSERT INTO bartable (foo) SELECT name FROM footable WHERE name =
=3D
> old.name LIMIT 1;
>
> -- Query fires Rule
> UPDATE footable SET name =3D name;
> -- Result
> SELECT * FROM bartable;
>
> -- Reset
> DELETE FROM bartable;
>
> -- RULE without LIMIT 1
> CREATE OR REPLACE RULE foorule AS ON UPDATE TO footable DO
> =A0 =A0INSERT INTO bartable (foo) SELECT name FROM footable WHERE name =
=3D
> old.name;
>
> -- Query fires Rule
> UPDATE footable SET name =3D name;
> -- Result
> SELECT * FROM bartable;
>
> -- Cleanup
> DROP TABLE footable;
> DROP TABLE bartable;
>
> ROLLBACK;

See my response to your other bug report - same issues apply here.

...Robert