Conflict prevention with an MMR-ready sequence v7

To prevent uniqueness conflicts in a multi-master replication system, you can use an MMR-ready sequence to generate unique identifiers for each row of publication tables that doesn't have an inherent, unique identifier.

An MMR-ready sequence incorporates a function and a sequence to return BIGINT data type, integer values. These values combine a user-assigned, unique database identifier for each primary node with a sequence generated within that primary node.

You can modify a publication table requiring an MMR-ready sequence to include a BIGINT NOT NULL column with a default value returned by the function.

An MMR-ready sequence satisfies the following characteristics:

• Uniqueness. The combination of the unique database identifier with the sequence ensures that each row in a given table has a unique value across all primary nodes.
• Clustered index support. An MMR-ready sequence doesn't impair the use of a clustered index to provide retrieval efficiency. MMR-ready sequence values are returned in a typical, ordered sequence, not as random values such as if the universally unique identifier (UUID) were used.
• Effective migration support. Yuo can modify tables already using a sequence to use an MMR-ready sequence with minimal impact on existing primary keys and foreign keys.
• Reliability and maintainability. An MMR-ready sequence provides a reliable and maintainable method to avoid uniqueness conflicts.