Serial out of sync with seq

[FRrogoy]

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

 

[ceco]

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

 

[FRrogoy]

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

 

[ceco]

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

 

[FRrogoy]

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

 

[ceco]

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