Skip site navigation (1) Skip section navigation (2)

problem with sequence PostgreSQL 7.3.4

From: Betsy Barker <betsy(dot)barker(at)supportservicesinc(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Cc: betsy(dot)barker(at)supportservicesinc(dot)com
Subject: problem with sequence PostgreSQL 7.3.4
Date: 2004-11-12 22:31:15
Message-ID: 20041112153115.7ece7e8c.betsy.barker@supportservicesinc.com (view raw or flat)
Thread:
Lists: pgsql-novice
I am using PostgreSQL 7.3.4 and have noticed something odd while using a sequence. After using 3000 values, I seem to be getting 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 the last_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

Responses

pgsql-novice by date

Next:From: Betsy BarkerDate: 2004-11-12 23:37:26
Subject: Re: problem with sequence PostgreSQL 7.3.4
Previous:From: Tom LaneDate: 2004-11-12 22:25:58
Subject: Re: problem with sequence PostgreSQL 7.3.4

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group