Postgres Pro
Downloads
Home   >   mailing lists

Thread: Please get me out of this ASAP

Please get me out of this ASAP

From
shyam nair
Date:
My name is shyamkumar. Right now I am using Postgres as backend for development. And J2ee technology as front end.

Now I have a situation like the sequence id is repeating. This applicaton is running  since from 1999, this is the
firsttime we get such a result.  

I will give you more detail,

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;
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

I would like to know more about this, and I have to submit a detailed presentaton about this.
Hope you will provide valid informations and will help us to recover from this situation.

Yours Sincerly,

P.Shyamkumar
Software Engineer
Maxxion Systems.





---------------------------------
 Jiyo cricket on Yahoo! India cricket
Yahoo! Messenger Mobile Stay in touch with your buddies all the time.

Re: Please get me out of this ASAP

From
Michael Fuhr
Date:
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