I am using PostgreSQL 7.3.4 and have noticed something odd while using a sequence. After using 3000 values, I seem to
begetting a duplicate. I'm running a stored procedure that uses this sequence and it fails with a :
WARNING: Error occurred while executing PL/pgSQL function calc_sum_facilities
WARNING: line 678 at SQL statement
ERROR: Cannot insert a duplicate key into unique index calculationdifferential_pkey
I ran the program 4 times and checked the value of the sequence after each failure. There is a 3000 difference. Look at
thelast_value in each case.
ssi=> select * from calc_id_seq;
sequence_name | last_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled |
is_called
---------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
calc_id_seq | 4527999 | 1 | 9223372036854775807 | 1 | 1000 | 32 | f | t
sequence_name | last_value | increment_by | max_value | min_valu
---------------+------------+--------------+---------------------+---------
calc_id_seq | 4530999 | 1 | 9223372036854775807 |
ssi=> select * from calc_id_seq;
sequence_name | last_value | increment_by | max_value | min_valu
---------------+------------+--------------+---------------------+---------
calc_id_seq | 4533999 | 1 | 9223372036854775807 |
(1 row)
ssi=> select * from calc_id_seq;
sequence_name | last_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled |
is_called
---------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
calc_id_seq | 4536999 | 1 | 9223372036854775807 | 1 | 1000 | 32 | f | t
(1 row)
Does anyone know what is going on , or how I can get the sequence to not act this way?
Thank you,
--
Betsy Barker
IT Manager
Support Services, Inc
(720)489-1630 X 38