Skip site navigation (1) Skip section navigation (2)

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

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Marcel Wieland <marcel(dot)wieland(at)fondsnet(dot)de>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #5202: Rule affecting more than one row is only fired once with LIMIT 1
Date: 2009-11-23 04:35:12
Message-ID: 603c8f070911222035l60754072lf311a19b816c208c@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
On Fri, Nov 20, 2009 at 9:58 AM, Marcel Wieland
<marcel(dot)wieland(at)fondsnet(dot)de> wrote:
>
> The following bug has been logged online:
>
> Bug reference:      5202
> 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 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;

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

...Robert

In response to

pgsql-bugs by date

Next:From: Robert HaasDate: 2009-11-23 04:37:09
Subject: Re: BUG #5205: Cannot ADD CONSTRAINT ... FOREIGN KEY...
Previous:From: Robert HaasDate: 2009-11-23 04:34:23
Subject: Re: BUG #5203: Rule affecting more than one row is only fired once, when there is no reference to the row.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group