Creating an MMR-ready sequence v7
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.
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.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.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 theDEFAULT
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.(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 theMMR_sequence_currval
function.Add or modify the publication table column that uses the MMR-ready sequence. The column data type must be
BIGINT
. TheMMR_sequence_nextval
function is specified in theDEFAULT
clause as shown in the following example for columnid
.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.
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.
Create the complete, multi-master replication system as described in Multi-master replication operation.