Converting a standard sequence to an MMR-ready sequence v7
If you have an existing application with tables that use a standard sequence such as with the SERIAL
data type, you can modify these tables to use the MMR-ready sequence for incorporation into a multi-master replication system. The basic conversion process is:
- Update the sequence values in the existing rows with MMR-ready sequence compatible values.
- Modify the column definition to be compatible with the MMR-ready sequence. These changes include modifying or adding the
DEFAULT
clause to use the MMR-ready sequence function to supply the default values for later inserts.
To perform the conversion of existing sequence values, first create the unique database identifier as described in Step 1 of Creating an MMR-ready sequence.
Use the following function to update the existing primary key and foreign key values that you need to convert.
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$;
The function input argument is the old sequence value. The function returns the new MMR-ready sequence value.
The function input and return arguments are data type BIGINT
, so you must modify the existing sequence columns accordingly before using the function.
Finally, the sequence columns must include the clauses BIGINT NOT NULL DEFAULT MMR_sequence_nextval('seq_name')
to supply MMR-ready sequence values for future inserts.
See Creating an MMR-ready sequence for information on creating the objects required for an MMR-ready sequence.