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


--


Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen!
Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster