are NEW and OLD rule attributes broken?

From: Forest Wilkinson <fspam(at)home(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: are NEW and OLD rule attributes broken?
Date: 2001-05-18 18:36:17
Message-ID: 01051811361700.01348@bartok
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2001-05-18 19:20:47 Re: are NEW and OLD rule attributes broken?
Previous Message BOUCHPAN-LERUST-JUERY Lionel 2001-05-18 17:48:41 Help with ECPG on debian Potato