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