Message-ID: <4358F79B.1000409@gmail.com>
Date: Fri, 21 Oct 2005 16:13:47 +0200
From: Wiebe Cazemier <halfgaar@gmail.com>
User-Agent: Mozilla Thunderbird 1.0.6 (X11/20050729)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Delete rule chain stops unexpectedly
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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.