MMR-ready sequence example v7

Suggest edits

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;

Output

        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;

Output

        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;

Output

        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)

← Prev

Creating an MMR-ready sequence

Converting a standard sequence to an MMR-ready sequence

MMR-ready sequence example v7

Note

Note

Note

← Prev

Next →