SEQUENCE Functions
In database application development, generating unique and incrementing sequence numbers is a fundamental and common requirement. For example, in an e-commerce system, a unique order number is generated for each order, an auto-increment primary key ID is assigned during user registration, and a unique serial number is generated for each log record. In these scenarios, it is necessary to securely obtain unique sequence values in a multi-user concurrent environment.
DWS provides the SEQUENCE function (including nextval, currval, lastval, and setval) to provide a simple and secure method for users to obtain subsequent sequence values from sequence objects. Compared with the method of generating sequence numbers at the application layer, the SEQUENCE function uses the global transaction manager (GTM) to coordinate the allocation of sequence values in a unified manner. This ensures the uniqueness and continuity of sequence values in a distributed cluster environment and avoids concurrent conflicts.
Concepts
- regclass: A data type in DWS, which indicates the OID (object identifier) of a row in the pg_class system catalog. In the sequence function, the regclass parameter is used to specify the name or OID of a sequence object. The system automatically parses the sequence name (for example, seqDemo) into the corresponding OID.
- is_called flag: An internal status flag of a sequence object that controls the return behavior of the nextval operation. When is_called is true, it indicates that the current sequence value has been called. In this case, the nextval operation will return the current value plus the increment. When is_called is false, it indicates that the current sequence value has not been called. In this case, the nextval operation will return the current value.
- Pushdown: A query optimization mechanism of DWS, which refers to delivering SQL statements from the CN to DNs for direct execution, reducing data transmission between the CN and DNs. When nextval is pushed down to DNs, each DN will connect to the GTM to request the sequence value, which may consume a large number of GTM connections.
Preparation: Creating a Sequence
Before using the SEQUENCE function, you need to create a sequence object. Run the following SQL statements to create a sample sequence:
1 2 3 4 | -- Create a sample sequence seqDemo with the start value of 1 and step of 1. CREATE SEQUENCE seqDemo START 1; -- Create a sample sequence seq1 with the start value of 1 and step of 1. CREATE SEQUENCE seq1 START 1; |
For details about the complete syntax and parameters of CREATE SEQUENCE, see CREATE SEQUENCE. After a sequence is created, you can use the following SEQUENCE functions to perform operations on the sequence object.
nextval(regclass)
Specifies an increasing sequence and returns a new value.
- To avoid blocking of concurrent transactions that obtain values from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched, it is considered used and will not be returned again. This occurs even if the operation is within a transaction that later aborts, or if the calling query ultimately does not use the value. This situation leaves unused "holes" in the sequence of assigned values. Therefore, DWS sequence objects cannot be used to obtain gaps-free sequences.
- If nextval is pushed down to DNs, each DN automatically connects to the GTM to request next values. For example, in the statement INSERT INTO t1 SELECT xxx, a column of t1 needs to invoke the nextval function. Because the maximum number of connections on the GTM is 8192, and this type of pushdown statement consumes too many GTM connections, the maximum number of concurrent statements of this type is limited to 7,000 (other statements need to occupy some connections)/Number of cluster DNs.
Return type: bigint.
The nextval function can be invoked in either of the following ways: (In example 2, the Oracle syntax is supported. Currently, the sequence name cannot contain a dot.)
Example 1:
1 2 3 4 5 | SELECT nextval('seqDemo'); nextval --------- 2 (1 row) |
Example 2:
1 2 3 4 5 | SELECT seqDemo.nextval; nextval --------- 2 (1 row) |
currval(regclass)
Returns the last value of nextval for a specified sequence in the current session. If nextval has not been invoked for the specified sequence in the current session, an error is reported when currval is invoked. By default, currval is disabled. To enable it, set enable_beta_features to true. After currval is enabled, nextval will not be pushed down.
Return type: bigint.
The currval function can be invoked in either of the following ways: (In example 2, the Oracle syntax is supported. Currently, the sequence name cannot contain a dot.)
Example 1:
1 2 3 4 5 | SELECT currval('seq1'); currval --------- 2 (1 row) |
Example 2:
1 2 3 4 5 | SELECT seq1.currval seq1; currval --------- 2 (1 row) |
lastval()
Returns the last value of nextval in the current session. This function is equivalent to currval, but lastval does not have a parameter. If nextval has not been invoked in the current session, an error is reported when lastval is invoked.
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: bigint.
For example:
1 2 3 4 5 | SELECT lastval(); lastval --------- 2 (1 row) |
setval(regclass, bigint)
Sets the current value of a sequence.
Return type: bigint.
For example:
1 2 3 4 5 | SELECT setval('seqDemo',1); setval -------- 1 (1 row) |
setval(regclass, bigint, boolean)
Sets the current value of a sequence and the is_called sign.
Return type: bigint.
For example:
1 2 3 4 5 | SELECT setval('seqDemo',1,true); setval -------- 1 (1 row) |
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 use setval with caution.
Because the sequence is non-transactional, changes made by setval will not be canceled when a transaction rolled back.