Problem with a sequence being acted on by an on insert rule. - Mailing list pgsql-bugs

Apologies if this bug has already been reported and I am also reasonably
new to postgresql so I might be doing something stupid :-)

Anyway to replicate the problem:

Initial tables:

- create sequence autonumber increment 1 minvalue 0 start 0;
- create table testtable1 ( pk int primary key );
- create table testtable2 ( fk int primary key references
testtable1(pk));
- create rule updatetesttable2 as on insert to testtable1 do (insert
into testable2 (fk) values( new.pk ));

Testing:

- insert into testtable1 values ('1');
- select * from testtable2;
<produces>
fk
----
  1
(1 row)

The Problem:
- insert into testtable1 values ( (select nextval('autonumber')));
<produces>
ERROR:  <unnamed> referential integrity violation - key referenced from
testtable2 not found in testtable1

This confused me for a while until I did some testing and removed the
foreign key from testtable2 i.e.

- drop table testtable2;
- drop rule updatetesttable2;
- create table testtable2 (fk int);
- create rule updatetesttable2 as on insert to testtable1 do (insert
into testable2 (fk) values( new.pk ));

Now we have:

- insert into testtable1 values ( ( select nextval('autonumber')));
- select * from testtable1;
 pk
-----
   1
 155
(2 rows)
- select * from testtable2;
 fk
-----
 156
(1 row)


So in other words the rule action new.pk actually pulled the next number
from the sequence autonumber thus failing the refential integrity checks
above. There is no rush to fix this as I have modified the rule as
follows which works but still wastes a sequence number:

- create rule updatetesttable2 as on insert to testtable1 do (insert
into testable2 (fk) values( new.pk - 1 ));


Version information as follows:

- Linux distro - Debian stable
- Kernel version 2.4.19
- select version();
            version
---------------------------------------------------------------
 PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4
(1 row)


Thanks


Mark