From: Wiebe Cazemier <halfgaar@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Delete rule chain stops unexpectedly
Date: Fri, 21 Oct 2005 16:13:47 +0200
Message-ID: <4358F79B.1000409@gmail.com> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: halfgaar@gmail.com
Subject: Re: Delete rule chain stops unexpectedly
In-Reply-To: <4358F79B.1000409@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox