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

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

From: "Marcel Wieland" <marcel(dot)wieland(at)fondsnet(dot)de>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #5202: Rule affecting more than one row is only fired once with LIMIT 1
Date: 2009-11-20 14:58:01
Message-ID: 200911201458.nAKEw1t2071641@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
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;

Responses

pgsql-bugs by date

Next:From: Marcel WielandDate: 2009-11-20 15:11:19
Subject: BUG #5203: Rule affecting more than one row is only fired once, when there is no reference to the row.
Previous:From: anteuszDate: 2009-11-20 09:31:02
Subject: BUG #5201: insert select gives bogus error message

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