From: John <johnf@jfcomputer.com>
To: pgsql-sql@postgresql.org
Subject: Re: strangest thing happened
Date: Wed, 7 Jul 2010 13:38:46 -0700
User-Agent: KMail/1.9.9
References: <201007071259.03294.johnf@jfcomputer.com>
	<4C34F0B9.5020902@magwerks.com>
In-Reply-To: <4C34F0B9.5020902@magwerks.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <201007071338.46469.johnf@jfcomputer.com>

Yes I'm using auto_increment (serial data type).  No function, method inserts 
PK's anywhere in my code.  I'm thinking/guessing it had something to do with 
vacumn or the backup.  I have been using Postgres for a number of years now 
and I never seen this in the past.  So I'm really at a loss as how this could 
have occurred.  The backup is a windows product "exec" and I'm using a 
special plug-in from exec for the Linux backup.  But I still can't see this 
actually happening.

Well I have it running for the moment and I'll have to account black magic as 
the cause. 

Johnf
On Wednesday 07 July 2010 02:25:13 pm Justin Graf wrote:
> Are you using PG's  sequence/auto increment???
>
> If so.
> Once PG fires off the nextval() for the sequence that number is
> considered used and gone even if the transaction that called nextval()
> is rolled back
>
> Depending on how the app is written nextval() might be called, but allow
> the User to cancel the invoice creation before the insert into table is
> completed eating up Invoice numbers
>
> To reset Sequences number call
> Select setval('Sequence_Name', VAlue_To_Set_To);
>
> Most people ignore this kind of annoyance when sequence numbers jump.
> Now if it happens all the time where every X hours eating up Z number of
> sequence numbers then one needs to dig into the logs and figure out what
> is calling nextval()
>
> Search the logs to see what is calling nextval('My_Sequence')
>
> You may need to turn up logging to find it.
>
> On 7/7/2010 2:59 PM, John wrote:
> > I am the only developer, DBA etc.. for a small project.  Today (yesterday
> > was everything was perfect) many of the sequence numbers fell behind what
> > is the actual PK value.   For example the invoice PK sequence current
> > value = 1056 but the table PK was 1071.  Nobody (other than myself) knows
> > how to edit/access the postgres server.  So
> >
> > 1. Does anyone know how this could have happened?????? Other than human
> > interaction.
> >
> > 2. Does anyone have a script to reset the sequences to match the tables?
> >
> > Thanks in advance,
> >
> > Johnf