From: John <johnf@jfcomputer.com>
To: pgsql-sql@postgresql.org
Subject: Re: strangest thing happened
Date: Wed, 7 Jul 2010 13:38:46 -0700
Message-ID: <201007071338.46469.johnf@jfcomputer.com> (raw)
In-Reply-To: <4C34F0B9.5020902@magwerks.com>
References: <201007071259.03294.johnf@jfcomputer.com>
<4C34F0B9.5020902@magwerks.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: johnf@jfcomputer.com
Subject: Re: strangest thing happened
In-Reply-To: <201007071338.46469.johnf@jfcomputer.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox