BUG #2612: Strange behavior of incremented_by - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      2612
Logged by:          Sven Kirsten
Email address:      skirsten@gmx.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
| cache_value | log_cnt | is_cycled | is_called
---------------+------------+--------------+---------------------+----------
-+-------------+---------+-----------+-----------
 test_id_seq   |          1 |            1 | 9223372036854775807 |         1
|           1 |       1 | f         | f
(1 Zeile)


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
| cache_value | log_cnt | is_cycled | is_called
---------------+------------+--------------+---------------------+----------
-+-------------+---------+-----------+-----------
 test_id_seq   |          2 |            1 | 9223372036854775807 |         1
|           1 |      32 | f         | t
(1 Zeile)

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
| cache_value | log_cnt | is_cycled | is_called
---------------+------------+--------------+---------------------+----------
-+-------------+---------+-----------+-----------
 test_id_seq   |          4 |            1 | 9223372036854775807 |         1
|           1 |      31 | f         | t
(1 Zeile)

--> 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
| cache_value | log_cnt | is_cycled | is_called
---------------+------------+--------------+---------------------+----------
-+-------------+---------+-----------+-----------
 test_id_seq   |          6 |            1 | 9223372036854775807 |         1
|           1 |      29 | f         | t
(1 Zeile)

-> 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
| cache_value | log_cnt | is_cycled | is_called
---------------+------------+--------------+---------------------+----------
-+-------------+---------+-----------+-----------
 test_id_seq   |         10 |            2 | 9223372036854775807 |         1
|           1 |      27 | f         | t
(1 Zeile)

-> 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
| cache_value | log_cnt | is_cycled | is_called
---------------+------------+--------------+---------------------+----------
-+-------------+---------+-----------+-----------
 test_id_seq   |         14 |            2 | 9223372036854775807 |         1
|           1 |      25 | f         | t
(1 Zeile)

-> 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
| cache_value | log_cnt | is_cycled | is_called
---------------+------------+--------------+---------------------+----------
-+-------------+---------+-----------+-----------
 test_id_seq   |         20 |            3 | 9223372036854775807 |         1
|           1 |      23 | f         | t
(1 Zeile)

-> 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
| cache_value | log_cnt | is_cycled | is_called
---------------+------------+--------------+---------------------+----------
-+-------------+---------+-----------+-----------
 test_id_seq   |         26 |            3 | 9223372036854775807 |         1
|           1 |      21 | f         | t
(1 Zeile)

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

-----------

Is that a bug or a feature ?

greetings
Sven Kirsten