Thread: changing the last_value in a sequence

I've discovered a problem in our legacy software todo where, somewhere
along the road, over the last year, a "todo" item in our todo table got
"skipped".

This means that I have 689 records in the table when I should have 690.

I fixed this by inserting a record for the "missing" todo, and updating
it's id to be that of the "missing" todo.

But, I now realize that the problem is that the sequence on that table has
been updating (as it should), but that my insert for the missing todo
helped _for_now_, but I'm going to run across the same issue very soon.
Inserting the "missing" todo took care of the gap in my list of todo's
(1 through 690), but now, the last_value in the sequence for this table
is (691):

cmsdb=# select last_value from todo_todoid_seq;
 last_value
------------
        691
(1 row)

Meaning that the next todo I insert will have a value of 692, and once
again, I'll have a missing todo (#691).

I quickly figured out that I can't update the last_value in my sequence
table.  Any suggestions out ther on how I can tackle this issue to bring
the number of records in the todo table and my last_value into sync?

    Sincere Thanks in Advance,
    Aurangzeb

"Aurangzeb M. Agha" <aagha@bigfoot.com> writes:
> I quickly figured out that I can't update the last_value in my sequence
> table.

You can use setval().  However, if you're using a sequence to generate
IDs then it's fundamentally broken to assume there will be no gaps in
the assigned IDs.  (You'll get a gap anytime an inserting transaction
fails after having done nextval().)  I suggest rethinking your assumption
that there must be a consecutive sequence of TODO numbers ...

            regards, tom lane