SEQUENCE Functions

nextval(regclass)

Description: Specifies an increasing sequence and returns a new value.

• To avoid blocking of concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value is fetched, it is considered used, even if the transaction that did the nextval later aborts. This means that aborted transactions may leave unused "holes" in the sequence of assigned values. Therefore, the GaussDB sequence object cannot be used to obtain "holeless" sequences.
• If the nextval function is pushed to DNs, each DN will automatically connect to the GTM and requests the next value. For example, in the insert into t1 select xxx statement, a column in table t1 needs to call the nextval function. If the maximum number of connections on the GTM is 8192, this type of pushed statements occupies too many GTM connections. Therefore, the number of concurrent connections for these statements is limited to 7000 divided by the number of clusterDNs. The other connections are reserved for other statements.

Return type: numeric

The nextval function can be called in either of the following ways (In example 2, the ORA syntax is supported. Currently, the sequence name cannot contain a dot.):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

gaussdb=# CREATE SEQUENCE seqDemo;

-- Example 1:
gaussdb=# SELECT nextval('seqDemo'); 
 nextval
---------
       1
(1 row)

-- Example 2:
gaussdb=# SELECT seqDemo.nextval; 
 nextval
---------
       2
(1 row)
gaussdb=# DROP SEQUENCE seqDemo;

currval(regclass)

Description: Returns the last value of nextval in the current session. If nextval has not been called for the specified sequence in the current session, an error is reported when currval is called. By default, currval is disabled. To enable it, set enable_beta_features to true. After enable_beta_features is set to true, nextval will not be pushed down.

Return type: numeric

The currval function can be called in either of the following ways (In example 2, the ORA syntax is supported. Currently, the sequence name cannot contain a dot.):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

gaussdb=# CREATE SEQUENCE seq1;
gaussdb=# SELECT nextval('seq1'); 
gaussdb=# SET enable_beta_features = true;

-- Example 1:
gaussdb=# SELECT currval('seq1'); 
 currval
---------
       1
(1 row)

-- Example 2:
gaussdb=# SELECT seq1.currval seq1; 
 seq1
------
    1
(1 row)
gaussdb=# DROP SEQUENCE seq1;
gaussdb=# SET enable_beta_features = false;

lastval()

Returns the last value of nextval in the current session. This function is equivalent to currval, except that it does not use sequence names as parameters. It fetches the sequence used by nextval last time in the current session. If nextval has not been called in the current session, an error is reported when lastval is called.

By default, lastval is disabled. To enable it, set enable_beta_features or lastval_supported to true. After lastval is enabled, nextval() will not be pushed down.

Return type: numeric

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

gaussdb=# CREATE SEQUENCE seq1;
gaussdb=# SELECT nextval('seq1'); 
gaussdb=# SET enable_beta_features = true;
gaussdb=# SELECT lastval(); 
 lastval
---------
       1
(1 row)
gaussdb=# DROP SEQUENCE seq1;
gaussdb=# SET enable_beta_features = false;

setval(regclass, bigint)

Description: Sets the current value of a sequence.

Return type: numeric

Example:

1
2
3
4
5
6
7
8

gaussdb=# CREATE SEQUENCE seqDemo;
gaussdb=# SELECT nextval('seqDemo'); 
gaussdb=# SELECT setval('seqDemo',3);
 setval
--------
      3
(1 row)
gaussdb=# DROP SEQUENCE seqDemo;

setval(regclass, numeric, Boolean)

Sets the current value of a sequence and the is_called sign.

Return type: numeric

Example:

1
2
3
4
5
6
7
8

gaussdb=# CREATE SEQUENCE seqDemo;
gaussdb=# SELECT nextval('seqDemo'); 
gaussdb=# SELECT setval('seqDemo',5,true);
 setval
--------
      5
(1 row)
gaussdb=# DROP SEQUENCE seqDemo;

The current session and GTM will take effect immediately after setval is performed. If other sessions have buffered sequence values, setval will take effect only after the values are used up. Therefore, to prevent sequence value conflicts, you are advised to perform setval with caution.

Because the sequence is non-transactional, changes made by setval will not be canceled when a transaction rolled back.

pg_sequence_last_value(sequence_oid oid, OUT cache_value int16, OUT last_value int16)

Description: Obtains the parameters of a specified sequence, including the cache value and current value.

Return type: int16, int16

gs_get_sequence_parameters(sequence_oid)

Description: Obtains the parameters of a specified sequence, including the start value, minimum value, maximum value, and incremental value.

Return type: int16, int16, int16, int16, Boolean

gs_get_sequence_last_value(sequence_oid oid, OUT cache_value int16, OUT last_value int16)

Description: Obtains the parameters of a specified sequence, including the cache value and current value.

Return type: int16, int16

get_global_last_value_from_gtm(sequence_regclass regclass, OUT is_called boolean, OUT last_value numeric)

Description: Obtains the flag indicating whether a sequence has been called and the latest value of the requested sequence from GTM.

Parameter: name or OID of a sequence to be queried.

Return type: Boolean, numeric

• For upgrade from a version that does not support get_global_last_value_from_gtm to a version that supports get_global_last_value_from_gtm, this function cannot be used during the upgrade observation period.
• The get_global_last_value_from_gtm function requires communication with the GTM each time it is called; therefore, it is not recommended to execute it frequently.
• The latest value that has been requested for a sequence does not necessarily indicate the maximum value that has been used. If the sequence's cache is greater than 1 and the sequence values requested by the node are not used up, this will leave unused "holes."