I have a script (that can fail) with multiple inserts that include nextval as
insert into table a (id, value) (id_seq.nextval, 'value');
...
If script fails and I rollback the inserts, the sequence isn't rollback and multiple ids won't be in use
Is there a best practice to avoid "losing" ids from sequence on multiple inserts?
I found similar questions but in different context
It is not possible to set the current value of an existing sequence. If you need to change the value(currentvalue) of a sequence you have to "increment it back" or recreate it.
"Incrementing back"(dirty workaround):
-- current value is 100
ALTER SEQUENCE seq_x INCREMENT BY -25;--number of wrong increments or max(id)-seq_x.nextval in a procedure
SELECT seq_x.NEXTVAL FROM dual; -increments by 1 step, in this case -25
-- -> new current value is 75
ALTER SEQUENCE seq_x INCREMENT BY 1;
Recreate it:
DROP SEQUENCE seq_x;
CREATE SEQUENCE seq_x INCREMENT BY 1 START WITH 75;-- desired start value
-- You need to reassign privileges, if some were given
If you're using a auto increment column instead, you can set it to the highes value in the table by using
ALTER TABLE table_name MODIFY ID GENERATED ALWAYS AS IDENTITY (START WITH LIMIT VALUE);
-- customize as you need it, i.e. GENERATED BY DEFAULT instead of GENERATED ALWAYS
If you always need the last value for whatever reason, you have to select MAX(id) from the given table.
But: Missing ids in a table dont hurt performance or anything else, it only hurts some ppl eyes :)