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 SEQUENCEallows 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_seqand queried it one hundred times, so that the current value of the sequence is holding
How is it possible to make the sequence start over again?
A first possibility is to use the
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.1PostgreSQL version (and probably even before): see the old documentation here.
Wait, what about
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 RESTARTthe
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 powerful
ALTER SEQUENCEcommand only in recent versions. For older versions, the official documentation for the command
ALTER SEQUENCEclearly states that To restart the sequence at a different number, you must drop and re-create it!
Until version 18:
ALTER SEQUENCEcannot 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.,
ALTER SEQUENCEcommand 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
18and next. Therefore, if you are facing a previous Oracle version, you need to do the above set of commands to manually adjust the sequences.
ConclusionsPostgreSQL 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!