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
