Sorry, missed the SQL to test.
Sebastian
/* tables */
CREATE TABLE test (
id INTEGER PRIMARY KEY,
test TEXT NOT NULL
);
CREATE TABLE join1 (
id INTEGER PRIMARY KEY,
text1 TEXT NOT NULL
);
CREATE TABLE join2 (
id INTEGER PRIMARY KEY,
text2 TEXT NOT NULL
);
CREATE TABLE join3 (
id INTEGER PRIMARY KEY,
text3 TEXT NOT NULL
);
/* view */
CREATE OR REPLACE VIEW view_test AS
SELECT
id,
test,
text1,
text2,
text3
FROM test
LEFT JOIN join1 USING (id)
LEFT JOIN join2 USING (id)
LEFT JOIN join3 USING (id);
/* data */
INSERT INTO test (id) VALUES ('1','Test 1');
INSERT INTO test (id) VALUES ('2','Test 2');
INSERT INTO test (id) VALUES ('3','Test 3');
INSERT INTO join1 (id,text1) VALUES ('1','Test 1 1');
INSERT INTO join1 (id,text1) VALUES ('2','Test 1 2');
INSERT INTO join1 (id,text1) VALUES ('3','Test 1 3');
INSERT INTO join2 (id,text2) VALUES ('1','Test 2 1');
INSERT INTO join2 (id,text2) VALUES ('2','Test 2 2');
INSERT INTO join2 (id,text2) VALUES ('3','Test 2 3');
INSERT INTO join3 (id,text3) VALUES ('1','Test 3 1');
INSERT INTO join3 (id,text3) VALUES ('2','Test 3 2');
INSERT INTO join3 (id,text3) VALUES ('3','Test 3 3');
/* 1st way of separating updates
pro: no unnecessary updates on tables
con: the view gets evaluated 4 times
This was the whole thing being before change.
This can get *really* slow, if the view itself is not the fastest.
*/
CREATE OR REPLACE RULE upd AS ON UPDATE TO view_test
DO INSTEAD NOTHING;
CREATE OR REPLACE RULE upd_ AS ON UPDATE TO view_test
WHERE NEW.test <> OLD.test
DO UPDATE test
SET test = NEW.test
WHERE id = OLD.id;
CREATE OR REPLACE RULE upd_1 AS ON UPDATE TO view_test
WHERE NEW.text1 <> OLD.text1
DO UPDATE join1
SET text1 = NEW.text1
WHERE id = OLD.id;
CREATE OR REPLACE RULE upd_2 AS ON UPDATE TO view_test
WHERE NEW.text2 <> OLD.text2
DO UPDATE join2
SET text2 = NEW.text2
WHERE id = OLD.id;
CREATE OR REPLACE RULE upd_3 AS ON UPDATE TO view_test
WHERE NEW.text3 <> OLD.text3
DO UPDATE join3
SET text3 = NEW.text3
WHERE id = OLD.id;
/* 2nd way of separating updates
pro: ?
con: the view gets evaluated 4 times, why?
unnecessary updates on tables
First approach to reduce execution time of update, but view gets
also evaluated 4 times (no performance boost).
Here I discovered the problem that all underlying tables are getting
the updates, even if the data in that table doesn't change.
This can hurt you as well, if you log all updates.
*/
DROP RULE upd_ ON view_test;
DROP RULE upd_1 ON view_test;
DROP RULE upd_2 ON view_test;
DROP RULE upd_3 ON view_test;
CREATE OR REPLACE RULE upd AS ON UPDATE TO view_test
DO INSTEAD (
UPDATE test SET test = NEW.test WHERE id = OLD.id;
UPDATE join1 SET text1 = NEW.text1 WHERE id = OLD.id;
UPDATE join2 SET text2 = NEW.text2 WHERE id = OLD.id;
UPDATE join3 SET text3 = NEW.text3 WHERE id = OLD.id;
);
/* 3rd way of separating updates
con: unnecessary updates on tables
pro: view gets evaluated only 1 time
Not adressing the problem of unnecessary updates, but the view
gets only evaluated one time.
*/
CREATE OR REPLACE FUNCTION upd (view_test) RETURNS VOID AS $$
DECLARE
NEW ALIAS FOR $1;
BEGIN
RAISE NOTICE 'UPDATE';
UPDATE test SET test = NEW.test WHERE id = OLD.id;
UPDATE join1 SET text1 = NEW.text1 WHERE id = OLD.id;
UPDATE join2 SET text2 = NEW.text2 WHERE id = OLD.id;
UPDATE join3 SET text3 = NEW.text3 WHERE id = OLD.id;
RETURN;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE RULE upd AS ON UPDATE TO view_test
DO INSTEAD SELECT upd (NEW.*);
/* 4th way of doing it
pro: view gets evaluated only 1 time
no unnecessary updates on tables
con: ??
Here is the way I solved all my performance problems.
Only remainig issue: How can I eliminate the response of the select?
*/
CREATE OR REPLACE FUNCTION upd (view_test, view_test) RETURNS VOID AS $$
DECLARE
NEW ALIAS FOR $1;
OLD ALIAS FOR $2;
BEGIN
IF (NEW.test <> OLD.test) THEN
RAISE NOTICE 'UPDATE test';
UPDATE test SET test = NEW.test WHERE id = OLD.id;
END IF;
IF (NEW.text1 <> OLD.text1) THEN
RAISE NOTICE 'UPDATE join1';
UPDATE join1 SET text1 = NEW.text1 WHERE id = OLD.id;
END IF;
IF (NEW.text2 <> OLD.text2) THEN
RAISE NOTICE 'UPDATE join2';
UPDATE join2 SET text2 = NEW.text2 WHERE id = OLD.id;
END IF;
IF (NEW.text3 <> OLD.text3) THEN
RAISE NOTICE 'UPDATE join3';
UPDATE join3 SET text3 = NEW.text3 WHERE id = OLD.id;
END IF;
RETURN;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE RULE upd AS ON UPDATE TO view_test
DO INSTEAD SELECT upd (NEW.*,OLD.*);
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx)
