PolarDB:1.0 to 2.0 migration preparation and FAQs

Problem description: In version 2.0, when calling stored procedures or functions with OUT parameters, you must pass a value at the OUT parameter position, otherwise the call will fail.

Problem example:

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

Solution: Business code needs to be modified. This is a kernel-level compatibility change that cannot be resolved through configuration. You must modify the application code. When calling stored procedures with OUT parameters, you must pass a placeholder value at the OUT parameter position (this value will not be actually used, it is only for parameter type detection).

Problem description: After migration, tables, views, stored procedures, and other database objects might not be found, usually related to the search_path configuration.

Solution:

• Preferred solution: Set database-level search_path.

  Note

  If you use JDBC to connect to the database, you need to set currentSchema=schema1 in the JDBC connection string to specify the namespace.

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

• Fallback solution: Create public synonyms When adjusting the search_path cannot solve the problem, you can resolve object access issues by creating public synonyms:

  -- Script to generate synonym creation statements in batch
  DO $$
  DECLARE
      relation_name VARCHAR(100);
      relation_nsp VARCHAR(100);
  BEGIN
      -- Query all tables and views created by the specified user
      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
          -- Generate SQL statements to create public synonyms
          RAISE NOTICE 'CREATE PUBLIC SYNONYM % FOR %.%;', 
                       relation_name, relation_nsp, relation_name;
      END LOOP;
  END $$;

  Note

  • Replace <actual object creator username> in the above script with the actual username.

  • After executing the script, copy the output CREATE PUBLIC SYNONYM statements, confirm the SQL statements are correct, and manually execute these statements to create synonyms.

Problem description: After migration, there might be insufficient access permissions for tables or views.

Solution: Manually add necessary permissions using the GRANT statement:

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

Problem description: If you created objects in version 1.0 using double quotes with alternating case, you might encounter issues where objects cannot be found in version 2.0.

Problem cause: The application code depends on a bug behavior from early versions of 1.0.

Solution: Enable the case ignore parameter in version 2.0:

-- Enable case interleaving ignore functionality
SET polar_enable_ignore_case_interleaving = on;