PolarDB:Limits

Limits and usage notes

When you use a sequence, take note of the following items:

• You must specify the START WITH parameter when you convert a sequence from one type to a different type.
• A GROUP sequence that contains multiple units cannot be converted to a sequence of another type, and other types of sequences cannot be converted to GROUP sequences that contain multiple units. You can change only the value of the START WITH parameter for a GROUP sequence that contains multiple units. The values of other parameters that are used to define the sequence cannot be changed.
• In the definitions of unit sequences that belong to the same GROUP sequence, the values of the UNIT COUNT parameter must be the same, and the values of the INDEX parameter must be different.
• If the INSERT statement is executed in a non-sharded PolarDB-X 1.0 database or a sharded database that has only non-sharded tables but no broadcast tables, PolarDB-X 1.0 automatically optimizes and sends the statement. This way, you do not need to use an optimizer to allocate sequence values. In this case, the INSERT INTO ... VALUES (seq.nextval, ...) statement is not supported. We recommend that you use the auto-increment column feature provided by ApsaraDB RDS instead.
• If a hint that is used to specify database shards is contained in an INSERT statement such as INSERT INTO ... VALUES ... or INSERT INTO... SELECT ..., and the target table for which the INSERT statement is executed uses a sequence, PolarDB-X 1.0 bypasses the optimizer and directly sends the statement to be executed. In this case, the sequence does not take effect and the auto-increment column in the table that is stored in ApsaraDB RDS is used to generate IDs in the target table.
• You can use a sequence or the auto-increment feature provided by ApsaraDB RDS to generate auto-increment IDs. Make sure that you use the same method to generate auto-increment IDs in the same table. If both methods are used in the same table, duplicate IDs that are difficult to identify are generated.
• If you want to use a time-based (TIME) sequence to generate values for an auto-increment column of a table, the data type of the column must be BIGINT.
• We recommend that you do not use a GROUP sequence prefixed with AUTO_SEQ_ that is automatically associated with the table when the table is created to obtain NEXTVAL and insert the obtained value into the table as an explicit value by using INSERT. If you do so, the GROUP sequence may frequently refresh the cache and cause the value to grow too fast.

Troubleshoot primary key conflicts

If a data record is directly written to ApsaraDB RDS and the corresponding primary key value is not a sequence value generated by PolarDB-X 1.0, the primary key value that is automatically generated by PolarDB-X 1.0 may conflict with the primary key value that corresponds to the data record. To solve this issue, perform the following steps:

1. Execute the SHOW SEQUENCES statement to query the existing sequences. Sequences whose names are prefixed with AUTO_SEQ_ are implicit sequences. To create an implicit sequence, you must specify the AUTO_INCREMENT parameter in the statement that is executed to create a table.

   Execute the following statement in your CLI:

   SHOW SEQUENCES;

   The following results are returned:

   +---------------------+-------+--------------+------------+-----------+-------+-------+ 
   | NAME                | VALUE | INCREMENT_BY | START_WITH | MAX_VALUE | CYCLE | TYPE  | 
   +---------------------+-------+--------------+------------+-----------+-------+-------+ 
   | AUTO_SEQ_xkv_t_item | 0     | N/A          | N/A        | N/A       | N/A   | GROUP | 
   | AUTO_SEQ_xkv_shard  | 0     | N/A          | N/A        | N/A       | N/A   | GROUP | 
   +---------------------+-------+--------------+------------+-----------+-------+-------+ 
   2 rows in set (0.04 sec)

2. Query the existing maximum value in the primary key column of the PolarDB-X 1.0 table. In this example, the primary key column of the xkv_t_item table is id, and the table contains duplicate primary key values. You can execute the following statement to query the existing maximum value in the id column:

   Execute the following statement in your CLI:

   SELECT MAX(id) FROM xkv_t_item;

   The following results are returned:

   +-----------+ 
   | MAX(id)   | 
   +-----------+ 
   | 8231      | 
   +-----------+ 
   1 row in set (0.01 sec)

3. Change the maximum sequence value in the table to a value greater than 8231, such as 9000. After the value is changed, no error is returned for auto-increment primary keys that are generated when you execute the INSERT statement.

   Execute the following statement in your CLI:

   ALTER SEQUENCE AUTO_SEQ_xkv_t_item START WITH 9000;