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 по дате отправления:

Предыдущее
От: Thomas Pundt
Дата:
Сообщение: Re: Postgres 7.4.9 slow!
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Postgres 7.4.9 slow!