Conversion to an MMR-ready sequence example v7

Suggest edits

This example shows how you convert two tables with standard sequences used as primary keys and a parent-child relationship by a foreign key constraint to use the MMR-ready sequence, You can then use them in a multi-master replication system.

The tables are defined as follows:

CREATE TABLE MMR_seq_tbl (
    id          SERIAL PRIMARY KEY,
    field       VARCHAR2(20)
);

CREATE TABLE MMR_seq_child_tbl (
    id          SERIAL PRIMARY KEY,
    field       VARCHAR2(20),
    parent_id   INTEGER CONSTRAINT MMR_seq_tbl_fk
                REFERENCES MMR_seq_tbl(id)
);
Note

Observe the foreign key constraint between columns MMR_seq_child_tbl.parent_id and MMR_seq_tbl.id.

The tables are populated with an initial set of rows:

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');

INSERT INTO MMR_seq_child_tbl (field, parent_id) VALUES ('MMRnode_a - Row 1-1', 1);
INSERT INTO MMR_seq_child_tbl (field, parent_id) VALUES ('MMRnode_a - Row 1-2', 1);
INSERT INTO MMR_seq_child_tbl (field, parent_id) VALUES ('MMRnode_a - Row 2-1', 2);
INSERT INTO MMR_seq_child_tbl (field, parent_id) VALUES ('MMRnode_a - Row 2-2', 2);
INSERT INTO MMR_seq_child_tbl (field, parent_id) VALUES ('MMRnode_a - Row 3-1', 3);
INSERT INTO MMR_seq_child_tbl (field, parent_id) VALUES ('MMRnode_a - Row 3-2', 3);

The resulting table content is the following:

edb=# SELECT * FROM MMR_seq_tbl;
Output
 id |       field
----+-------------------
  1 | MMRnode_a - Row 1
  2 | MMRnode_a - Row 2
  3 | MMRnode_a - Row 3
(3 rows)
edb=# SELECT * FROM MMR_seq_child_tbl;
Output
 id |        field        | parent_id
----+---------------------+-----------
  1 | MMRnode_a - Row 1-1 |         1
  2 | MMRnode_a - Row 1-2 |         1
  3 | MMRnode_a - Row 2-1 |         2
  4 | MMRnode_a - Row 2-2 |         2
  5 | MMRnode_a - Row 3-1 |         3
  6 | MMRnode_a - Row 3-2 |         3
(6 rows)

Prior to performing the conversion, obtain the current, maximum sequence value of the sequence to convert to an MMR-ready sequence. In this example, the value is 6, as seen in the id column of table MMR_seq_child_tbl.

This value is needed to set a newly created sequence to use for the MMR-ready sequence. This value must be a large enough starting value to avoid auniqu eness conflict with the converted sequence values of the existing rows.

Converting existing standard sequence values

To convert the existing sequence values in columns MMR_seq_tbl.id, MMR_seq_child_tbl.id, and MMR_seq_child_tbl.parent_id:

Permit deferred updates to the foreign key constraint.

ALTER TABLE MMR_seq_child_tbl ALTER CONSTRAINT MMR_seq_tbl_fk DEFERRABLE INITIALLY DEFERRED;

Create the function to perform the sequence conversion.

CREATE OR REPLACE FUNCTION MMR_sequence_convert (
    old_seq_value   bigint
)
RETURNS bigint
LANGUAGE sql
AS
$function$
SELECT (
 (SELECT current_setting('cluster.unique_db_id'))::bigint
  << 52)::bigint + $1;
$function$;

Change the sequence columns to data type BIGINT so they are large enough for the MMR-ready sequence.

ALTER TABLE MMR_seq_tbl ALTER COLUMN id SET DATA TYPE BIGINT;
ALTER TABLE MMR_seq_child_tbl ALTER COLUMN id SET DATA TYPE BIGINT;
ALTER TABLE MMR_seq_child_tbl ALTER COLUMN parent_id SET DATA TYPE BIGINT;

Set the unique database identifier used by the MMR-ready sequence.

ALTER DATABASE MMRnode_a SET cluster.unique_db_id TO 1;
SET cluster.unique_db_id TO 1;

Update the primary key and foreign key values with the MMR_sequence_convert function. Perform the updates affecting the foreign key constraint in the same transaction to avoid a foreign key violation error.

BEGIN TRANSACTION;
    UPDATE MMR_seq_tbl SET id = MMR_sequence_convert (id);
    UPDATE MMR_seq_child_tbl SET parent_id = MMR_sequence_convert (parent_id);
    UPDATE MMR_seq_child_tbl SET id = MMR_sequence_convert (id);
COMMIT;

Reset the foreign key constraint back to its original setting. For example:

ALTER TABLE MMR_seq_child_tbl ALTER CONSTRAINT MMR_seq_tbl_fk NOT DEFERRABLE;

After the conversion to the MMR-ready sequence, the table content is as follows:

edb=# SELECT * FROM MMR_seq_tbl;
        id        |       field
------------------+-------------------
 4503599627370497 | MMRnode_a - Row 1
 4503599627370498 | MMRnode_a - Row 2
 4503599627370499 | MMRnode_a - Row 3
(3 rows)

edb=# SELECT * FROM MMR_seq_child_tbl;
        id        |        field        |    parent_id
------------------+---------------------+------------------
 4503599627370497 | MMRnode_a - Row 1-1 | 4503599627370497
 4503599627370498 | MMRnode_a - Row 1-2 | 4503599627370497
 4503599627370499 | MMRnode_a - Row 2-1 | 4503599627370498
 4503599627370500 | MMRnode_a - Row 2-2 | 4503599627370498
 4503599627370501 | MMRnode_a - Row 3-1 | 4503599627370499
 4503599627370502 | MMRnode_a - Row 3-2 | 4503599627370499
(6 rows)

The parent-child foreign key relationship between columns MMR_seq_child_tbl.parent_id and MMR_seq_tbl.id is maintained.

The primary key id values incorporate the old sequence values but are increased by the addition of the 52-bit shifted, database identifier value.

Setting up the MMR-ready sequence

Perform the steps described in Creating an MMR-ready sequence on the databases to use as primary nodes. For database MMRnode_a that contains the converted tables, create a new sequence with a starting value of 7 to avoid a primary key uniqueness conflict with the existing rows. In the original tables, the maximum used sequence value was 6. When a sequence number is transformed to an MMR-ready sequence value, the same result is returned if the same database identifier is used with the same original number.

CREATE SEQUENCE MMR_seq START WITH 7 INCREMENT BY 1 NO CYCLE;

Create the function to return the MMR-ready sequence value.

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$;

Modify the primary key columns to use the function to return the default value.

ALTER TABLE MMR_seq_tbl ALTER COLUMN id SET DEFAULT MMR_sequence_nextval('MMR_seq');
ALTER TABLE MMR_seq_child_tbl ALTER COLUMN id SET DEFAULT MMR_sequence_nextval('MMR_seq');

Repeat the MMR-ready sequence setup process for the other primary nodes.

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$;

Repeat the process for MMRnode_c.

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$;

Tables after initial multi-master replication system creation

Create the multi-master replication system using databases MMRnode_a, MMRnode_b, and MMRnode_c in a similar manner as described in MMR-ready sequence example.

After you create the system with the initial snapshot, MMRnode_a, MMRnode_b, and MMRnode_c all contain identical content. The following is the table content:

MMRnode_a=# SELECT * FROM MMR_seq_tbl;
        id        |       field
------------------+-------------------
 4503599627370497 | MMRnode_a - Row 1
 4503599627370498 | MMRnode_a - Row 2
 4503599627370499 | MMRnode_a - Row 3
(3 rows)

MMRnode_a=# SELECT * FROM MMR_seq_child_tbl;
        id        |        field        |    parent_id
------------------+---------------------+------------------
 4503599627370497 | MMRnode_a - Row 1-1 | 4503599627370497
 4503599627370498 | MMRnode_a - Row 1-2 | 4503599627370497
 4503599627370499 | MMRnode_a - Row 2-1 | 4503599627370498
 4503599627370500 | MMRnode_a - Row 2-2 | 4503599627370498
 4503599627370501 | MMRnode_a - Row 3-1 | 4503599627370499
 4503599627370502 | MMRnode_a - Row 3-2 | 4503599627370499
(6 rows)

Subsequent row insertions and synchronization

The following rows are inserted on MMRnode_a:

INSERT INTO MMR_seq_tbl (field) VALUES ('MMRnode_a - Row 4');
INSERT INTO MMR_seq_child_tbl (field, parent_id) VALUES ('MMRnode_a - Row 4-1', 4503599627370503);

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
 4503599627370503 | MMRnode_a - Row 4
(4 rows)

MMRnode_a=# SELECT * FROM MMR_seq_child_tbl ORDER BY id;
        id        |        field        |    parent_id
------------------+---------------------+------------------
 4503599627370497 | MMRnode_a - Row 1-1 | 4503599627370497
 4503599627370498 | MMRnode_a - Row 1-2 | 4503599627370497
 4503599627370499 | MMRnode_a - Row 2-1 | 4503599627370498
 4503599627370500 | MMRnode_a - Row 2-2 | 4503599627370498
 4503599627370501 | MMRnode_a - Row 3-1 | 4503599627370499
 4503599627370502 | MMRnode_a - Row 3-2 | 4503599627370499
 4503599627370504 | MMRnode_a - Row 4-1 | 4503599627370503
(7 rows)

The following rows are inserted on MMRnode_b:

INSERT INTO MMR_seq_tbl (field) VALUES ('MMRnode_b - Row 1');
INSERT INTO MMR_seq_child_tbl (field, parent_id) VALUES ('MMRnode_b - Row 1-1', 9007199254740993);

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
(4 rows)

MMRnode_b=# SELECT * FROM MMR_seq_child_tbl ORDER BY id;
        id        |        field        |    parent_id
------------------+---------------------+------------------
 4503599627370497 | MMRnode_a - Row 1-1 | 4503599627370497
 4503599627370498 | MMRnode_a - Row 1-2 | 4503599627370497
 4503599627370499 | MMRnode_a - Row 2-1 | 4503599627370498
 4503599627370500 | MMRnode_a - Row 2-2 | 4503599627370498
 4503599627370501 | MMRnode_a - Row 3-1 | 4503599627370499
 4503599627370502 | MMRnode_a - Row 3-2 | 4503599627370499
 9007199254740994 | MMRnode_b - Row 1-1 | 9007199254740993
(7 rows)

The following rows are inserted on MMRnode_c:

INSERT INTO MMR_seq_tbl (field) VALUES ('MMRnode_c - Row 1');
INSERT INTO MMR_seq_child_tbl (field, parent_id) VALUES ('MMRnode_c - Row 1-1', 13510798882111489);

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
 13510798882111489 | MMRnode_c - Row 1
(4 rows)

MMRnode_c=# SELECT * FROM MMR_seq_child_tbl ORDER BY id;
        id         |        field        |     parent_id
-------------------+---------------------+-------------------
  4503599627370497 | MMRnode_a - Row 1-1 |  4503599627370497
  4503599627370498 | MMRnode_a - Row 1-2 |  4503599627370497
  4503599627370499 | MMRnode_a - Row 2-1 |  4503599627370498
  4503599627370500 | MMRnode_a - Row 2-2 |  4503599627370498
  4503599627370501 | MMRnode_a - Row 3-1 |  4503599627370499
  4503599627370502 | MMRnode_a - Row 3-2 |  4503599627370499
 13510798882111490 | MMRnode_c - Row 1-1 | 13510798882111489
(7 rows)

After you perform a synchronization replication, there are no uniqueness conflicts. The following shows the synchronized, consistent tables in the primary nodes:

Content of MMRnode_a after synchronization:

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
  4503599627370503 | MMRnode_a - Row 4
  9007199254740993 | MMRnode_b - Row 1
 13510798882111489 | MMRnode_c - Row 1
(6 rows)

MMRnode_a=# SELECT * FROM MMR_seq_child_tbl ORDER BY id;
        id         |        field        |     parent_id
-------------------+---------------------+-------------------
  4503599627370497 | MMRnode_a - Row 1-1 |  4503599627370497
  4503599627370498 | MMRnode_a - Row 1-2 |  4503599627370497
  4503599627370499 | MMRnode_a - Row 2-1 |  4503599627370498
  4503599627370500 | MMRnode_a - Row 2-2 |  4503599627370498
  4503599627370501 | MMRnode_a - Row 3-1 |  4503599627370499
  4503599627370502 | MMRnode_a - Row 3-2 |  4503599627370499
  4503599627370504 | MMRnode_a - Row 4-1 |  4503599627370503
  9007199254740994 | MMRnode_b - Row 1-1 |  9007199254740993
 13510798882111490 | MMRnode_c - Row 1-1 | 13510798882111489
(9 rows)

Content of MMRnode_b after synchronization:

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
  4503599627370503 | MMRnode_a - Row 4
  9007199254740993 | MMRnode_b - Row 1
 13510798882111489 | MMRnode_c - Row 1
(6 rows)

MMRnode_b=# SELECT * FROM MMR_seq_child_tbl ORDER BY id;
        id         |        field        |     parent_id
-------------------+---------------------+-------------------
  4503599627370497 | MMRnode_a - Row 1-1 |  4503599627370497
  4503599627370498 | MMRnode_a - Row 1-2 |  4503599627370497
  4503599627370499 | MMRnode_a - Row 2-1 |  4503599627370498
  4503599627370500 | MMRnode_a - Row 2-2 |  4503599627370498
  4503599627370501 | MMRnode_a - Row 3-1 |  4503599627370499
  4503599627370502 | MMRnode_a - Row 3-2 |  4503599627370499
  4503599627370504 | MMRnode_a - Row 4-1 |  4503599627370503
  9007199254740994 | MMRnode_b - Row 1-1 |  9007199254740993
 13510798882111490 | MMRnode_c - Row 1-1 | 13510798882111489
(9 rows)

Content of MMRnode_c after synchronization:

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
  4503599627370503 | MMRnode_a - Row 4
  9007199254740993 | MMRnode_b - Row 1
 13510798882111489 | MMRnode_c - Row 1
(6 rows)

MMRnode_c=# SELECT * FROM MMR_seq_child_tbl ORDER BY id;
        id         |        field        |     parent_id
-------------------+---------------------+-------------------
  4503599627370497 | MMRnode_a - Row 1-1 |  4503599627370497
  4503599627370498 | MMRnode_a - Row 1-2 |  4503599627370497
  4503599627370499 | MMRnode_a - Row 2-1 |  4503599627370498
  4503599627370500 | MMRnode_a - Row 2-2 |  4503599627370498
  4503599627370501 | MMRnode_a - Row 3-1 |  4503599627370499
  4503599627370502 | MMRnode_a - Row 3-2 |  4503599627370499
  4503599627370504 | MMRnode_a - Row 4-1 |  4503599627370503
  9007199254740994 | MMRnode_b - Row 1-1 |  9007199254740993
 13510798882111490 | MMRnode_c - Row 1-1 | 13510798882111489
(9 rows)
← Prev

Converting a standard sequence to an MMR-ready sequence

↑ Up

Conflict prevention with an MMR-ready sequence

Next →

Automatic conflict-resolution example