Schema review workarounds

The following sections provide workarounds for issues detected by the Voyager migration assessment and schema analysis commands.

Review the unsupported features and limitations, and implement the suggested workarounds to successfully migrate data from MySQL, Oracle, or PostgreSQL to YugabyteDB.

• Workarounds for PostgreSQL issues
• Workarounds for Oracle issues
• Workarounds for MySQL issues

Unsupported features

Currently, yb-voyager doesn't support the following features:

Assessment and schema analysis limitations

Although migration assessment and schema analysis detect most issues you may face when migrating a database so that you can work around and mitigate them, the commands have the following known limitations.

Normalized queries in pg_stat_statements

The pg_stat_statements extension in PostgreSQL tracks execution statistics of SQL queries by normalizing them. Because normalization removes the constants, migration assessment can't detect issues related to the constants for the following scenarios:

• JSON_TABLE (JSON Query function) usage in DML statements
• Non-decimal integer literals in DML statements
• Two-Phase Commit (XA syntax)

For example:

--query stored in pg_stat_statements:
SELECT $1 as binary; 

--actual query:
SELECT 0b101010 as binary;

Transactional queries in pg_stat_statements

pg_stat_statements records a single transaction as multiple separate query entries. This fragmentation makes it challenging to detect issues that occur within transaction boundaries, such as:

• DDL operations within Transaction

Example:

yugabyte=# \d test
Did not find any relation named "test".
yugabyte=# BEGIN;
BEGIN
yugabyte=*# CREATE TABLE test(id int, val text);
CREATE TABLE
yugabyte=*# \d test
                Table "public.test"
Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
id     | integer |           |          | 
val    | text    |           |          | 
yugabyte=*# ROLLBACK;
ROLLBACK
yugabyte=# \d test
                Table "public.test"
Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
id     | integer |           |          | 
val    | text    |           |          | 

Determining the type during query processing

In complex queries, determining the type of data being handled is not always straightforward. This limitation affects the detection of JSONB subscripting in such queries, making it difficult to report issues accurately.

For example:

select ab_data['name'] from (select data as ab_data from test_jsonb);
# data is the JSONB column in test_jsonb table

Parser limitations

The internal Golang PostgreSQL parser used for schema analysis and migration assessment has certain limitations within PL/pgSQL blocks. This leads to not being able to detect issues on the statements embedded inside Expressions, Conditions, Assignments, Loop variables, Function call arguments, and so on.

For example:

CREATE OR REPLACE FUNCTION example() 
RETURNS VOID AS $$
DECLARE
    x TEXT; -- Adjust the type based on jsonb value type
BEGIN
    x := (SELECT jsonb_column['value'] FROM orders WHERE id = 1);
    -- Further processing logic here
    RAISE NOTICE 'Extracted value: %', x;
END;
$$ LANGUAGE plpgsql;

Global objects

Global objects in PostgreSQL are database-level objects that are not tied to a specific schema. As a result, schema analysis and migration assessment are unable to detect issues in the following global objects:

• CREATE ACCESS METHOD / ALTER ACCESS METHOD
• CREATE SERVER
• CREATE DATABASE with certain options (for example, ICU_LOCALE, LOCALE_PROVIDER)