0

From this article, I understand that the sequence can go out of sync in the following situations: -

  1. importing many rows with an INSERT script or restoring an extensive database;
  2. manually setting the wrong value of a sequence with the setval() function;
  3. inserting a new record into a table by manually specifying the id field in the INSERT query.

I understand how 2 and 3 can cause a sequence to go out of sync, but I don't understand how 1 can cause the sequence being out of sync issue. I tried finding it online, but the articles usually describe the out of sync issue, when it happens and how it can be fixed.

Can someone please help me understand this? Thank you.

Prabhatika Vij
  • 305
  • 3
  • 15
  • 1 is the same as 3 just with more rows –  Mar 07 '23 at 06:25
  • @a_horse_with_no_name Thanks for replying. But what if in 1, we import many rows without specifying the id field manually? I don't understand how 1 and 3 are the same. I might be missing something. – Prabhatika Vij Mar 07 '23 at 06:28
  • 1
    I don't see either how a large `INSERT` (unless it overrides the default value for the generated primary key) can cause a problem. Either shrug it off or ask the author what is meant. – Laurenz Albe Mar 07 '23 at 06:56
  • https://stackoverflow.com/questions/9314382/ –  Mar 07 '23 at 07:23
  • 1
    @PrabhatikaVij: if you manually provide a value for the (id) column, the sequence goes out of sync. It's irrelevant how many rows you insert while doing that. –  Mar 07 '23 at 07:25
  • #1 is garbage, except to the extent it is the same thing as #3. Maybe the author doesn't know what he is talking about, maybe he is trying to say something else (I don't know what it would be) and just worded it poorly. There is no shortage of either one of those things on the internet. – jjanes Mar 07 '23 at 16:07
  • @jjanes Thanks for replying, I would also have dismissed it had I not read the same thing in the answer here: https://stackoverflow.com/a/21639138/12242023. The answer mentions this can happen due to the mass import process (my understanding is that the author of the answer is talking about bulk inserts or a db restore). – Prabhatika Vij Mar 08 '23 at 03:30
  • 1
    Maybe the point he is trying to make is that you don't need to have done an INSERT manually to cause the problem, you could have accomplished that by a script that you just ran without paying much attention to. The size of the insert/copy doesn't matter, but maybe the larger the script the less likely you are to pay sufficient attention to each thing it does. – jjanes Mar 08 '23 at 15:14
  • @jjanes That makes sense. Thanks for taking the time to respond :) – Prabhatika Vij Mar 11 '23 at 06:59

0 Answers0