Delete rule chain stops unexpectedly

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.

Responses

Browse pgsql-sql by date

  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!