Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

For the third time it has happened to me that a sequence has been set to an initial value (zero or one, not sure) while in the table, there are around 1500 records. When it happens, new rows cannot be inserted from my application. So I would like to know possible causes for the sequence to be out of sync like that. Because I have no idea why this problem is occurring.

share|improve this question
 
There are three different possibilities: You are running a drop sequence ..; create sequence ...;, you are calling setval(..) or you use truncate table restart identity somewhere. Also, please show us the output of select version() –  a_horse_with_no_name Aug 29 at 13:48

2 Answers

up vote 1 down vote accepted

There are a few possible cases where things can get out of sync.

  1. Very old versions (unsupported) used to sometimes fail to set sequences on backup restore. If you have manual backup and restore routines, this is somewhere to look.

  2. setval('sequence_name', 1) will set to to 1.

Those are your only two possibilities unless you have a short cycle, and are cycling.

share|improve this answer
 
it was my stupid mistake. The sequence has never been used because It was specified in the import file which I didn't notice. –  clime Aug 29 at 14:03
up vote 0 down vote

Was your sequence created with the "CYCLE" option? You can check by querying the sequence directly

select * from your_seq;

Look at the "max_value" and "is_cycled" columns. What those attributes mean is covered in more detail at http://www.postgresql.org/docs/current/static/sql-createsequence.html

share|improve this answer
 
thx, is_cycled is false and max_value is huge. –  clime Aug 7 at 14:31
1  
Do you have anywhere in your application (or external jobs/crons/etc) that might be issuing a "setval()" on the sequence to reset it? –  bma Aug 7 at 14:34
 
The sequence has never been used because It was specified in the import file which I didn't notice. –  clime Aug 29 at 14:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.