CREATE RULE on VIEW with INSERT after UPDATE does not work - Mailing list pgsql-general

Hi all,

I have a table "mytable" to log the validity of
data records with start and stop time.
To see, which records are still valid,
I created a view on all entries with stop=null.

The DB-Interaction should be done over the view,
so I added rules for INSERT, UPDATE an DELETE.

Insert and Update work fine, but the DELETE_RULE
stopps after the first UPDATE statement in the Rule-Body,
any further statements are ignored!!
Multiple Statements are not the Problem (Log=1,2),
and the first UPDATE statement works also. (Stop=now())

Is this a known Problem? Am I doing something wrong?
Is there any workaround for it?

Thanks, Peter


PS: Here is the code for testing, mylog should contain 1,2,3,4:


DROP VIEW myview;
DROP TABLE mytable;
DROP TABLE mylog;

CREATE TABLE mylog(id int);
CREATE TABLE mytable(id serial, proc text, start timestamp(4), stop timestamp(4));
CREATE VIEW myview AS SELECT id, proc, start, stop FROM mytable WHERE stop IS null;

CREATE RULE sri AS ON INSERT TO myview DO INSTEAD
  INSERT INTO mytable (proc, start, stop) VALUES (new.proc, now(), null);

CREATE RULE srd AS ON DELETE TO myview DO INSTEAD
  UPDATE mytable SET stop = now() WHERE id = old.id AND stop IS null;

CREATE RULE sru AS ON UPDATE TO myview DO INSTEAD (
  INSERT INTO mylog (id) VALUES (1);
  INSERT INTO mylog (id) VALUES (2);
  UPDATE mytable SET stop = now() WHERE id = old.id AND stop IS null;
  INSERT INTO mylog (id) VALUES (3);
  UPDATE mytable SET stop = now() WHERE id = old.id+1 AND stop IS null;
  INSERT INTO mylog (id) VALUES (4);
);

-- Insert some values works fine
INSERT INTO myview (proc) VALUES ('alpha');
INSERT INTO myview (proc) VALUES ('omega');
INSERT INTO myview (proc) VALUES ('gamma');
INSERT INTO myview (proc) VALUES ('delta');

-- Both Table and View are identical
SELECT * FROM mytable ORDER BY id;
SELECT * FROM myview ORDER BY id;
SELECT * FROM mylog ORDER BY id;

-- Delete a row works fine, too
DELETE FROM myview WHERE id = 4;

-- Row 4 is deleted
SELECT * FROM mytable ORDER BY id;
SELECT * FROM myview ORDER BY id;
SELECT * FROM mylog ORDER BY id;


-- !! The UPDATE_RULE does not work correct !!
UPDATE myview SET proc='beta' WHERE id = 2;

-- The Process 2 is updated, but there is no entry in the log
SELECT * FROM mytable ORDER BY id;
SELECT * FROM myview ORDER BY id;
SELECT * FROM mylog ORDER BY id;



--
Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen!
Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer