From: | Wiebe Cazemier <halfgaar(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Delete rule chain stops unexpectedly |
Date: | 2005-10-21 14:13:47 |
Message-ID: | 4358F79B.1000409@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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_id INTEGER REFERENCES _rating_params,
seq_num INTEGER NOT NULL DEFAULT 1,
name_id INTEGER NOT NULL REFERENCES messages_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.
From | Date | Subject | |
---|---|---|---|
Next Message | Alex Turner | 2005-10-21 14:16:08 | Re: [pgsql-advocacy] Oracle buys Innobase |
Previous Message | Thomas Pundt | 2005-10-21 12:52:59 | Re: Postgres 7.4.9 slow! |