Thread: Is this a bug? Sequences and rules

Hi;

I noticed that rules were not behaving properly.  I created a test case,
and it looks like the sequence is getting double-incrimented.  Is this
the way this is supposed to work?

I know triggers would be better for something like this but I find these
results... surprising....

Version is 8.1.4

postgres=# create table test1 (id serial, test text);
NOTICE:  CREATE TABLE will create implicit sequence "test1_id_seq" for
serial column "test1.id"
CREATE TABLE
postgres=# create table test2 (id int);
CREATE TABLE
postgres=# create rule insert as on insert to test1 do also insert into
test2 (id) values (new.id);
CREATE RULE

postgres=# insert into test1 (test) values (1);
INSERT 0 1
postgres=# insert into test1 (test) values (1);
INSERT 0 1
postgres=# insert into test1 (test) values (1);
INSERT 0 1
postgres=# select * from test1;
 id | test
----+------
  1 | 1
  3 | 1
  5 | 1
(3 rows)

postgres=# select * from test2;
 id
----
  2
  4
  6
(3 rows)

Chris Travers wrote:
> Hi;
>
> I noticed that rules were not behaving properly.  I created a test
> case, and it looks like the sequence is getting double-incrimented.
> Is this the way this is supposed to work?
>
> I know triggers would be better for something like this but I find
> these results... surprising....
>
> postgres=# insert into test1 (test) values (1);

This insert is implicitly specifying that you should use the default
value as the value for the ID column. The default is
nextval('test1_id_seq'), so this is what gets inserted into test2 as
well, thus incrementing the sequence twice.

--
Tommy Gildseth



--
Tommy Gildseth
DBA, Gruppe for databasedrift
Universitetet i Oslo, USIT
m: +47 45 86 38 50

am  Tue, dem 10.04.2007, um 22:49:27 -0700 mailte Chris Travers folgendes:
> Hi;
>
> I noticed that rules were not behaving properly.  I created a test case,
> and it looks like the sequence is getting double-incrimented.  Is this
> the way this is supposed to work?
>
> I know triggers would be better for something like this but I find these
> results... surprising....
>
> Version is 8.1.4
>
> postgres=# create table test1 (id serial, test text);
> NOTICE:  CREATE TABLE will create implicit sequence "test1_id_seq" for
> serial column "test1.id"
> CREATE TABLE
> postgres=# create table test2 (id int);
> CREATE TABLE
> postgres=# create rule insert as on insert to test1 do also insert into
> test2 (id) values (new.id);
> CREATE RULE

Use this instead:
create rule insert as on insert to test1 do also insert into test2 (id) values (currval('test1_id_seq'));


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net