Creating an MMR-ready sequence v7

Suggest edits

The following are the steps to create an MMR-ready sequence in a database to participate as a primary node in a multi-master replication system.

Begin these steps with the database to use as the primary definition node.

  1. Assign a unique, database identifier as an integer from 1 to 1024, inclusive. You can uniquely identify a maximum of 1024 databases in a multi-master replication system with an MMR-ready sequence.

    Issue the following commands to create and set the database identifier:

    ALTER DATABASE dbname SET cluster.unique_db_id TO db_id;
SET cluster.unique_db_id TO <db_id>;

    Use a different <db_id> value for each database.

  2. Create a sequence to uniquely identify each table row in the database.

    CREATE SEQUENCE seq_name START WITH 1 INCREMENT BY 1 NO CYCLE;

    You can create multiple sequences if you want to use separate sequences for multiple tables in the publication. Be sure to use the same sequence name across all databases for the same given table.

    A publication table column that uses an MMR-ready sequence includes a DEFAULT clause referencing the sequence name in a function call. The publication table definition must be consistent across all primary nodes by referencing the same sequence name in the function call.

  3. Create the following function that returns the next MMR-ready sequence value when a row is inserted into the table. This function is referenced by the DEFAULT clause of the publication table column.

    CREATE OR REPLACE FUNCTION MMR_sequence_nextval (
    seq_id          VARCHAR
)
RETURNS bigint
LANGUAGE sql
AS
$function$
SELECT (
 (SELECT current_setting('cluster.unique_db_id'))::bigint
  << 52)::bigint +
  nextval($1::regclass);
$function$;

    The sequence name created in Step 2 is specified as the seq_id input argument when the function is added to the DEFAULT clause of the publication table column.

    This function performs a bitwise shift left operation (<< 52) on the database identifier (cluster.unique_db_id), thus significantly increasing its numeric value. The next sequence value is then added to this number. Thus, all rows inserted in the table on a given database fall in a numeric range determined by the shifted, database identifier value.

  4. (Optional) Create the following function to obtain the current MMR-ready sequence value.

    CREATE OR REPLACE FUNCTION MMR_sequence_currval (
    seq_id          VARCHAR
)
RETURNS bigint
LANGUAGE sql
AS
$function$
SELECT (
 (SELECT current_setting('cluster.unique_db_id'))::bigint
  << 52)::bigint +
  currval($1::regclass);
$function$;

    Invoke the MMR_sequence_nextval function in the current session before calling the MMR_sequence_currval function.

  5. Add or modify the publication table column that uses the MMR-ready sequence. The column data type must be BIGINT. The MMR_sequence_nextval function is specified in the DEFAULT clause as shown in the following example for column id.

    CREATE TABLE table_name (
    id          BIGINT NOT NULL PRIMARY KEY
                DEFAULT MMR_sequence_nextval('seq_name'),
    field       VARCHAR2(20)
);

    The column also typically is the primary key.

  6. Repeat steps 1 through 4 for the other databases to add as primary nodes.

    Note

    Omit Step 5 for the additional primary nodes. Publication table definitions are replicated from the primary definition node to the additional primary nodes when they are created. See Creating additional primary nodes.

  7. Create the complete, multi-master replication system as described in Multi-master replication operation.

← Prev

Conflict prevention with an MMR-ready sequence

Next →

MMR-ready sequence example