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:

1. Update the sequence values in the existing rows with MMR-ready sequence compatible values.
2. 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.