Delete rule chain stops unexpectedly
От | Wiebe Cazemier |
---|---|
Тема | Delete rule chain stops unexpectedly |
Дата | |
Msg-id | 4358F79B.1000409@gmail.com обсуждение исходный текст |
Ответы |
Re: Delete rule chain stops unexpectedly
|
Список | pgsql-sql |
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.
В списке pgsql-sql по дате отправления: