pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Rule trouble (looks to me exactly like the example)
6+ messages / 2 participants
[nested] [flat]

* Rule trouble (looks to me exactly like the example)
@ 2002-04-04 17:17  Frank Joerdens <frank@joerdens.de>
  0 siblings, 1 reply; 6+ messages in thread

From: Frank Joerdens @ 2002-04-04 17:17 UTC (permalink / raw)
  To: pgsql-sql

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 node
 11 |        0 |        0 |        0 |      1 | foo
 12 |        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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Rule trouble (looks to me exactly like the example)
@ 2002-04-04 19:29  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Frank Joerdens <frank@joerdens.de>
  0 siblings, 2 replies; 6+ messages in thread

From: Tom Lane @ 2002-04-04 19:29 UTC (permalink / raw)
  To: Frank Joerdens <frank@joerdens.de>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Rule trouble (looks to me exactly like the example)
@ 2002-04-05 05:59  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 6+ messages in thread

From: Tom Lane @ 2002-04-05 05:59 UTC (permalink / raw)
  To: Frank Joerdens <frank@joerdens.de>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Rule trouble (looks to me exactly like the example)
@ 2002-04-05 10:08  Frank Joerdens <frank@joerdens.de>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 6+ messages in thread

From: Frank Joerdens @ 2002-04-05 10:08 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Rule trouble (looks to me exactly like the example)
@ 2002-04-05 15:42  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Frank Joerdens <frank@joerdens.de>
  0 siblings, 1 reply; 6+ messages in thread

From: Tom Lane @ 2002-04-05 15:42 UTC (permalink / raw)
  To: Frank Joerdens <frank@joerdens.de>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Rule trouble (looks to me exactly like the example)
@ 2002-04-07 14:15  Frank Joerdens <frank@joerdens.de>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 6+ messages in thread

From: Frank Joerdens @ 2002-04-07 14:15 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2002-04-07 14:15 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-04-04 17:17 Rule trouble (looks to me exactly like the example) Frank Joerdens <frank@joerdens.de>
2002-04-04 19:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-04-05 05:59   ` Tom Lane <tgl@sss.pgh.pa.us>
2002-04-05 10:08   ` Frank Joerdens <frank@joerdens.de>
2002-04-05 15:42     ` Tom Lane <tgl@sss.pgh.pa.us>
2002-04-07 14:15       ` Frank Joerdens <frank@joerdens.de>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox