Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003
    Tampa, Florida

    Unanswered: postgres sequence

    I'm using the sequence for table primary key and it increases by 1,
    I have noticed sometimes the data get lost and the sequence get skipped, that means when ever I inserted a record say the seq was 10, the next one should be 11, instead it skips to 13 and I see 2 records has been missing.
    What causes this problem Please let me know if anyone knows the answer to this question.

  2. #2
    Join Date
    May 2002
    Florida, USA
    Sequences are a one-way procedure. I believe that every time you attempt to insert a row that uses a sequence, that sequence is advanced, even if the INSERT fails.

    This can be especially important in a heavy multi-user application, where many users may be trying to INSERT to a table at the same time. Thus if for some reason there is too much resource contention, a sequence may be advanced, although the INSERT fails.

    Remember, sequences are independent of the table. Sequences can be advanced simply by calling SELECT nextval('squence_name');. Once advanced, they cannot be reversed. In fact, for this reason, a sequence can be shared among several tables, because every sequence call is atomic.

    Sequences aren't for the purpose of keeping a perfect numerical progression, but for guaranteeing uniqueness.

    So, your problem doesn't mean records are missing. It just means that the sequence has been advanced, but not used in the table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts