1.0 to 2.0 migration preparation and FAQs

During migration, Sequence current values may not be copied to the destination database. If the destination Sequence starts lower than the source, newly generated values conflict with existing data (duplicates) or leave gaps in the sequence.

Synchronize Sequence values from the source to the destination database immediately after migration and before your application starts generating new values:

1. Run the following SQL in the source database to get the current value of each Sequence:

   DO LANGUAGE plpgsql $$
   DECLARE
     nsp name;
     rel name;
     val int8;
   BEGIN
     FOR nsp, rel IN
       SELECT nspname, relname
       FROM pg_class t2, pg_namespace t3
       WHERE t2.relnamespace = t3.oid
         AND t2.relkind = 'S'
         AND t2.relowner != 10
     LOOP
       EXECUTE format($_$select last_value from %I.%I$_$, nsp, rel) into val;
       RAISE NOTICE '%',
       format($_$select setval('%I.%I'::regclass, %s);$_$, nsp, rel, val+1);
     END LOOP;
   END;
   $$;

2. Copy the setval(...) statements from the output and run them in the destination database.

3. Confirm the Sequence values in the destination match the source before opening traffic.

In version 2.0, calling a stored procedure or function with OUT parameters requires passing a placeholder value at each OUT parameter position. Calls that omit OUT arguments—which worked in version 1.0—fail with an error.

This is a kernel-level compatibility change and cannot be resolved through configuration. Update your application code to pass a placeholder value at each OUT parameter position. The value is used only for type detection and does not affect the procedure's behavior.

-- Stored procedure definition
CREATE PROCEDURE p(a OUT INT) IS
BEGIN
  a := 1;
END;

-- Call method in PolarDB-O 1.0 (will fail in 2.0)
CALL p();

-- Call method compatible with PolarDB-O 2.0 and Oracle
CALL p(100); -- Parameter value can be any integer, only used for type detection

Missing objects after migration are almost always caused by search_path not being set on the destination cluster. Set it at the database level to match your schema layout:

ALTER DATABASE database_name SET search_path = 'schema1,schema2,public';

If you connect via JDBC, also add currentSchema=schema1 to the JDBC connection string to specify the namespace.

If adjusting search_path does not resolve the issue, create public synonyms as a fallback. The following script generates the CREATE PUBLIC SYNONYM statements for all tables and views owned by a specific user:

DO $$
DECLARE
    relation_name VARCHAR(100);
    relation_nsp VARCHAR(100);
BEGIN
    FOR relation_name, relation_nsp IN
        SELECT relname, relnamespace::regnamespace
        FROM pg_class
        WHERE relkind IN ('r', 'v')
        AND relowner = '<actual object creator username>'::regrole::oid
    LOOP
        RAISE NOTICE 'CREATE PUBLIC SYNONYM % FOR %.%;',
                     relation_name, relation_nsp, relation_name;
    END LOOP;
END $$;

Replace <actual object creator username> with the actual username. After running the script, review the output statements for correctness, then execute them to create the synonyms.

Permissions are not automatically migrated. Grant the necessary permissions manually:

-- Grant query permission on a table to a user
GRANT SELECT ON table_name TO user_name;

-- Grant query permission on a view to a user
GRANT SELECT ON view_name TO user_name;

-- Grant usage permission on a schema to a user
GRANT USAGE ON SCHEMA schema_name TO user_name;

If your application relied on a bug in early versions of 1.0 where double-quoted mixed-case identifiers were handled differently, enable the case-interleaving ignore parameter in version 2.0:

SET polar_enable_ignore_case_interleaving = on;