Thread: strangest thing happened

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

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



All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by
ourproprietary 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
informationproprietary 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
thatany unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have
receivedthis e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this
e-mailimmediately. 
Thank you.

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

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.                                 reedstrm@rice.edu
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
byour 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
informationproprietary 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
thatany unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have
receivedthis e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this
e-mailimmediately.
 
> Thank you.


> 
> -- 
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql

On 07/07/2010 12: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.

I've never heard of this happening. Are you certain nothing bypassed the
sequence and directly inserted a PK value?

> 2. Does anyone have a script to reset the sequences to match the tables?

Not heavily tested, but something like this might do the trick:

8<----------------------
CREATE OR REPLACE FUNCTION adjust_seqs(namespace text) RETURNS text AS $$
DECLARE rec         record; startval    bigint; sql         text; seqname     text;
BEGIN FOR rec in EXECUTE 'select table_name, column_name, column_default                     from
information_schema.columns                    where table_schema = ''' || namespace || '''                     and
column_defaultlike ''nextval%''' LOOP 
   seqname := pg_get_serial_sequence(rec.table_name, rec.column_name);   sql := 'select max(' || rec.column_name || ')
+1 from ' ||                                                       rec.table_name;   EXECUTE sql INTO startval;   IF
startvalIS NOT NULL THEN     sql := 'ALTER SEQUENCE ' || seqname || ' RESTART WITH ' ||
                           startval;     EXECUTE sql;     RAISE NOTICE '%', sql;   END IF; END LOOP; RETURN 'OK'; 
END;
$$ LANGUAGE plpgsql STRICT;

select adjust_seqs('public');
8<----------------------

HTH,

Joe

--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & Support

On 7/7/2010 3:42 PM, Ross J. Reedstrom wrote:
>
> 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
>


Yes i did miss read his statement,  oops =-O

The highest PK value in the table is 1071  but the next sequence is
1056.  That's  interesting and could be a big problem

Quoteing JonF

------------------------------------------------
I'm thinking/guessing it had something to do with
vacumn or the backup.
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.

--------------------------------------------------

BakupExec HMMM. Are you doing a file level backup, meaning backing up
PGDATA folder or are you doing pg_dump??

I don't think its a backup issue, unless you have done a restore. Which
this would say there are more problems else where


Are there invoices that use up numbers 1056 to 1071 in that table???

Does the app allow for resetting Sequence in a admin interface???  Many
apps have such features and someone could have accidentally rest the
value???

I would be looking at the log files for the Inserts into that table as a
means to track down what is the cause.  If there are no log files or
don't have enough detail, crank up the logging level and wait for it to
happen again???





All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by
ourproprietary 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
informationproprietary 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
thatany unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have
receivedthis e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this
e-mailimmediately. 
Thank you.

On Wednesday 07 July 2010 03:14:40 pm Justin Graf wrote:
> I would be looking at the log files for the Inserts into that table as a
> means to track down what is the cause.  If there are no log files or
> don't have enough detail, crank up the logging level and wait for it to
> happen again???


That is scary - let it happen again????  I'm not keeping enough info in the
log.  I actually turned off most of the info the log files are gathering
because the system has been running for 6-7 months without an issue.  I just
got a call around noon telling me something was going wrong.  That's when I
discovered the sequences were the wrong values.  I'm sure there has to be
some sort of real explanation - but I don't know what it is.

Johnf

Quoth johnf@jfcomputer.com (John):
> On Wednesday 07 July 2010 03:14:40 pm Justin Graf wrote:
> > I would be looking at the log files for the Inserts into that table as a
> > means to track down what is the cause.  If there are no log files or
> > don't have enough detail, crank up the logging level and wait for it to
> > happen again???
> 
> 
> That is scary - let it happen again????  I'm not keeping enough info in the 
> log.  I actually turned off most of the info the log files are gathering 
> because the system has been running for 6-7 months without an issue.  I just 
> got a call around noon telling me something was going wrong.  That's when I 
> discovered the sequences were the wrong values.  I'm sure there has to be 
> some sort of real explanation - but I don't know what it is.

There are several possible causes:
   - Something somewhere is inserting values directly into the serial     columns, without using the sequence. This can
beprevented by     REVOKEing INSERT and UPDATE on the relevant columns for all users.     If this causes problems
anywherein your app, those are good     places to start looking for bugs.
 
   - Something somewhere is manipulating the sequence. This can be     prevented by REVOKEing UPDATE on all sequences
forall users. You     may need some additional GRANTs of USAGE on sequences if parts of     the app were relying on
UPDATEto call nextval().
 

Obviously if your app routinely drops and creates tables you will need to
arrange for these permissions to be applied every time.
   - The database has become corrupted, perhaps by a badly-done backup     and restore. (I would not expect taking a
backupalone to cause     corruption, but if the backup isn't done right the backed-up copy     may be corrupt.) Have
youdone a restore recently?
 
   - Something I haven't thought of :).
   - A bug in Pg. While this is *extremely* unlikely, it must be     mentioned as a possibility.

Ben

On 7/7/2010 5:41 PM, John wrote:
> On Wednesday 07 July 2010 03:14:40 pm Justin Graf wrote:
>
>> I would be looking at the log files for the Inserts into that table as a
>> means to track down what is the cause.  If there are no log files or
>> don't have enough detail, crank up the logging level and wait for it to
>> happen again???
>>
>
> That is scary - let it happen again????  I'm not keeping enough info in the
> log.  I actually turned off most of the info the log files are gathering
> because the system has been running for 6-7 months without an issue.  I just
> got a call around noon telling me something was going wrong.  That's when I
> discovered the sequences were the wrong values.  I'm sure there has to be
> some sort of real explanation - but I don't know what it is.
>
> Johnf
>
>

Sometimes we just don't have a choice but to let things become broke
again to figure out what is the cause.

I had an odd case where Parent records could become deleted every once
in a while.  For what appeared to be no rhyme or reason
The app had around 1000 functions in pg/psql not counting triggers,  so
figuring out the cause by just staring at the code was not practical.
The log was already was set to record
     log_statement (all)
     log_line_prefix ('User %u, DB%d, Client%r, PID %p, Time %m, SID %c,
LineCount %l , TID %x);
     log_destination ('csvlog')

The logs rotated out every 30 days.  So I had good sample of the
commands sent to PG to figure out what went wrong

As the statements are logged all i had to do was search for
     Delete from cohead where cohead_id = XXXX

this gave me the transaction ID and the Session ID to start backtracking
to see if a Trigger or Function issued the delete.  Once I had figured
out that it was pg/psql procedure, i needed to figure out where in the
application called this seriously miss thought out  DELETE Sales Order
function.  I threw in a RAISE EXCEPTION in the psql and waited for the
Data entry people to come screaming.

Around 3 weeks later a data entry girl came and found me asking to
explain why her computer ordered her to come find me ASAP and why
nothing else matter but stop and find me.

With that error, I now the call stack from the app to figure out the
serious of events that allowed a Sales Order to be deleted.

After all was said and done there was a total of 50 records deleted out
of 60,000 (not even 0.1%)  not a big deal unless you are customer who's
order was deleted.

My experience has taught me never turn off logging because we never when
we may need it.





All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by
ourproprietary 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
informationproprietary 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
thatany unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have
receivedthis e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this
e-mailimmediately. 
Thank you.

Joe Conway schrieb:

>> 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.
> 
> I've never heard of this happening. Are you certain nothing bypassed the
> sequence and directly inserted a PK value?

Maybe the sequence is defined with "cycle" and starts again? Very 
unlikly, but a possibility.

Otherwise i would expect an bad backup/restore, direct insert of serials 
or manipulated them afterwards (by insert, update or an function).

Gretings from Germany,
Torsten

-- 
http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8 
verschiedenen Datenbanksystemen abstrahiert,
Queries von Applikationen trennt und automatisch die Query-Ergebnisse 
auswerten kann.