On Wed, Jul 07, 2010 at 04:25:13PM -0500, 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.
Justin, you're missing that John reported that the sequences are
_behind_ the table. This only happens for me if I've been doing
bulk data loads. Then I use:
select setval(sequence_name,max(serial_id_column)) from table_with_serial_id;
You do need to trackdown how this might have happened, though. Any
clever code doing it's own 'serial' incrementing?
Ross
--
Ross Reedstrom, Ph.D. [email protected]
Systems Engineer & Admin, Research Scientist phone: 713-348-6166
The Connexions Project http://cnx.org fax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE
>
>
> 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
> >
> >
>
>
>
> All legitimate Magwerks Corporation quotations are sent in a .PDF file
> attachment with a unique ID number generated by our proprietary quotation
> system. Quotations received via any other form of communication will not be
> honored.
>
> CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain
> legally privileged, confidential or other information proprietary to Magwerks
> Corporation and is intended solely for the use of the individual to whom it
> addresses. If the reader of this e-mail is not the intended recipient or
> authorized agent, the reader is hereby notified that any unauthorized
> viewing, dissemination, distribution or copying of this e-mail is strictly
> prohibited. If you have received this e-mail in error, please notify the
> sender by replying to this message and destroy all occurrences of this e-mail
> immediately.
> Thank you.
>
> --
> Sent via pgsql-sql mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
