The following bug has been logged online:

Bug reference: 2612
Logged by: Sven Kirsten
Email address: skirsten (AT) gmx (DOT) net
PostgreSQL version: 8.1.4
Operating system: SusSE 9.1 and Debian 3.1
Description: Strange behavior of incremented_by
Details:

Hello,

I have created a table with a serial value. There is a rule watching
to that table and reporting changes to a another table.
If I make a insert to the table the serial value is incremented by
the double value of the incremented_by value form the _id_seq sequence
table.

Environment:

SELECT version();
version
----------------------------------------------------------------------------
----------------------------------
PostgreSQL 8.1.4 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2
20060708 (prerelease) (Debian 4.1.1-8)
(1 Zeile)

The problem is recreatable on a suse 9.1 and debian 3.1 system.

Here I have writen down step by step statements for you to create the
problem by self.

1) Create the primary table:

create table test (id serial,val int);
HINWEIS: CREATE TABLE erstellt implizit eine Sequenz »test_id_seq« für
die »serial«-Spalte »test.id«
CREATE TABLE


2) Create the second table:

create table log (id int);
CREATE TABLE

3) Create the rule
create or replace rule ruler as on insert to test do insert into log (id)
values (new.id);
CREATE RULE

4) Look into the sequence table:

select * from test_id_seq;
sequence_name | last_value | increment_by | max_value | min_value
-+-------------+---------+-----------+-----------
test_id_seq | 1 | 1 | 9223372036854775807 | 1


5) Make the first insert

insert into test (val) values (0);
INSERT 0 1

6) Look again

select * from test_id_seq;
sequence_name | last_value | increment_by | max_value | min_value
-+-------------+---------+-----------+-----------
test_id_seq | 2 | 1 | 9223372036854775807 | 1

7) Insert again
insert into test (val) values (0);
INSERT 0 1

8) Look again

select * from test_id_seq;
sequence_name | last_value | increment_by | max_value | min_value
-+-------------+---------+-----------+-----------
test_id_seq | 4 | 1 | 9223372036854775807 | 1

--> The last_value is skip to 4 ! Not the expected 3.

9) Insert again

insert into test (val) values (0);
INSERT 0 1

10) Loock again

select * from test_id_seq;
sequence_name | last_value | increment_by | max_value | min_value
-+-------------+---------+-----------+-----------
test_id_seq | 6 | 1 | 9223372036854775807 | 1

-> The last_value is skip to 6, not to 5.

11) Now change the increment_by value:

alter sequence test_id_seq increment by 2;
ALTER SEQUENCE

12) Insert again

insert into test (val) values (0);
INSERT 0 1

13) Look

select * from test_id_seq;
sequence_name | last_value | increment_by | max_value | min_value
-+-------------+---------+-----------+-----------
test_id_seq | 10 | 2 | 9223372036854775807 | 1

-> Wow, there is a 10, 6 + 2 = 10 ?

14) Insert again

insert into test (val) values (0);
INSERT 0 1

15) Look again

select * from test_id_seq;
sequence_name | last_value | increment_by | max_value | min_value
-+-------------+---------+-----------+-----------
test_id_seq | 14 | 2 | 9223372036854775807 | 1

-> There is a 14

16) Okay, last alter the seqence

alter sequence test_id_seq increment by 3;
ALTER SEQUENCE

17) Insert again

insert into test (val) values (0);
INSERT 0 1

18) Look again

select * from test_id_seq;
sequence_name | last_value | increment_by | max_value | min_value
-+-------------+---------+-----------+-----------
test_id_seq | 20 | 3 | 9223372036854775807 | 1

-> Wow, 14 + 3 = 20 ? Not 17 ?

19) Insert the last one

insert into test (val) values (0);
INSERT 0 1

20) Look the last one

select * from test_id_seq;
sequence_name | last_value | increment_by | max_value | min_value
-+-------------+---------+-----------+-----------
test_id_seq | 26 | 3 | 9223372036854775807 | 1

-> The last_value is incremented by 6 not by the expected 3.

-----------

Is that a bug or a feature ?

greetings
Sven Kirsten

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq