MMR-ready sequence example v7
The following is an example of a three-primary node system using an MMR-ready sequence. The databases to use as the primary nodes are MMRnode_a
, MMRnode_b
, and MMRnode_c
. A publication table named MMR_seq_tbl
uses the MMR-ready
sequence.
The following commands are invoked in database MMRnode_a
, which is the primary definition node:
ALTER DATABASE MMRnode_a SET cluster.unique_db_id TO 1; SET cluster.unique_db_id TO 1; CREATE SEQUENCE MMR_seq START WITH 1 INCREMENT BY 1 NO CYCLE; 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$; 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$; CREATE TABLE MMR_seq_tbl ( id BIGINT NOT NULL PRIMARY KEY DEFAULT MMR_sequence_nextval('MMR_seq'), field VARCHAR2(20) );
On MMRnode_b
and MMRnode_c
, run the commands to create different settings for the configuration parameter cluster.unique_db_id
are run and the commands to create the sequence and the functions.
On MMRnode_b
, invoke the following commands.
Note
Cluster.unique_db_id
is set to 2
.
ALTER DATABASE MMRnode_b SET cluster.unique_db_id TO 2; SET cluster.unique_db_id TO 2; CREATE SEQUENCE MMR_seq START WITH 1 INCREMENT BY 1 NO CYCLE; 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$; 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$;
On MMRnode_c
, invoke the following commands.
Note
The cluster.unique_db_id
is set to 3.
ALTER DATABASE MMRnode_c SET cluster.unique_db_id TO 3; SET cluster.unique_db_id TO 3; CREATE SEQUENCE MMR_seq START WITH 1 INCREMENT BY 1 NO CYCLE; 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$; 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$;
Create the multi-master replication system with the Replicate Publication Schema and the Perform Initial Snapshot options selected when creating the additional primary nodes MMRnode_b
and MMRnode_c
.
The resulting primary nodes are shown in the Replication Server console.
Note
The Default Value
property of the id
column uses the MMR_sequence_nextval
function.
Invoke the following INSERT
commands on MMRnode_a
:
INSERT INTO MMR_seq_tbl (field) VALUES ('MMRnode_a - Row 1'); INSERT INTO MMR_seq_tbl (field) VALUES ('MMRnode_a - Row 2'); INSERT INTO MMR_seq_tbl (field) VALUES ('MMRnode_a - Row 3');
Invoke the following INSERT
on MMRnode_b
:
INSERT INTO MMR_seq_tbl (field) VALUES ('MMRnode_b - Row 1'); INSERT INTO MMR_seq_tbl (field) VALUES ('MMRnode_b - Row 2'); INSERT INTO MMR_seq_tbl (field) VALUES ('MMRnode_b - Row 3');
Invoke the following INSERT
on MMRnode_c
:
INSERT INTO MMR_seq_tbl (field) VALUES ('MMRnode_c - Row 1'); INSERT INTO MMR_seq_tbl (field) VALUES ('MMRnode_c - Row 2'); INSERT INTO MMR_seq_tbl (field) VALUES ('MMRnode_c - Row 3');
A synchronization replication is performed.
No uniqueness conflicts occur. A unique value is generated for the id
primary key column as shown by the following results on MMRnode_a
:
MMRnode_a=# SELECT * FROM MMR_seq_tbl ORDER BY id;
id | field -------------------+------------------- 4503599627370497 | MMRnode_a - Row 1 4503599627370498 | MMRnode_a - Row 2 4503599627370499 | MMRnode_a - Row 3 9007199254740993 | MMRnode_b - Row 1 9007199254740994 | MMRnode_b - Row 2 9007199254740995 | MMRnode_b - Row 3 13510798882111489 | MMRnode_c - Row 1 13510798882111490 | MMRnode_c - Row 2 13510798882111491 | MMRnode_c - Row 3 (9 rows)
The same query on MMRnode_b
shows the same set of rows:
MMRnode_b=# SELECT * FROM MMR_seq_tbl ORDER BY id;
id | field -------------------+------------------- 4503599627370497 | MMRnode_a - Row 1 4503599627370498 | MMRnode_a - Row 2 4503599627370499 | MMRnode_a - Row 3 9007199254740993 | MMRnode_b - Row 1 9007199254740994 | MMRnode_b - Row 2 9007199254740995 | MMRnode_b - Row 3 13510798882111489 | MMRnode_c - Row 1 13510798882111490 | MMRnode_c - Row 2 13510798882111491 | MMRnode_c - Row 3 (9 rows)
The same results are present on MMRnode_c
:
MMRnode_c=# SELECT * FROM MMR_seq_tbl ORDER BY id;
id | field -------------------+------------------- 4503599627370497 | MMRnode_a - Row 1 4503599627370498 | MMRnode_a - Row 2 4503599627370499 | MMRnode_a - Row 3 9007199254740993 | MMRnode_b - Row 1 9007199254740994 | MMRnode_b - Row 2 9007199254740995 | MMRnode_b - Row 3 13510798882111489 | MMRnode_c - Row 1 13510798882111490 | MMRnode_c - Row 2 13510798882111491 | MMRnode_c - Row 3 (9 rows)