Generated Row and History Identifiers for System-Versioned Tables

Summary

Add optional utility columns to system-versioned tables:

• ROW ID identifies a logical row and links its current and historical versions.
• HISTORY ID uniquely identifies each historical row version.

The columns are generated and maintained by the server. They address two separate requirements:

• Prevent duplicate-key conflicts when several historical versions have identical user-key and ROW END values.
• Provide an unambiguous relationship between a logical row and all of its versions.

Because these columns increase row size, index size, and DML overhead, they must be enabled explicitly for each table.

Rationale

MariaDB permits multiple versions of the same logical row in a system-versioned table by internally extending primary and unique keys with the ROW END column.

Concurrent operations can produce historical rows having identical:

• User-defined primary or unique-key values.
• ROW END values.

This may cause a duplicate-key error while creating the history row.

Skipping an otherwise identical history row is not an acceptable solution. A system-versioned table must be able to record every successful update, including updates that do not change user-visible column values.

A timestamp also does not uniquely identify a row version:

• Multiple changes may receive the same timestamp.
• A session can override its timestamp.
• Timestamp precision is finite.
• Timestamp-precise and transaction-precise versioning use different representations.
• Replicated or restored operations may reuse an existing version boundary.

A separate problem is identifying all versions belonging to the same logical row. User-defined keys cannot always provide this relationship because an UPDATE may change one or more key columns.

For example:

CREATE TABLE accounts (

  account_name VARCHAR(64) PRIMARY KEY,

  balance DECIMAL(12,2)

) WITH SYSTEM VERSIONING;

INSERT INTO accounts VALUES ('old-name', 100);

UPDATE accounts

SET account_name = 'new-name'

WHERE account_name = 'old-name';

The current and historical versions have different primary-key values. A stable server-generated row identifier would allow both versions to be associated with the same logical row.

Goals

The feature must provide:

• A stable identifier shared by every version of a logical row.
• A unique identifier for every historical version.
• Collision-free unique-key handling for guaranteed history.
• Correct operation when multiple history rows have equal versioning timestamps or transaction identifiers.
• Per-table opt-in configuration.
• Support for timestamp-precise and transaction-precise system versioning.
• Stable values across replication, backup, dump, and restore.

Proposed syntax

Allow explicitly declared generated utility columns:

CREATE TABLE t1 (

  id INT PRIMARY KEY,

  value VARCHAR(100),

  system_row_id BIGINT UNSIGNED

    GENERATED ALWAYS AS ROW ID INVISIBLE,

  system_history_id BIGINT UNSIGNED

    GENERATED ALWAYS AS HISTORY ID INVISIBLE

) WITH SYSTEM VERSIONING;

The grammar is:

column_name BIGINT UNSIGNED

    GENERATED ALWAYS AS ROW ID

    [VISIBLE | INVISIBLE]

column_name BIGINT UNSIGNED

    GENERATED ALWAYS AS HISTORY ID

    [VISIBLE | INVISIBLE]

The names system_row_id and system_history_id in the example are user-defined. Applications may choose other valid column names.

A table may define:

• Both utility columns.
• Only a ROW ID column.
• Only a HISTORY ID column.
• Neither column, preserving existing behavior.

Only one column of each utility type is permitted in a table.

ROW ID semantics

ROW ID identifies a logical row lineage.

Its properties are:

• The value is non-NULL.
• The value is generated when a new logical row is inserted.
• The value is unique among independently inserted logical rows.
• The value is copied unchanged to every historical version of that row.
• The value remains unchanged when user columns, including primary-key columns, are updated.
• The same value may occur in multiple physical records because all versions of one logical row share it.
• The value is not an SQL AUTO_INCREMENT column.
• Its allocator is independent of any user-defined AUTO_INCREMENT column.

The column is physically non-unique because multiple versions share the same value. Logically, a newly allocated value must not be reused for another row lineage.

HISTORY ID semantics

HISTORY ID identifies a specific historical row version.

Its properties are:

• The current row version contains NULL.
• Every historical row version contains a non-NULL value.
• Every generated non-NULL value is unique within the table.
• A new value is assigned whenever an update or delete creates a historical version.
• Different history rows must receive different values even when their user data, keys, ROW START, and ROW END values are identical.
• A value must not be reused after its history row is deleted.
• Allocation gaps caused by rollback or failed statements are allowed.
• Values provide identity, not commit ordering.

HISTORY ID must not be interpreted as:

• A transaction identifier.
• A timestamp.
• A GTID.
• A globally meaningful identifier outside the table.

Insert behavior

When a new logical row is inserted:

• A new ROW ID is generated.
• HISTORY ID is set to NULL.
• Normal user-defined defaults and constraints are evaluated as usual.

Example:

INSERT INTO t1 (id, value)

VALUES (1, 'initial');

SELECT id, value, system_row_id, system_history_id

FROM t1;

The result contains a generated system_row_id and a NULL system_history_id.

A second independently inserted logical row must receive a different ROW ID.

Update behavior

When a current row is updated:

1. The old current version is copied into history.
2. The history row retains the existing ROW ID.
3. A new unique HISTORY ID is assigned to the history row.
4. The new current version retains the same ROW ID.
5. The current version's HISTORY ID remains NULL.

Conceptually:

Before UPDATE:

ROW_ID = 100

HISTORY_ID = NULL

value = 'old'

After UPDATE:

Historical version:

  ROW_ID = 100

  HISTORY_ID = 500

  value = 'old'

Current version:

  ROW_ID = 100

  HISTORY_ID = NULL

  value = 'new'

Updating a primary or unique-key column must not change ROW ID.

Identical updates

An update that matches a row but does not change its user-visible values may still create a history row according to guaranteed-history behavior.

Each such history row must receive a distinct HISTORY ID.

For example:

CREATE TABLE t1 (

  id INT PRIMARY KEY,

  value INT,

  row_identifier BIGINT UNSIGNED

    GENERATED ALWAYS AS ROW ID INVISIBLE,

  version_identifier BIGINT UNSIGNED

    GENERATED ALWAYS AS HISTORY ID INVISIBLE

) WITH SYSTEM VERSIONING;

INSERT INTO t1 VALUES (1, 10);

UPDATE t1 SET value = 10 WHERE id = 1;

UPDATE t1 SET value = 10 WHERE id = 1;

If both updates produce history, the resulting historical rows must have:

• The same ROW ID.
• The same user-defined values.
• Potentially equal temporal boundaries.
• Different non-NULL HISTORY ID values.

Neither update may fail merely because an equivalent historical version already exists.

Delete behavior

When a current row is deleted:

• Its last current version becomes historical.
• The row retains its ROW ID.
• A new unique HISTORY ID is assigned.
• No current version with that ROW ID remains.

All historical versions of the deleted logical row remain discoverable using its ROW ID:

SELECT *

FROM t1 FOR SYSTEM_TIME ALL

WHERE system_row_id = 100

ORDER BY row_start;

REPLACE behavior

REPLACE logically removes a conflicting current row and inserts a new logical row.

The expected utility-column behavior is:

• The replaced row's final historical version retains its old ROW ID and receives a new HISTORY ID.
• The newly inserted row receives a new ROW ID.
• The new current row has a NULL HISTORY ID.

The old and new current rows represent different logical row lineages, even if they use the same user-defined primary key.

INSERT ON DUPLICATE KEY UPDATE

When INSERT ... ON DUPLICATE KEY UPDATE takes the update path:

• The operation is treated as an update of the existing logical row.
• The current row retains its ROW ID.
• The generated history row retains that ROW ID and receives a new HISTORY ID.

When it takes the insert path, normal new-row allocation rules apply.

Unique-key handling

When a system-versioned table has a HISTORY ID column, it must be used as the historical-version discriminator for primary and unique keys instead of relying only on ROW END.

The implementation must allow multiple history rows with identical:

• User-defined unique-key values.
• ROW END values.
• ROW START values, where applicable.

These rows remain distinguishable by their different HISTORY ID values.

Current-row uniqueness must remain unchanged.

Although HISTORY ID is SQL NULL for current rows, the server must still reject duplicate current values for every user-defined primary or unique key. The implementation must not accidentally apply ordinary nullable-unique-key semantics that permit multiple current rows with the same user key.

For example:

CREATE TABLE users (

  id INT PRIMARY KEY,

  email VARCHAR(255) UNIQUE,

  row_identifier BIGINT UNSIGNED

    GENERATED ALWAYS AS ROW ID INVISIBLE,

  history_identifier BIGINT UNSIGNED

    GENERATED ALWAYS AS HISTORY ID INVISIBLE

) WITH SYSTEM VERSIONING;

INSERT INTO users (id, email)

VALUES (1, 'user@example.test');

INSERT INTO users (id, email)

VALUES (2, 'user@example.test');

The second insert must fail with the normal duplicate-key error.

Historical versions containing the same email must remain valid when their non-NULL HISTORY ID values differ.

Index representation

SHOW CREATE TABLE must display the declared utility columns.

The physical extension of user-defined unique indexes may remain an implementation detail, consistent with existing system-versioned index handling.

Information Schema and metadata APIs must distinguish the new generated-column types from:

• Ordinary generated columns.
• ROW START.
• ROW END.
• User-defined AUTO_INCREMENT columns.

Index metadata must continue to expose user-defined key columns consistently with current system-versioned-table behavior.

Column visibility

Utility columns may be declared VISIBLE or INVISIBLE.

When invisible:

• They are not returned by SELECT *.
• They can be selected explicitly by their declared names.
• They are available in predicates, joins, ordering, grouping, and explicit indexes.
• They remain present in table metadata and SHOW CREATE TABLE.

Example:

SELECT id, value, system_row_id, system_history_id

FROM t1 FOR SYSTEM_TIME ALL

WHERE system_row_id = 100

ORDER BY system_history_id;

The simplified system-versioning syntax must not create utility columns automatically. Existing tables must not incur additional storage or processing overhead unless the feature is explicitly enabled.

Generated-column restrictions

Utility-column values are generated and maintained by the server.

Normal DML must not allow an application to assign arbitrary values:

INSERT INTO t1 (

  id,

  value,

  system_row_id,

  system_history_id

)

VALUES (1, 'value', 100, 500);

The statement must fail unless an explicit, privileged history-import or restore mode permits utility-column values to be supplied.

Assignments using DEFAULT may be accepted where consistent with other generated columns.

Utility columns must not support:

• A user-defined default expression.
• AUTO_INCREMENT.
• ON UPDATE.
• A generated-column expression.
• Changing their generated utility type with MODIFY COLUMN.
• More than one ROW ID or HISTORY ID per table.

Counter allocation

ROW ID and HISTORY ID require persistent per-table allocation.

The allocator must guarantee that:

• Concurrent transactions cannot receive the same identifier.
• Values remain unique after commit, rollback, restart, or crash recovery.
• A rolled-back allocation may leave a gap.
• Deleting rows or history does not make identifiers available for reuse.
• A normal DELETE does not reset either allocator.
• Allocation does not conflict with user-defined AUTO_INCREMENT columns.
• The allocator survives table rebuilds and supported online DDL operations.
• Restoring explicit values advances the corresponding high-water mark.

The allocator may use separate sequences for ROW ID and HISTORY ID, or a shared internal allocator, provided all externally visible semantics are preserved.

The maximum supported value and overflow behavior must be documented. Exhaustion must produce an error without partially executing the modifying statement.

Transactional behavior

Creation of a history row and assignment of its utility values must be part of the same transaction as the originating update or delete.

On rollback:

• The current row must retain its original state.
• No generated history row may remain.
• Allocated identifiers may remain unused.
• No identifier may later be reused if doing so could conflict with persisted or replicated data.

If history-row insertion fails, the originating DML statement must fail according to the normal atomicity rules of the storage engine.

Concurrent DML

Concurrent updates must be able to generate separate history rows even when their temporal metadata would otherwise collide.

The implementation must cover:

• Concurrent updates using the same effective timestamp.
• Concurrent no-op updates.
• Concurrent REPLACE statements.
• Concurrent updates of different logical rows having equal unique-key history.
• Lock waits and deadlock retries.
• Transaction rollback after identifier allocation.

Each successfully stored history row must have a distinct HISTORY ID.

Utility-column allocation must not weaken normal row-locking, isolation, or duplicate-current-key guarantees.

Timestamp-precise versioning

For timestamp-precise versioning, utility-column generation must be independent of timestamp precision.

Two history rows may have identical ROW END timestamps and still be stored because their HISTORY ID values differ.

Changing:

• timestamp session state.
• Session time zone.
• Timestamp precision.
• Supported maximum timestamp.

must not affect utility-column uniqueness.

Transaction-precise versioning

For transaction-precise versioning:

• ROW ID continues to identify a logical row lineage.
• HISTORY ID continues to identify a physical historical version.
• Utility identifiers must not be derived from transaction identifiers.
• Reuse or cleanup of transaction-registry information must not affect utility identifiers.
• Utility identifiers remain valid after the originating transaction is no longer present in the transaction registry.

ALTER TABLE

Utility columns may be added to or removed from a system-versioned table with ALTER TABLE, subject to normal system-versioned history-preservation rules.

Example:

ALTER TABLE t1

  ADD COLUMN system_row_id BIGINT UNSIGNED

    GENERATED ALWAYS AS ROW ID INVISIBLE,

  ADD COLUMN system_history_id BIGINT UNSIGNED

    GENERATED ALWAYS AS HISTORY ID INVISIBLE;

For a table with current rows but no history:

• A new ROW ID can be assigned to every current row.
• Every current HISTORY ID is set to NULL.

For a table that already contains history, assigning correct ROW ID values may be ambiguous because user-defined keys can change between versions.

The server must not silently infer incorrect row lineages.

Enabling ROW ID on a table containing existing history must therefore do one of the following:

• Perform a provably correct lineage reconstruction.
• Require utility values to be supplied through an explicit import operation.
• Reject the operation with an error explaining that existing history cannot be linked automatically.

Adding only HISTORY ID to existing history may assign a unique value to each existing historical row and NULL to current rows during a table rebuild.

Dropping HISTORY ID must restore the normal ROW END-based unique-key representation.

Dropping ROW ID removes lineage information but must not alter user data or temporal periods.

All transformations must be atomic.

Table-copy operations

Utility-column definitions and values must be handled correctly by:

• CREATE TABLE ... LIKE.
• CREATE TABLE ... SELECT.
• ALTER TABLE ... ALGORITHM=COPY.
• Partition conversion and exchange operations.
• Storage-engine conversion.
• Table rebuild and optimization operations.

CREATE TABLE ... LIKE must copy the utility-column definitions but not allocator state associated with existing rows.

A successful table rebuild must preserve existing values and advance allocator state beyond every preserved explicit value.

Operations that copy only user-visible data should generate new identifiers unless they explicitly request preservation of system-versioning metadata.

Partitioning

Utility identifiers are table-wide, not partition-local.

The same identifier must not be allocated independently in different partitions.

Partition operations must preserve utility values during:

• REORGANIZE PARTITION.
• CONVERT PARTITION ... TO TABLE.
• CONVERT TABLE ... TO PARTITION.
• EXCHANGE PARTITION.
• Partition rebuilds.
• Automatic SYSTEM_TIME history-partition creation.

Attaching data containing utility identifiers must validate allocator state and identifier conflicts before completing the operation.

Replication

Utility-column values are persistent logical data and must be identical on the primary and replica.

Replication must preserve:

• ROW ID values.
• HISTORY ID values.
• NULL HISTORY ID values for current rows.
• Allocator high-water marks.
• Row-lineage relationships.

Row-based replication must include or reconstruct the generated identifiers without allocating different values on the replica.

Statement-based replication must not produce different identifiers because of concurrency, execution order, session timestamp, or allocator state. If exact reproduction cannot be guaranteed for a statement, the server must use the established unsafe-statement handling rather than silently diverging.

Retrying a replicated statement must not produce duplicate history or allocate a different identifier for an already applied row event.

Mixed-format replication must produce the same logical and physical utility values as row-based replication.

Galera and multi-primary operation

Concurrent writes originating on different cluster nodes must not allocate conflicting utility identifiers.

The implementation must either:

• Integrate utility-column allocation with a cluster-safe allocation mechanism.
• Allocate disjoint value ranges.
• Certify conflicting allocations.
• Reject unsupported configurations with a clear error.

Applying a replicated writeset must preserve the identifiers allocated by its originating node.

Cluster state transfer must preserve both stored values and allocator high-water marks.

Backup and restore

Physical backup and restore must preserve:

• All utility-column values.
• Column definitions.
• Internal allocator state.

Logical dump must preserve enough information to reproduce row lineages and history identities.

A complete history dump must include:

• ROW ID for every current and historical version.
• HISTORY ID for every historical version.
• NULL HISTORY ID for current versions.
• The utility-column definitions in CREATE TABLE.
• A controlled restore mechanism for explicit generated values.

After logical restore, newly allocated values must be greater than, or otherwise guaranteed not to conflict with, all restored values.

Restoring duplicate non-NULL HISTORY ID values must fail.

Restoring the same non-NULL ROW ID for unrelated row lineages must either fail when detectable or require an explicit trusted restore mode.

A current-data-only dump may generate new utility identifiers only if this behavior is explicitly selected and documented. A history-preserving dump must retain the original values.

History import

The existing privileged mechanism for inserting historical data must be extended to support utility columns.

During an explicit history import:

• Preserving supplied ROW ID and HISTORY ID values must be possible.
• Supplied non-NULL HISTORY ID values must be validated for uniqueness.
• Imported current rows must have NULL HISTORY ID.
• Allocator high-water marks must be advanced.
• Ordinary non-privileged DML must remain unable to override generated values.

Importing history without supplied utility values may generate new values only when the requested import mode explicitly permits it.

Storage engines

The server and storage engine must agree on utility-column generation, storage, indexing, and recovery.

A storage engine supporting system versioning with utility columns must provide:

• Persistent storage for both columns.
• Atomic identifier generation or server-provided generated values.
• Correct unique-key treatment.
• Crash-safe allocator state.
• Table-wide allocation for partitioned tables.
• Correct replication and recovery behavior.

If a storage engine supports system versioning but not utility columns, creating or altering a table to include them must fail with an appropriate unsupported-feature error.

The server must not silently ignore the declarations.

Information Schema

Utility columns must be represented in metadata.

information_schema.COLUMNS should expose:

• The declared column name.
• The physical SQL type.
• Nullability.
• Visibility.
• A generated-column classification identifying ROW ID or HISTORY ID.

Metadata must allow clients to distinguish the utility columns from ordinary generated columns and period columns.

Where appropriate, table metadata should indicate that utility-column support is enabled.

Compatibility

Existing system-versioned tables without utility columns must preserve their current behavior and physical representation.

The feature must not change:

• Existing ROW START and ROW END semantics.
• Existing temporal query syntax.
• Existing tables unless they are explicitly altered.
• Current-row visibility.
• History retention and DELETE HISTORY behavior.
• User-defined AUTO_INCREMENT behavior.
• Application-time periods.
• Non-versioned tables.

Applications that do not select the new columns must receive the same result shape as before when the columns are invisible.

Tables using utility columns must remain system-versioned tables and support all existing FOR SYSTEM_TIME forms.

Error handling

The server must reject:

• More than one ROW ID column.
• More than one HISTORY ID column.
• A utility column on a non-system-versioned table.
• An unsupported utility-column data type.
• A nullable ROW ID declaration.
• A non-nullable HISTORY ID declaration if current rows require NULL.
• A default expression on a utility column.
• AUTO_INCREMENT on a utility column.
• Direct assignment outside an authorized restore mode.
• Duplicate restored HISTORY ID values.
• Identifier overflow.
• Unsupported storage engines.
• An unsafe attempt to infer existing row lineages.
• Alter operations that would lose or corrupt utility identifiers.

Errors must leave table data, indexes, history, and allocator state unchanged.

Acceptance criteria

The task is complete when all of the following are satisfied:

• A table can explicitly define ROW ID and HISTORY ID columns.
• Each new logical row receives a non-NULL ROW ID.
• Independently inserted rows receive different ROW ID values.
• All versions of one logical row retain the same ROW ID.
• Updating primary or unique-key columns does not change ROW ID.
• Current rows have a NULL HISTORY ID.
• Every generated history row has a non-NULL unique HISTORY ID.
• Concurrent history rows with equal user keys and equal ROW END values do not conflict.
• Repeated identical updates can generate distinct history rows.
• User-defined uniqueness remains enforced for current rows.
• Normal DML cannot assign utility-column values.
• Utility columns do not interfere with a user AUTO_INCREMENT column.
• Values survive restart and crash recovery.
• Allocated values are not reused after rollback or history deletion.
• Timestamp-precise versioning is supported.
• Transaction-precise versioning is supported.
• Partitioned tables use table-wide identifiers.
• Row-based and statement-based replication preserve values.
• Galera operation cannot create conflicting values.
• Physical backup preserves values and allocator state.
• History-aware logical dump and restore preserve values.
• Restoring explicit values advances allocator state.
• Existing tables without utility columns remain unchanged.
• SHOW CREATE TABLE and Information Schema expose the declarations.
• Invisible utility columns are omitted from SELECT *.
• Explicit selection of utility columns works with temporal queries.

Test coverage

Add tests covering:

• Table creation with both utility columns.
• Table creation with only ROW ID.
• Table creation with only HISTORY ID.
• Table creation without utility columns.
• Explicit visible utility columns.
• Explicit invisible utility columns.
• Duplicate declarations.
• Invalid data types and attributes.
• Insert of one row.
• Insert of multiple rows.
• Multi-row insert.
• Update of a non-key column.
• Update of a primary-key column.
• Update of a unique-key column.
• Multiple updates of one row.
• Identical no-op updates.
• Delete after update.
• Delete without a preceding update.
• REPLACE.
• INSERT ... ON DUPLICATE KEY UPDATE.
• Multi-table update.
• Multi-table delete.
• Trigger-generated DML.
• Foreign-key cascades.
• Concurrent updates using the same timestamp.
• Concurrent updates from multiple sessions.
• Deadlock and retry.
• Lock-wait timeout.
• Statement rollback after allocation.
• Transaction rollback after several allocations.
• Server restart after committed allocation.
• Injected crash during history insertion.
• Allocator overflow.
• User-defined AUTO_INCREMENT in the same table.
• Multiple user-defined unique keys.
• Duplicate current-key rejection with NULL HISTORY ID.
• Historical duplicates differing only by HISTORY ID.
• Timestamp-precise tables.
• Transaction-precise tables.
• Explicit and implicit period columns.
• FOR SYSTEM_TIME ALL queries by ROW ID.
• FOR SYSTEM_TIME AS OF with explicit utility-column projection.
• DELETE HISTORY followed by additional DML.
• ALTER adding utility columns to an empty table.
• ALTER adding utility columns to a current-data-only table.
• ALTER on a table containing history.
• ALTER dropping utility columns.
• Copy-based ALTER.
• Storage-engine conversion.
• CREATE TABLE ... LIKE.
• Partitioned tables.
• Partition reorganizing and conversion.
• Statement-based replication.
• Row-based replication.
• Mixed-format replication.
• Replication retry.
• Galera concurrent allocation.
• Physical backup and restore.
• Current-data-only logical dump.
• Full-history logical dump and restore.
• Explicit history import.
• Duplicate identifiers during import.
• Allocator advancement after import.
• Information Schema metadata.
• SHOW CREATE TABLE.
• Backward compatibility for existing system-versioned tables.

Documentation

Update the system-versioned-table, CREATE TABLE, ALTER TABLE, replication, and backup documentation.

Document:

• The purpose of ROW ID and HISTORY ID.
• Their declaration syntax.
• Their generated values for current and historical rows.
• The distinction between logical row identity and history-version identity.
• That utility identifiers are independent of timestamps and transaction IDs.
• Their effect on unique-key handling.
• Their visibility rules.
• Per-table storage and performance overhead.
• Counter allocation, gaps, persistence, and overflow.
• ALTER TABLE limitations for existing history.
• Replication and Galera behavior.
• Logical dump and restore requirements.
• History-import behavior.
• Storage-engine restrictions.
• Examples for querying the complete lineage of a logical row.