I find the following behavior confusing:

neilc=# create table t1 (a int, b int);
CREATE TABLE
neilc=# create table t2 (a int, b int);
CREATE TABLE
neilc=# create table t3 (a int, b int);
CREATE TABLE
neilc=# create rule t1_rule1 as on insert to t1 where NEW.a > 100 do
instead insert into t2 values (NEW.a, NEW.b);
CREATE RULE
neilc=# create rule t1_rule2 as on insert to t1 do instead insert into
t3 values (NEW.a, NEW.b);
CREATE RULE
neilc=# insert into t1 values (200, 400);
INSERT 0 1
neilc=# select * from t2;
a | b
-----+-----
200 | 400
(1 row)

neilc=# select * from t3;
a | b
-----+-----
200 | 400

Note that although both rules are DO INSTEAD, they both get fired for
the insertion. I would have expected that we would iterate through the
rules in alphabetical order, firing rules whose conditionals match, and
stopping when we run out of parsetrees (e.g. after applying a DO INSTEAD
rule). In this case, that would mean only inserting into t2. (The above
example behaves the same if we substitute "DO ALSO" for "DO INSTEAD" in
the definition of t1_rule1.)

The rule documentation does suggest this, albeit in a very confusing manner:

****
So we have four cases that produce the following query trees for a
one-action rule.
[...]
Qualification given and INSTEAD

the query tree from the rule action with the rule qualification and
the original query tree's qualification; and the original query tree
with the negated rule qualification added
****

Can anyone explain why the system behaves like this?

-Neil

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Neil Conway <neilc@samurai.com> writes:
> Note that although both rules are DO INSTEAD, they both get fired for
> the insertion.

DO INSTEAD means that the *original* query will not execute; it does
not suppress actions produced by other rules. If we did not define
it that way, I think your example would have to error out --- how
would you choose which INSTEAD rule wins?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Tom Lane wrote:
> DO INSTEAD means that the *original* query will not execute; it does
> not suppress actions produced by other rules. If we did not define
> it that way, I think your example would have to error out --- how
> would you choose which INSTEAD rule wins?

I think DO INSTEAD should control whether the original query is added to
the set of query trees produced by the rule. Since we apply rules in
alphabetical order, I would expect the first rule's conditional to be
checked; when it matches, we would apply the rule and replace the
original query with the rule's action (since it is DO INSTEAD). We would
then apply rules to the result of the first rule; since no more rules
can be applied, the resulting query would be the result of the first rule.

-Neil

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Neil Conway <neilc@samurai.com> writes:
> Tom Lane wrote:
>> DO INSTEAD means that the *original* query will not execute; it does
>> not suppress actions produced by other rules.

> I think DO INSTEAD should control whether the original query is added to
> the set of query trees produced by the rule.

.... which is what it does ...

> Since we apply rules in
> alphabetical order, I would expect the first rule's conditional to be
> checked; when it matches, we would apply the rule and replace the
> original query with the rule's action (since it is DO INSTEAD). We would
> then apply rules to the result of the first rule; since no more rules
> can be applied, the resulting query would be the result of the first rule.

Here I've got to differ. The alphabetical-order rule was introduced to
nail down the order of execution of operations that were going to happen
in any case, but would otherwise have happened in an unspecified order.
You are proposing to let it define what gets executed and what does not.
I don't think that's a great idea --- for one thing, it raises the ante
quite a bit as to whose idea of alphabetical order is definitive. But
more importantly, such a change will certainly break existing
applications, and you haven't offered a sufficiently compelling reason
why we should do that.

(I have been thinking more and more that we should consider a wholesale
redesign of the rule mechanism, because it sure seems not to answer the
needs/expectations of a lot of people out there. But I am not talking
about marginal questions like what INSTEAD means --- it seems to me that
there's some very fundamental disconnect between what the rewriter does
and what people want. I don't have any specific substitute proposal,
which is why I've not brought it up...)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Tom Lane wrote:
> Here I've got to differ. The alphabetical-order rule was introduced to
> nail down the order of execution of operations that were going to happen
> in any case, but would otherwise have happened in an unspecified order.
> You are proposing to let it define what gets executed and what does not.
> I don't think that's a great idea --- for one thing, it raises the ante
> quite a bit as to whose idea of alphabetical order is definitive. But
> more importantly, such a change will certainly break existing
> applications, and you haven't offered a sufficiently compelling reason
> why we should do that.

I do think the behavior I outlined an improvement over how the system
behaves at present, but I agree it is probably not worth breaking
backward compatibility for.

-Neil

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

David Wheeler <david@kineticode.com> writes:
> On Apr 25, 2005, at 11:37 PM, Tom Lane wrote:
>> (I have been thinking more and more that we should consider a wholesale
>> redesign of the rule mechanism, because it sure seems not to answer the
>> needs/expectations of a lot of people out there.

> I think that people are likely to confuse rules and triggers. The other
> issue is that they are not documented in such a way as to make them
> simple to understand. But beyond that, although I like Neil's
> suggestion better, rules work pretty well for what I need them for--the
> ability to INSERT, UPDATE, or DELETE on a view.

Well, they handle simple situations OK, but we keep seeing people get
burnt as soon as they venture into interesting territory. For instance,
if the view is a join, you can't easily make a rule that turns a delete
into deletions of both joined rows. And you'll get burnt if you try to
insert any volatile functions, because of the multiple-evaluation issue.
Etc.

Like I said, I don't have a better idea. Just a vague feeling of
dissatisfaction.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

On Apr 26, 2005, at 8:55 AM, Tom Lane wrote:

> Well, they handle simple situations OK, but we keep seeing people get
> burnt as soon as they venture into interesting territory. For
> instance,
> if the view is a join, you can't easily make a rule that turns a delete
> into deletions of both joined rows. And you'll get burnt if you try to
> insert any volatile functions, because of the multiple-evaluation
> issue.
> Etc.

sharky=# CREATE TABLE a (
sharky(# id int,
sharky(# name text
sharky(# );
CREATE TABLE
sharky=# CREATE TABLE b (
sharky(# a_id int,
sharky(# rank text
sharky(# );
CREATE TABLE
sharky=#
sharky=# CREATE VIEW ab AS
sharky-# SELECT id, name, rank
sharky-# FROM a, b
sharky-# WHERE a.id = b.a_id
sharky-# ;
CREATE VIEW
sharky=# CREATE RULE delete_ab AS
sharky-# ON DELETE TO ab DO INSTEAD (
sharky(# DELETE FROM b
sharky(# WHERE a_id = OLD.id;
sharky(#
sharky(# DELETE FROM a
sharky(# WHERE id = OLD.id;
sharky(# );
CREATE RULE
sharky=#
sharky=#
sharky=# insert into a values (1, 'test');
INSERT 597795 1
sharky=# insert into b values (1, 'sergeant');
INSERT 597796 1
sharky=# select * from ab;
id | name | rank
----+------+----------
1 | test | sergeant
(1 row)

sharky=# delete from ab;
DELETE 0
sharky=# select * from ab;
id | name | rank
----+------+------
(0 rows)

sharky=# select * from a;
id | name
----+------
1 | test
(1 row)

sharky=# select * from b;
a_id | rank
------+------
(0 rows)

Ah, yes, you're right, that is...unexpected. Perhaps OLD can contain
its values for the duration of the RULE's statements? I'm assuming that
what's happening is that OLD.id is NULL after the first of the two
DELETE statements...

> Like I said, I don't have a better idea. Just a vague feeling of
> dissatisfaction.

I'd call it a bug. ;-)

Regards,

David


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

On Apr 25, 2005, at 11:37 PM, Tom Lane wrote:

> (I have been thinking more and more that we should consider a wholesale
> redesign of the rule mechanism, because it sure seems not to answer the
> needs/expectations of a lot of people out there. But I am not talking
> about marginal questions like what INSTEAD means --- it seems to me
> that
> there's some very fundamental disconnect between what the rewriter does
> and what people want. I don't have any specific substitute proposal,
> which is why I've not brought it up...)

I think that people are likely to confuse rules and triggers. The other
issue is that they are not documented in such a way as to make them
simple to understand. But beyond that, although I like Neil's
suggestion better, rules work pretty well for what I need them for--the
ability to INSERT, UPDATE, or DELETE on a view.

Come to my presentation at OSCON this summer to see what I'm doing with
them. :-)

Cheers,

David


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

On Apr 25, 2005, at 11:00 PM, Tom Lane wrote:

> DO INSTEAD means that the *original* query will not execute; it does
> not suppress actions produced by other rules.

Ah!

> If we did not define
> it that way, I think your example would have to error out --- how
> would you choose which INSTEAD rule wins?

The documentation says that they evaluate in alphabetical order by
name. So I would expect that the first one to have its WHERE statement
evaluate to true would short-circuit the execution of the remaining
rules.

Regards,

David


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

David Wheeler <david@kineticode.com> writes:
> On Apr 26, 2005, at 8:55 AM, Tom Lane wrote:
>> Well, they handle simple situations OK, but we keep seeing people get
>> burnt as soon as they venture into interesting territory.

> [ snip ]

> Ah, yes, you're right, that is...unexpected. Perhaps OLD can contain
> its values for the duration of the RULE's statements? I'm assuming that
> what's happening is that OLD.id is NULL after the first of the two
> DELETE statements...

The problem is that OLD is effectively a macro for the view, and once
you've deleted one of the rows, that ID is no longer present anywhere in
the view. Sometimes you can work around this by making the join an
outer join, but that's certainly a kluge.

>> Like I said, I don't have a better idea. Just a vague feeling of
>> dissatisfaction.

> I'd call it a bug. ;-)

I don't think it's fixable without a fundamental rethinking of the
feature.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster