Hi,
I've got the following table (contents not really relevant):
CREATE TABLE _rating_params ( id SERIAL PRIMARY KEY, letter CHAR(1) NOT NULL CHECK (letter = 'E' OR letter = 'F'),
superparam_idINTEGER REFERENCES _rating_params, seq_num INTEGER NOT NULL DEFAULT 1, name_id INTEGER NOT NULL
REFERENCESmessages_eng, max_score NUMERIC(4)
);
which I manipulate with the view "rating_params". The delete rules on this view
act very strangely. They are, with comments I'll explain:
-- Actually delete the rating param, along with all it's subparams
CREATE RULE delete1 AS ON DELETE TO rating_params DO INSTEAD ( INSERT INTO debuglog (line) VALUES('step1');
-- When I comment out this line, then the other rules _are_ executed. If I
leave it here, execution stops here, after this query. DELETE FROM _rating_params WHERE id = OLD.id OR
superparam_id=OLD.id;
INSERT INTO debuglog (line) VALUES('step2');
);
-- Renumber sequences in order not to get any gaps
CREATE RULE delete2 AS ON DELETE TO rating_params DO ALSO ( UPDATE _rating_params SET seq_num = seq_num - 1 WHERE
superparam_id= OLD.superparam_id AND seq_num > OLD.seq_num;
INSERT INTO debuglog (line) VALUES('step3');
);
-- Remove the max. score from any maximum total scores
CREATE RULE delete3 AS ON DELETE TO rating_params WHERE OLD.superparam_id IS NOT
NULL DO ALSO ( UPDATE _rating_params SET max_score = rating_param_max_score(id) WHERE id = OLD.superparam_id;
INSERT INTO debuglog (line) VALUES('step4');
);
As you can see I've put several debug messages in the rules. As it is now, only
step1 is put in the debuglog table. When I remove the query to delete from the
_rating_params table, all other rules are executed and the debug messages are
inserted.
The strangest is yet to come. Normally I would delete with "delete from
rating_params where id=5". But, when I do "explain analyze delete from
rating_params where id=5", all the rules _are_ executed properly.
I'm using postgresql 8.0.3.
Anybody got an idea?
Thanks in advance.
