Thread: Rule trouble (looks to me exactly like the example)

This is my table:

CREATE TABLE tree (
id                      serial PRIMARY KEY,
parentid            int4 NOT NULL REFERENCES tree ( id ),
ordering        int4 NOT NULL,
leveling        int4 NOT NULL,
status            int4 NOT NULL,
title            text NOT NULL
);

And here's the rule (it doesn't do anything real or interesting, I just
simplified it to the point where it looked like the example):

CREATE RULE insert_level AS
ON INSERT TO tree
WHERE new.id > 0 DO
UPDATE tree SET leveling = 1
WHERE tree.oid = new.oid;

which looks to me like one of the examples in Bruce's book (page 299 in
my pdf version):

CREATE RULE example_5 AS
ON INERT TO emp
WHERE new.salary > 5000 DO
UPDATE emp SET salary = 5000
WHERE emp.oid = new.oid;

If I do an insert like

INSERT INTO tree (parentid,ordering,leveling,status,title)
VALUES (0,0,0,1,'foo');

nothing happens to the leveling column though:

archiweb=# select * from tree ;id | parentid | ordering | leveling | status |   title
----+----------+----------+----------+--------+----------- 0 |        0 |        0 |        0 |      0 | root node11 |
     0 |        0 |        0 |      1 | foo12 |        0 |        0 |        0 |      1 | foo
 
(3 rows)

Although the rule definitely is there:

archiweb=# select tablename,rulename from pg_rules;tablename |   rulename
-----------+--------------tree      | insert_level
(1 row)

(the definition column looks ok too, I didn't include it because it'd
look all messed up in the email here because it's in a single line.)

I am using Postgres version 7.2.1 on Linux.

Can anyone explain that?

Regards, Frank

Frank Joerdens <frank@joerdens.de> writes:
> And here's the rule (it doesn't do anything real or interesting, I just
> simplified it to the point where it looked like the example):

> CREATE RULE insert_level AS
> ON INSERT TO tree
> WHERE new.id > 0 DO
> UPDATE tree SET leveling = 1
> WHERE tree.oid = new.oid;

What's happening is that "new.id" is effectively still NULL at the point
where the rule is processed, so the rule WHERE condition fails.  I'm not
sure why you're bothering with that WHERE condition anyway ... but this
seems to be another argument for moving INSERT's default-value-insertion
step to before the rule rewriter.  (There was some discussion of this
point just a few days ago, IIRC.)
        regards, tom lane

I wrote:
> What's happening is that "new.id" is effectively still NULL at the point
> where the rule is processed, so the rule WHERE condition fails.  I'm not
> sure why you're bothering with that WHERE condition anyway ... but this
> seems to be another argument for moving INSERT's default-value-insertion
> step to before the rule rewriter.

I've committed fixes for this into CVS tip.
        regards, tom lane

On Thu, Apr 04, 2002 at 02:29:13PM -0500, Tom Lane wrote:
> Frank Joerdens <frank@joerdens.de> writes:
> > And here's the rule (it doesn't do anything real or interesting, I just
> > simplified it to the point where it looked like the example):
> 
> > CREATE RULE insert_level AS
> > ON INSERT TO tree
> > WHERE new.id > 0 DO
> > UPDATE tree SET leveling = 1
> > WHERE tree.oid = new.oid;
> 
> What's happening is that "new.id" is effectively still NULL at the point
> where the rule is processed, so the rule WHERE condition fails.  I'm not
> sure why you're bothering with that WHERE condition anyway ... 

Without the WHERE condition, all rows are affected by the update, and
not just the newly inserted one.

Regards, Frank

Frank Joerdens <frank@joerdens.de> writes:
>> What's happening is that "new.id" is effectively still NULL at the point
>> where the rule is processed, so the rule WHERE condition fails.  I'm not
>> sure why you're bothering with that WHERE condition anyway ... 

> Without the WHERE condition, all rows are affected by the update, and
> not just the newly inserted one.

I was speaking of the WHERE new.id > 0 part.  However, given that you
want to affect only the newly-inserted row, ISTM you'd be a lot better
off with a trigger instead of a rule.
        regards, tom lane

On Fri, Apr 05, 2002 at 10:42:18AM -0500, Tom Lane wrote:
> Frank Joerdens <frank@joerdens.de> writes:
> >> What's happening is that "new.id" is effectively still NULL at the point
> >> where the rule is processed, so the rule WHERE condition fails.  I'm not
> >> sure why you're bothering with that WHERE condition anyway ... 
> 
> > Without the WHERE condition, all rows are affected by the update, and
> > not just the newly inserted one.
> 
> I was speaking of the WHERE new.id > 0 part.

Oh yes, that's silly indeed. I just put it there to make the query look like
the example.

>  However, given that you
> want to affect only the newly-inserted row, ISTM you'd be a lot better
> off with a trigger instead of a rule.

I'll give that a try!

Thanks, Frank