nextval()

Synopsis

Use the nextval( sequence_name ) function to return the next value from the sequence cache for the current session. If no more values are available in the cache, the session allocates a block of numbers for the cache and returns the first one. The number of elements allocated is determined by the cache option specified as part of the CREATE SEQUENCE statement.

Semantics

sequence_name

Specify the name of the sequence.

An error is raised if a sequence reaches its minimum or maximum value.

Caching values on the YB-TServer

If ysql_sequence_cache_method is set to server, sequence values are cached on the YB-TServer, to be shared with all connections on that YB-TServer. This is beneficial when many connections on the server are expected to get the next value of a sequence. Normally, each connection waits for replication to complete, which can be expensive, especially in a multi-region cluster. With the server cache method, only one connection waits for Raft replication and the rest retrieve values from the same cached range.

When the server cache method is used, the connection cache size is implicitly set to 1. When the cache method is changed from connection to server, sequences continue to use the connection cache until it is exhausted, at which point they begin using the server cache. When the cache method is changed from server to connection, sequences immediately begin using a connection cache. The server cache is not cleared in this case, and its values can later be retrieved if the cache method is again set to server.

Limitations

Calling setval on a sequence or restarting a sequence is not currently compatible with server caching, as the cache will not be cleared. This issue is tracked in GitHub issue #16225.
Bidirectional xCluster replication and point-in-time-restore are not compatible with sequences and therefore are not compatible with this feature.

Examples

Create a basic sequence that increments by 1 every time nextval() is called

yugabyte=# CREATE SEQUENCE s;

CREATE SEQUENCE

Call nextval() a couple of times.

yugabyte=# SELECT nextval('s');

 nextval
---------
       1
(1 row)

yugabyte=# SELECT nextval('s');

 nextval
---------
       2
(1 row)

Create a sequence with a cache of 3 values

yugabyte=# CREATE SEQUENCE s2 CACHE 3;

CREATE SEQUENCE

In the same session, call nextval(). The first time it's called, the session's cache will allocate numbers 1, 2, and 3. This means that the data for this sequence will have its last_val set to 3. This modification requires two RPC requests.

SELECT nextval('s2');

 nextval
---------
       1
(1 row)

The next call of nextval() in the same session will not generate new numbers for the sequence, so it is much faster than the first nextval() call because it will just use the next value available from the cache.

SELECT nextval('s2');

nextval
---------
       2
(1 row)