Re: Problem with a sequence being acted on by an on insert

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