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
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
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
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
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
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 arule 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?
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Import Notes
Reply to msg id not found: 6667 | Resolved by subject fallback
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 arule 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?__________________________________________________
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 arule 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?
__________________________________
Do you Yahoo!?
Yahoo! Small Business - Try our new resources site!
http://smallbusiness.yahoo.com/resources/
Import Notes
Reply to msg id not found: 6667 | Resolved by subject fallback
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
Import Notes
Resolved by subject fallback