Re: Please get me out of this ASAP

On Thu, Feb 09, 2006 at 04:22:26AM +0000, shyam nair wrote:
> Now I have a situation like the sequence id is repeating. This
> applicaton is running since from 1999, this is the first time we
> get such a result.

What version of PostgreSQL are you running? Hopefully you've
upgraded since 1999.

> 1. This is the code we used to create sequence "seq_type_code"
>
> CREATE SEQUENCE "public"."seq_type_code"
> INCREMENT 1 MINVALUE 10
> MAXVALUE 9223372036854775807 START 10
> CACHE 1;

When do you create this sequence? Is it possible that it got dropped
and recreated, causing it to start at the beginning again? Or that
somebody used ALTER SEQUENCE or setval() to reset the start value?

> 2 This is query we used to insert value, here we were using the sequence.
> insert into tour_type(type_code,type_name,from_tour_num,end_tour_num) values(nextval('seq_type_code'),'type_name',tourRangeFrom,tourRangeTo);
>
> 3. This is the result we getting, see type code is repeating here type_code | type_name | from_tour_num | end_tour_num
> -----------+---------------------------+---------------+--------------
> 10 | TEST TOUR | 1 | 99
> 10 | FRANCE TEST TOUR | 100 | 199
> 11 | GERMENY TEST TOUR | 200 | 299
> 12 | HOLLAND TEST TOUR | 300 | 399

If this is a problem then why don't you have a primary key or unique
constraint on type_code? That doesn't explain why you're getting
duplicates, but at least you'd get an error when it happens. Or
do you have such a constraint and it isn't working?

Is it possible that somebody inserted a record with an explicit
type_code? That is, by specifying 10 instead of nextval('seq_type_code')?
Or that somebody updated an existing record? How many times has
the problem happened? If more than once, how often? Can you think
of anything that happened with the database around the time the
problem started? Have you enabled statement logging to see what
statements are actually being executed?

--
Michael Fuhr