pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
are NEW and OLD rule attributes broken?
2+ messages / 2 participants
[nested] [flat]

* are NEW and OLD rule attributes broken?
@ 2001-05-18 18:36 Forest Wilkinson <fspam@home.com>
  2001-05-18 19:20 ` Re: are NEW and OLD rule attributes broken? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Forest Wilkinson @ 2001-05-18 18:36 UTC (permalink / raw)
  To: pgsql-sql

(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



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

* Re: are NEW and OLD rule attributes broken?
  2001-05-18 18:36 are NEW and OLD rule attributes broken? Forest Wilkinson <fspam@home.com>
@ 2001-05-18 19:20 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2001-05-18 19:20 UTC (permalink / raw)
  To: Forest Wilkinson <fspam@home.com>; +Cc: pgsql-sql

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



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


end of thread, other threads:[~2001-05-18 19:20 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-05-18 18:36 are NEW and OLD rule attributes broken? Forest Wilkinson <fspam@home.com>
2001-05-18 19:20 ` Tom Lane <tgl@sss.pgh.pa.us>

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