Re: Update on tables when the row doesn't change - Mailing list pgsql-general

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.*);