Tom Lane wrote:
=?ISO-8859-1?Q?Sebastian_B=F6ck?= <[EMAIL PROTECTED]> writes:
Is this a known limitation with views, rules and
inherited tables i haven't heard of? Or is it a bug?
When you haven't shown us any details, it's impossible to tell.
Let's see the actual table, view, and rule definitions. (pg_dump -s
output would be good.)
Since my first attempt to send this message seems to be caught
by spamfilters, i'll repost it.
Sorry for the delay.
I investigated a little bit further and can be more precisely
about the whole thing. This (wrong) behaviour only occurs, if
the view has an order by clause.
To repoduce, simply run the attached script.
Sebastian
CREATE TABLE id (
id SERIAL PRIMARY KEY,
name TEXT
);
CREATE TABLE test_1 (
id INTEGER PRIMARY KEY,
name TEXT
) INHERITS (id);
CREATE TABLE test_2 (
id INTEGER PRIMARY KEY,
name TEXT
) INHERITS (id);
CREATE TABLE test_3 (
id INTEGER PRIMARY KEY,
name TEXT
) INHERITS (id);
CREATE VIEW working AS SELECT * FROM id;
CREATE RULE update_working AS ON UPDATE TO working
DO INSTEAD UPDATE id SET name = NEW.name WHERE id = OLD.id;
CREATE VIEW not_working AS SELECT * FROM id ORDER BY id;
CREATE RULE update_not_working AS ON UPDATE TO not_working
DO INSTEAD UPDATE id SET name = NEW.name WHERE id = OLD.id;
INSERT INTO test_1 (name) VALUES ('Test 1');
INSERT INTO test_1 (name) VALUES ('Test 2');
INSERT INTO test_2 (name) VALUES ('Test 3');
INSERT INTO test_2 (name) VALUES ('Test 4');
INSERT INTO test_3 (name) VALUES ('Test 5');
INSERT INTO test_3 (name) VALUES ('Test 6');
SELECT * FROM working;
UPDATE working SET name = 'working' WHERE id = '1';
UPDATE working SET name = 'working' WHERE id = '3';
UPDATE working SET name = 'working' WHERE id = '5';
SELECT * FROM working;
SELECT * FROM not_working;
UPDATE not_working SET name = 'should work' WHERE id = '2';
UPDATE not_working SET name = 'should work' WHERE id = '4';
UPDATE not_working SET name = 'should work' WHERE id = '5';
SELECT * FROM not_working;
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
