×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Serial out of sync with seq

Serial out of sync with seq

Serial out of sync with seq

(OP)
I know how to fix the seq when it is out of sync with a table:
  SELECT setval('tablename_seq',(SELECT max(tbl_id_col) FROM tablename));

My question: anyone know how they get out of sync in the first place?
The only code we have that inserts doesn't reference the id column, so PGsql should get the nextval and increment the seq. The only scenarios I can think of are someone manually inserting or updating a lower key record from the console.

Frank

RE: Serial out of sync with seq

if an insert fails! in any reason: constraints, foreign keys, transaction rollback, etc. the sequence is incremented and the next insert gets the next number

the sequences are not meant to fill the table with sequential values! )) sounds funny but it's true. If you need this you should do it with a function you create and locking!!) It is not postgresql gotcha, all the databases I know are like that.

(by the way this SELECT max(tbl_id_col) FROM tablename; is faster if you do it this way SELECT tbl_id_col FROM tablename ORDER BY tbl_id_col DESC LIMIT 1; -> this is postgresql gotcha ))

RE: Serial out of sync with seq

(OP)
Sorry, I meant getting out of sync in the other direction! Causing the "ERROR: Cannot insert a duplicate key into unique index tablename_pkey."

Thanks for the tip on the sort being faster than the search. In this case though, I was just manually resetting the seq so code that does inserts would work again.

Frank

RE: Serial out of sync with seq

well this problem is quite strange!
can you give an example of INSERT statment you use?

RE: Serial out of sync with seq

(OP)
Oh, it's just a simple insert:
$qry = "INSERT INTO tbl1(code, user, duty, seq,role, email)                              VALUES('$mycode',$uid,'$duty',$seq,'$_role','$_email')";
$db->query($qry);
The primary key column (proc_id) isn't mentioned in the query.

It works fine for months. Yesterday on our production system the seq was 1746. The highest record was 1747. I checked our development instance (copied from production about a month ago) and the seq was 1741 and the highest record was 1746. Odd that the same table in both instances was having the same problem.
If it were human intervention, there are only a couple of other people who could have done it. The only way I could imagine it happening was if they deleted a lower key record and reinserted it from our query console. Can't think of a reason anyone would mess with this particular table though...  

Frank

RE: Serial out of sync with seq

well actually I haven't such kind of a problem and would think of somehow to find out if somebody messed the things up, if not hmm ... turn on the query log and wait for another error like that and analyze the query logs ....

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close