Restarting a sequence: how hard could it be? (PostgreSQL and Oracle)
One reason I like PostgreSQL so much is that it makes me feel at home: it has a very consistent and coherent interface to its objects. An example of this, is the management of sequences:ALTER SEQUENCE allows you to modify pretty much every detail about a sequence, in particular to restart it from its initial value.
Let’s see this in action:
testdb=> create sequence batch_seq
increment by 1 start with 1;
CREATE SEQUENCE
testdb=> do $$
declare
i int;
begin
for i in 1..100 loop
perform nextval( 'batch_seq' );
end loop;
end
$$
;
DO
testdb=> select currval( 'batch_seq' );
currval
---------
100
In the above piece of code, I’ve created a
batch_seq and queried it one hundred times, so that the current value of the sequence is holding 100.
How is it possible to make the sequence start over again?
A first possibility is to use the
setval function:
testdb=> select setval( 'batch_seq', 1 );
setval
--------
1
testdb=> select currval( 'batch_seq' );
currval
---------
1
Another option is to use
ALTER SEQUENCE, that is a command aimed to this purpose (and others):
testdb=> alter sequence batch_seq restart;
ALTER SEQUENCE
testdb=> select nextval( 'batch_seq' );
nextval
---------
1
An important thing to note here, is that the only option specified has been
RESTART, that is the sequence already knows what restarting means: it means reset to its original starting value.
It is also possible to specify a specific value for the restarting:
testdb=> alter sequence batch_seq restart with 666;
ALTER SEQUENCE
testdb=> select nextval( 'batch_seq' );
nextval
---------
666
That’s so simple!
The above behaviour is guaranteed back to the
8.1 PostgreSQL version (and probably even before): see the old documentation here.
Wait, what about currval()?
The careful reader has probably noted that I used nextval() to see if the reset of a sequence worked, instead of currval(). The reason can be found in the official documentation: *Returns the value most recently obtained by nextval for this sequence *in the current session . *
It is easy to test this:
testdb=> select nextval( 'batch_seq' );
nextval
---------
667
testdb=> alter sequence batch_seq restart with 999;
ALTER SEQUENCE
testdb=> select currval( 'batch_seq' );
currval
---------
667
testdb=> select nextval( 'batch_seq' );
nextval
---------
999
As you can see, after an
ALTER SEQUENCE RESTART the currval() result remains unchanged (it is the last polled value within the current session), while nextval() (that actually queries the sequence) provides the right and expected value.
What about Oracle sequences?
Oracle provides a powerfulALTER SEQUENCE command only in recent versions. For older versions, the official documentation for the command ALTER SEQUENCE clearly states that To restart the sequence at a different number, you must drop and re-create it!
Err… what?
Until version 18:
ALTER SEQUENCE cannot restart the sequence. What is then the solution? You need to trigger a sequence update:
- change the increment of the sequence to effectively subtract values;
- ask the sequence a new value, so that it applies the subtraction;
- set the increment to its correct value.
This means you have to do something like the following:
SQL> select batch_seq.nextval from dual;
SQL> alter sequence batch_seq increment by -666;
SQL> select batch_seq.nextval from dual;
SQL> alter sequence batch_seq increment by 1;
I don’t like this approach very much, because it is error prone and requires you to do some computation ensuring you are not going to go outside the sequence boundaries.
In recent versions of Oracle Database (e.g.,
21), the ALTER SEQUENCE command works as in PostgreSQL, i.e., as in the standard SQL, and this is good, of course.
With a quick search for within the Oracle documentation about
ALTER SEQUENCE, the right behaviour has been introduced in Oracle 18 and next. Therefore, if you are facing a previous Oracle version, you need to do the above set of commands to manually adjust the sequences.
Conclusions
PostgreSQL has a very strict approach to the SQL standard, that roots even in old versions. Unluckily, Oracle is not the same, and older versions require some tricks to simulate the PostgreSQL behavior.This is not meant to be a flame or a comparison, it simply indicates how counter-intuitive could be to handle Oracle once you have been used to PostgreSQL!