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

Re: Please get me out of this ASAP

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: shyam nair <shyam_nair81(at)yahoo(dot)co(dot)in>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Please get me out of this ASAP
Date: 2006-02-09 12:39:50
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
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

In response to

pgsql-bugs by date

Next:From: Andrew - SupernewsDate: 2006-02-09 15:12:20
Subject: Re: BUG #2243: Postgresql fails to finish some queries
Previous:From: Ian MooreDate: 2006-02-09 11:26:46
Subject: BUG #2250: JSTL parameterized queries inserting numeric values

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