Thread: Problem with a sequence being acted on by an on insert rule.

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

On 5 Nov 2002, Mark Le Huray wrote:

> 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 ));

You probably really want a trigger, not a rule for this. Rules are like a
substitution system, so the second insert becomes something like insert
into testtable2(fk) values ((select nextval('autonumber'));  This is
the expected behavior.

I think both currval('autonumber') and your solution (-1) will fail to
do what you expect if multiple rows are being inserted in one insert
statement (insert .. select for example).

Mark Le Huray <mark.lehuray@dsl.pipex.com> writes:
> - 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 ));
> - insert into testtable1 values ( (select nextval('autonumber')));

This does not work very well, as you've discovered, because a rule is
really a macro.  As such, it has all the usual issues with multiple
evaluations of arguments.

I'd recommend a trigger, not a rule, for propagating information from
one table to another.  Although the notational cruft is messier, the
trigger will likely be faster as well as impervious to this type of
problem.

            regards, tom lane