Thread: are NEW and OLD rule attributes broken?

(Postgres 7.0.3, linux kernel 2.4.2, i386, red hat 7.1)

I'm trying to build rules to automatically populate several tables with 
references to any new rows inserted into a primary key table.  The primary 
key is a sequence.  Here's what's going on:


mydb=# create table foo (fooid serial primary key, foonote text);
NOTICE:  CREATE TABLE will create implicit sequence 'foo_fooid_seq' for 
SERIAL column 'foo.fooid'
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'foo_pkey' for 
table 'foo'
CREATE

mydb=# create table bar (fooid integer references foo (fooid) deferrable 
initially deferred, barnote text);
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY 
check(s)
CREATE

mydb=# create rule brule as on insert to foo do insert into bar (fooid, 
barnote) values ( new.fooid, 'blah');
CREATE 30351 1
mydb=# begin;
BEGIN

mydb=# insert into foo (foonote) values ('test row');
INSERT 30353 1

mydb=# select * from foo;fooid | foonote
-------+----------    2 | test row
(1 row)
mydb=# select * from bar;fooid | barnote
-------+---------    1 | blah
(1 row)
mydb=# commit;
ERROR:  <unnamed> referential integrity violation - key referenced from bar 
not found in foo


What's going on here?  My rule inserted a new row into bar, but the fooid 
it used was not the new value inserted into foo.  It looks like my rule is 
inserting "nextval('foo_fooid_seq')" into the bar table, rather than 
inserting the new primary key from the foo table.  Is this the intended 
behavior?  How can I get the result I want?


Cheers,

Forest

Forest Wilkinson <fspam@home.com> writes:
> What's going on here?  My rule inserted a new row into bar, but the fooid 
> it used was not the new value inserted into foo.  It looks like my rule is 
> inserting "nextval('foo_fooid_seq')" into the bar table, rather than 
> inserting the new primary key from the foo table.  Is this the intended 
> behavior?

NEW and OLD are effectively macros, not variables.  So yes, you get
extra evaluations of nextval() this way.  I think there are cases where
that is actually what you want (not in this case obviously :-().  It
would be fairly difficult to change, anyway.

> How can I get the result I want?

If you want to copy the data actually inserted, a trigger is a much
better bet than a rule.
        regards, tom lane