Received: from femail1.sdc1.sfba.home.com (femail1.sdc1.sfba.home.com [24.0.95.81]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f4IIaJA23977 for ; Fri, 18 May 2001 14:36:19 -0400 (EDT) (envelope-from fspam@home.com) Received: from bartok ([24.9.226.149]) by femail1.sdc1.sfba.home.com (InterMail vM.4.01.03.20 201-229-121-120-20010223) with SMTP id <20010518183617.EZCZ27974.femail1.sdc1.sfba.home.com@bartok> for ; Fri, 18 May 2001 11:36:17 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Forest Wilkinson To: pgsql-sql@postgresql.org Subject: are NEW and OLD rule attributes broken? Date: Fri, 18 May 2001 11:36:17 -0700 X-Mailer: KMail [version 1.2] MIME-Version: 1.0 Message-Id: <01051811361700.01348@bartok> Content-Transfer-Encoding: 8bit X-Archive-Number: 200105/203 X-Sequence-Number: 3020 (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: 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