Understand sequence limits and troubleshoot primary key conflicts - PolarDB

This topic describes the limits on sequences and how to troubleshoot primary key conflicts.

Sequence type conversion

When converting a sequence from one type to another, the following constraints apply:

Specify START WITH when converting a sequence to a different type.
A GROUP sequence that contains multiple units cannot be converted to another type. Other sequence types also cannot be converted to a GROUP sequence that contains multiple units.
For a GROUP sequence with multiple units, only the START WITH value can be changed. All other parameters are immutable.
All unit sequences that belong to the same GROUP sequence must use the same UNIT COUNT value and different INDEX values.

INSERT statement behavior

In the following scenarios, PolarDB-X 1.0 bypasses the sequence and falls back to the ApsaraDB RDS auto-increment column to generate IDs:

Non-sharded or broadcast-table-free databases: If the INSERT statement runs in a non-sharded PolarDB-X 1.0 database, or in a sharded database that has only non-sharded tables and no broadcast tables, PolarDB-X 1.0 automatically optimizes and routes the statement without using the sequence allocator. In this case, INSERT INTO ... VALUES (seq.nextval, ...) is not supported. Use the auto-increment column provided by ApsaraDB RDS instead.
Shard-hint INSERT statements: If an INSERT statement (INSERT INTO ... VALUES ... or INSERT INTO ... SELECT ...) contains a hint that specifies a database shard, PolarDB-X 1.0 routes the statement directly to that shard, bypassing the query optimizer. The sequence does not take effect, and the ApsaraDB RDS auto-increment column generates the IDs.

Auto-increment ID generation

PolarDB-X 1.0 supports two methods for generating auto-increment IDs: sequences and the ApsaraDB RDS auto-increment column.

Warning

Do not mix both methods in the same table. If one row is inserted using an explicit sequence value and another row is inserted using the ApsaraDB RDS auto-increment column, the two ID ranges can overlap and produce duplicate IDs that are difficult to detect. Use the same method consistently throughout a table.

Additional constraints:

For a TIME (time-based) sequence used in an auto-increment column, the column's data type must be BIGINT.
Do not manually call NEXTVAL on a GROUP sequence prefixed with AUTO_SEQ_ and then insert the result as an explicit value using INSERT. Each manual NEXTVAL call causes the sequence to frequently refresh its cache, causing the sequence value to grow much faster than expected. Use the auto-increment column feature instead.

Fix primary key conflicts

When this happens: A data record is written directly to ApsaraDB RDS with a primary key value that PolarDB-X 1.0 did not generate. The next auto-generated ID from PolarDB-X 1.0 can collide with that value, resulting in a duplicate primary key error on INSERT.

Fix: Find the current maximum primary key value in the affected table and advance the sequence past it.

List the existing sequences to identify the one associated with the affected table.

SHOW SEQUENCES;

The output is similar to:

+---------------------+-------+--------------+------------+-----------+-------+-------+
| 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)

Sequences prefixed with AUTO_SEQ_ are implicit sequences, created automatically when a table is defined with AUTO_INCREMENT.

Find the current maximum value in the primary key column of the affected table. In this example, the table is xkv_t_item and the primary key column is id.
```
SELECT MAX(id) FROM xkv_t_item;
```
The output is similar to:
```
+-----------+
| MAX(id)   |
+-----------+
| 8231      |
+-----------+
1 row in set (0.01 sec)
```
Advance the sequence start value to a number greater than the current maximum. In this example, the current maximum is 8231, so a safe value is 9000.
```
ALTER SEQUENCE AUTO_SEQ_xkv_t_item START WITH 9000;
```
After this change, new auto-increment IDs generated by PolarDB-X 1.0 start from 9000 and no longer conflict with existing data.