Thread: DO INSTEAD and conditional rules
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
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
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
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
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
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
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
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
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
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
Are rules even needed anymore? Can't you do this all with triggers? If you want to "DO INSTEAD" just use a row based trigger, and return null. Or is this less efficient? Later Rob --- David Wheeler <david@kineticode.com> wrote: > 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 > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
On 4/26/2005 3:01 PM, Rob Butler wrote: > Are rules even needed anymore? Can't you do this all > with triggers? If you want to "DO INSTEAD" just use a > row based trigger, and return null. Or is this less > efficient? On INSERT, yes, on UPDATE, how so? Jan > > Later > Rob > --- David Wheeler <david@kineticode.com> wrote: >> 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 >> > > __________________________________________________ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Jan Wieck <JanWieck@Yahoo.com> writes:
> On 4/26/2005 3:01 PM, Rob Butler wrote:
>> Are rules even needed anymore? Can't you do this all
>> with triggers? If you want to "DO INSTEAD" just use a
>> row based trigger, and return null. Or is this less
>> efficient?
> On INSERT, yes, on UPDATE, how so?
We don't support triggers of any kind on views.
One possibility for an alternative mechanism is to allow triggers on
views --- but I'm not sure exactly how this would work, or if it would
solve all the problems. At the very least it would answer the "data
stability" issue, since I suppose the trigger would receive precomputed
rows that wouldn't change while it executed.
regards, tom lane
On Apr 26, 2005, at 12:35 PM, Tom Lane wrote: > One possibility for an alternative mechanism is to allow triggers on > views --- but I'm not sure exactly how this would work, or if it would > solve all the problems. At the very least it would answer the "data > stability" issue, since I suppose the trigger would receive precomputed > rows that wouldn't change while it executed. FWIW, SQLite allows triggers on views. They work very much like Pg's rules (only I think that its INSTEAD OF syntax short-circuits the execution of other triggers) in that it allows you to use a series of SQL statements that will be executed--no function definition required. http://www.sqlite.org/lang_createtrigger.html Works great! Cheers, David
On Apr 26, 2005, at 11:20 AM, Tom Lane wrote: > 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. Yah. > I don't think it's fixable without a fundamental rethinking of the > feature. Well, I'm not to worried about it for my current needs, but I can sure see how it would be unexpected and really bite someone. So has anyone else done any rethinking of rules? Cheers, David
For this particular scenario, can't you just create two ON DELETE rules? The first would delete from b, the second from a. Perhaps an example with a scenario like this can be added to the doc's? So, the short answer is you can only perform one query in a rule, but you can have multiple rules defined to do what you need. Can you call a stored proc from a rule? You could pass the old.id to the stored proc and do as many queries as you like in there without worry that the old.id would go away. Just some thoughts. It does suck that old.id goes away. Any way of preventing that from happening? later Rob --- David Wheeler <david@kineticode.com> wrote: > 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 > __________________________________ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/
On Apr 26, 2005, at 2:43 PM, Rob Butler wrote: > For this particular scenario, can't you just create > two ON DELETE rules? The first would delete from b, > the second from a. Perhaps an example with a scenario > like this can be added to the doc's? No, that approach has the same problem. Once the first rule deletes a record, it's gone from the view, so the second delete wouldn't know how to do it. > So, the short answer is you can only perform one query > in a rule, but you can have multiple rules defined to > do what you need. No, you can have multiple queries--you just have to understand that those that come first might have an effect on those that come later. > Can you call a stored proc from a rule? You could > pass the old.id to the stored proc and do as many > queries as you like in there without worry that the > old.id would go away. Yes, that would be one solution. Another would be to have an ON DELETE CASCADE on the foreign key constraint. Then, to delete the record from both tables, you just delete it from the primary key table. > Just some thoughts. It does suck that old.id goes > away. Any way of preventing that from happening? Doesn't sound like it. But your suggestion to use a function is a good one. (Although Tom did say something about volatile functions...). Regards, David
Rob Butler <crodster2k@yahoo.com> writes:
> For this particular scenario, can't you just create
> two ON DELETE rules? The first would delete from b,
> the second from a. Perhaps an example with a scenario
> like this can be added to the doc's?
No, that doesn't work any more than the other way.
regards, tom lane
David Wheeler <david@kineticode.com> writes:
> No, you can have multiple queries--you just have to understand that
> those that come first might have an effect on those that come later.
... which indeed can be a feature, not a bug, depending on what you're
doing ...
regards, tom lane
On Apr 26, 2005, at 2:58 PM, Tom Lane wrote:
> ... which indeed can be a feature, not a bug, depending on what you're
> doing ...
Absolutely. An INSERT rule I have looks like this:
CREATE RULE insert_one AS
ON INSERT TO one WHERE NEW.id IS NULL
DO INSTEAD ( INSERT INTO _simple (id, guid, state, name, description) VALUES (NEXTVAL('seq_kinetic'), NEW.guid,
NEW.state,NEW.name,
NEW.description);
INSERT INTO simple_one (id, bool) VALUES (CURRVAL('seq_kinetic'), NEW.bool);
);
The call to NEXTVAL() in the first statement sets up a value I use in
the second via CURRLVA().
Cheers,
David
On 4/26/2005 5:58 PM, Tom Lane wrote: > David Wheeler <david@kineticode.com> writes: >> No, you can have multiple queries--you just have to understand that >> those that come first might have an effect on those that come later. > > ... which indeed can be a feature, not a bug, depending on what you're > doing ... > > regards, tom lane There is no such thing as a free lunch here. If one wants a row inserted by one action being visible by a subsequent one, then a delete done in another action must (by default) be visible to subsequent actions as well. I don't think that fiddling with scan-command-ID's on top of the rule definitions will make the average user understand them easier. The multi-action rules usually come into play when someone attempts to make join-views updatable. Not an easy problem, granted, but most of the time I have found a combination of rules together with ON UPDATE/DELETE CASCADE constraints or even user defined triggers absolutely sufficient. The INSERT and UPDATE case is handled by rules as usual. And in the DELETE case the rule just deletes the critical rows and the ON DELETE CASCADE constraints do the rest. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
On Apr 26, 2005, at 5:02 PM, Jan Wieck wrote: > The multi-action rules usually come into play when someone attempts to > make join-views updatable. Not an easy problem, granted, but most of > the time I have found a combination of rules together with ON > UPDATE/DELETE CASCADE constraints or even user defined triggers > absolutely sufficient. The INSERT and UPDATE case is handled by rules > as usual. And in the DELETE case the rule just deletes the critical > rows and the ON DELETE CASCADE constraints do the rest. Yes, this is what I'm finding, too. But it would be good if the documentation better reflected that this is how it works. Regards, David
> > 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. Why not simply write non overlapping WHERE statements ? Imho not doing them all would be very counter intuitive. Andreas