Thread: Delete rule chain stops unexpectedly
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.
Wiebe Cazemier <halfgaar@gmail.com> writes:
> which I manipulate with the view "rating_params". The delete rules on this view
> act very strangely.
The rule that actually deletes the rows from the underlying has to fire
last, since the rows are gone from the view (and hence from OLD) the
moment you delete them.
In practice, you'd be way better off using an ON DELETE trigger for
these tasks.
regards, tom lane
Tom Lane wrote:> The rule that actually deletes the rows from the underlying has to fire> last, since the rows are gone fromthe view (and hence from OLD) the> moment you delete them. A quote from the postgresql manual: "But for ON UPDATE and ON DELETE rules, the original query is done after the actions added by rules. This ensures that the actions can see the to-be-updated or to-be-deleted rows" So, the actual delete should be done after all the rules. And even if it does delete before anything else, that does not explain why "step2" is not inserted into the debuglog table. Or, that all the rules _are_ executed when I call the query with "explain analayze".
Wiebe Cazemier <halfgaar@gmail.com> writes:
> Tom Lane wrote:
>>> The rule that actually deletes the rows from the underlying has to fire
>>> last, since the rows are gone from the view (and hence from OLD) the
>>> moment you delete them.
> A quote from the postgresql manual:
> "But for ON UPDATE and ON DELETE rules, the original query is done after the
> actions added by rules. This ensures that the actions can see the to-be-updated
> or to-be-deleted rows"
Yes, I know that quote. I wrote it. It's not relevant here because the
original query (the delete against the view) is never done at all, since
you have a DO INSTEAD rule. What is relevant is the timing of the
delete issued against the underlying table, and you have that scheduled
to occur too early because the rule that does it is first in
alphabetical order.
> So, the actual delete should be done after all the rules. And even if
> it does delete before anything else, that does not explain why "step2"
> is not inserted into the debuglog table.
Because the rule converts those inserts into, effectively,
INSERT INTO debuglog SELECT ... WHERE EXISTS(some matching OLD row);
and there are no longer any matching OLD rows in the view. (If it
didn't act that way then the INSERTs would execute even for a "DELETE
WHERE false". If you find any of this surprising or not what you want,
you should probably be using triggers not rules.)
> Or, that all the rules _are_
> executed when I call the query with "explain analayze".
Hmm ... this appears to be a bug in EXPLAIN ANALYZE: it really should
bump the CommandCounter between plan trees, but fails to ...
regards, tom lane
Tom Lane wrote: >>A quote from the postgresql manual: > >>"But for ON UPDATE and ON DELETE rules, the original query is done after the >>actions added by rules. This ensures that the actions can see the to-be-updated >>or to-be-deleted rows" > > Yes, I know that quote. I wrote it. It's not relevant here because the > original query (the delete against the view) is never done at all, since > you have a DO INSTEAD rule. What is relevant is the timing of the > delete issued against the underlying table, and you have that scheduled > to occur too early because the rule that does it is first in > alphabetical order. Oh yeah, whoops, I was under a false impression of what the original query was... > Because the rule converts those inserts into, effectively, > > INSERT INTO debuglog SELECT ... WHERE EXISTS(some matching OLD row); > > and there are no longer any matching OLD rows in the view. (If it > didn't act that way then the INSERTs would execute even for a "DELETE > WHERE false". If you find any of this surprising or not what you want, > you should probably be using triggers not rules.) Ah, I see. I'll convert them to triggers, or just put the delete at the end. I could even put all this in one rule, that would be easier. > > >>Or, that all the rules _are_ >>executed when I call the query with "explain analayze". > > > Hmm ... this appears to be a bug in EXPLAIN ANALYZE: it really should > bump the CommandCounter between plan trees, but fails to ... Is this something I have to report?
Wiebe Cazemier <halfgaar@gmail.com> writes:
> Tom Lane wrote:
>> Hmm ... this appears to be a bug in EXPLAIN ANALYZE: it really should
>> bump the CommandCounter between plan trees, but fails to ...
> Is this something I have to report?
Nah, I fixed it already (only in CVS HEAD though).
regards, tom lane
Tom Lane wrote: >>So, the actual delete should be done after all the rules. And even if >>it does delete before anything else, that does not explain why "step2" >>is not inserted into the debuglog table. > > > Because the rule converts those inserts into, effectively, > > INSERT INTO debuglog SELECT ... WHERE EXISTS(some matching OLD row); > > and there are no longer any matching OLD rows in the view. (If it > didn't act that way then the INSERTs would execute even for a "DELETE > WHERE false". If you find any of this surprising or not what you want, > you should probably be using triggers not rules.) I've got a late addition question about this. Is this behaviour also present in Postgres 7? I am continuing on work that was done by someone else in version 7. He said that what he made worked fine and tested OK, but with what you told me in mind, what he made can't possibly work (and it didn't when I tested it, and I have postgres 8).
Wiebe Cazemier <halfgaar@gmail.com> writes:
> Tom Lane wrote:
>> Because the rule converts those inserts into, effectively,
>>
>> INSERT INTO debuglog SELECT ... WHERE EXISTS(some matching OLD row);
>>
>> and there are no longer any matching OLD rows in the view.
> Is this behaviour also present in Postgres 7?
It's always been like that. We've jiggered some details about the order
of rule firing, but not much else. If you want a more detailed response
you need to be more specific about what version you're comparing to and
exactly what rules you're worried about.
regards, tom lane