PolarDB:April to September 2024

Release notes

Engine compatibility improvements

Only engine compatibility improvements are listed here. For more information, see PolarDB for PostgreSQL (Compatible with Oracle) 2.0 release notes.

• Built-in objects

  • The number() function is supported. The function call method with quotation marks (such as "TO_CHAR") is introduced as an extension of the case insensitivity feature for better compatibility with function calls in native SQL.

  • Row types of IS NULL and IS NOT NULL are supported to ensure compatibility with Oracle behaviors.

  • Supports for synonyms, partitions, packages and their bodies, types and their bodies, contexts, directories, languages, operators, tasks, and queues in DBA_OBJECTS views are enhanced. The index types and nls_parameters views in DBA_INDEXES views are enhanced. These serve the purpose of improving view compatibility.

  • The following issue is fixed: CHAR types fail to flow back to Oracle due to semantic differences.

  • The following issue is fixed: Errors are returned for has_schema_privilege functions due to built-in objects.

  • The following issue is fixed: schemas are also included when object names are included in querying views and therefore filtering objects by using view names fails.

  • The following issue is fixed: The decimal part is not truncated when a timestamptz type is converted to a date type.

  • The following issue is fixed: Default values of stored procedure parameters are displayed in the information_schema.parameters view.

  • The following issue is fixed: The date type is not displayed in multiple views of the INFORMATION_SCHEMA database.

  • The following issue is fixed: The stored procedure definition is incompletely displayed in the DBA_SOURCE view.

• SQL syntax

  • EDITIONTABLE-related keywords are supported to extend compatibility scenarios during structure migration.

  • Multiple special scenarios for using the DISTINCT and ORDER BY keywords are supported to meet the requirements for using non-standard SQL statements in Oracle.

  • You can enter date strings that slightly mismatch the required formats in the TO_DATE function. This improves compatibility of returned data types of the NVL and CONVERT functions.

  • The returned data types of the '+' operator are compatible with Oracle.

  • Case insensitivity is supported for synonyms to improve compatibility with Oracle.

  • The keywords in the SELECT statement can be used as table aliases to further enhance compatibility with Oracle.

  • Case insensitivity is supported if both uppercase and lowercase letters are included to improve compatibility with non-standard SQL statements.

  • You can define the session transaction isolation level by executing the ALTER SESSION statement.

  • The LIKE2, LIKE4, and LIKEC operators are supported.

  • IS NaN and IS INFINITE are supported.

  • The DISTINCT operation is supported in window functions.

  • The following issue is fixed: If the b table contains only a row, the semantics of the where a in b expression are inconsistent with Oracle.

  • The following issue is fixed: The range table entry (RTE) cannot be found when you update synonyms.

  • Multiple issues relate to binding parameters to functions are fixed to improve system stability.

• DDL statements

  • The CREATE PROFILE statement is supported to provide new features such as password reuse time, time, and password verify functions and to improving database security.

  • When you use the ALTER TABLE ... RENAME statement to modify table column names, you can use the COLUMN keyword.

  • The CREATE CONTEXT statement supports the INITIALIZED clause to further improve compatibility.

  • You can create PUBLIC synonyms in the PUBLIC SCHEMA.

  • Synonyms for PACKAGE and TYPE BODY are supported.

  • The MODIFY COLUMN is supported for compatibility with Oracle.

  • You can execute the DROP AGGREGATE statement to delete aggregate functions. This improves compatibility.

  • The FORCE keyword is supported in DROP statements.

• Partitioned tables

  • Indexes can be automatically created for partitioned tables. You want to create global indexes without explicitly using the GLOBAL keyword.

  • The REFERENCING OLD clause is supported in partitioned table triggers.

  • The issues related to stability are fixed for creating partitioned tables.

• Packages

  • Package variables can be used as default values for package function parameters.

  • The CREATE OR REPLACE PACKAGE BODY statement is supported.

  • You can use forward declarations to declare private functions in the package body to improve compatibility.

  • The functions and package variables missing in the DBMS_LOB package are supported.

• PL/SQL

  • Transaction commit is supported in functions and function commit is supported in the SECURE DEFINER state.

  • The stability issues related to statement-level transactions and autonomous transactions are fixed.

  • When packages are not instantiated, you can use cursor % attribute to access cursor attributes in packages.

  • The following issue is fixed: An error is returned when :new/:old is used in the declaration segment of a trigger function.

  • The following issue is fixed: When a stored procedure contains out refcursor and the parameter is NULL, an error is returned for using other internal cursors.

  • The following issue is fixed: Incorrect object addresses are transmitted from synonyms to a trigger.

  • The following issue is fixed: An error is returned when a foreign table is created due to event triggers created in DDL logical replication.

• Collection types

  • The column_value pseudo column can be used in TABLE expressions to access elements in collections.

  • You can use the record.field().field function to directly access fields of the collection type in a record.

  • The collection_type % type feature is supported.

Client and driver compatibility

PolarDB-Tools

Compatibility issues are fixed for the PolarDB-Tools feature to improve data compatibility. Specifically, the following issues are fixed:

• The compatibility issue when the colon parameter is bound in PostgreSQL.

• The compatibility issue of the OnCommitRollback feature.

JDBC

JDBC was updated to V45.4.0.10.4 on September 2, 2024. The following improvements are included in this update:

• The issue of incorrect bindings in PL blocks is fixed. This feature is disabled by default to reduce the impact on performance.

• Implicit data conversion within a major data type is supported. Character types (such as VARCHAR and CHAR) and numeric types (such as NUMERIC, INTEGER, and DOUBLE) can be converted to each other as INOUT parameters.

• The return value of the getDatabaseProductName() function in the JDBC metadata is updated to “POLARDB2 Database Compatible with Oracle”.

• The following issue is fixed: In MyBatis, the database cannot correctly infer the parameter type when the registered type of the object entity is TIMESTAMP.

.NET

. NET was updated to 8.0.4.1 on September 18, 2024. Then, PolarDB supports all .NET drivers: .NET 2.0, .NET 3.5, .NET 4.0, .NET 5.0, .NET 6.0, .NET 7.0, and .NET 8.0. Multiple versions such as netcoreapp3.1, netstandard2.0, and netstandard2.1 are supported. The following improvements are included in this update:

• Support for 8.0.4.1 and compatibility with .NET 6.0, .NET 7.0, and .NET 8.0.

• PL/SQL stored procedures can be executed without specifying the double dollar signs ($$).

• PolarDB for PostgreSQL (Compatible with Oracle) 2.0 supports 64-bit date formats.

• The following issue is fixed: The built-in objects of the database management system (DBMS) in metadata are not correctly identified.

• The following issue is fixed: Data types are not correctly read due to ROWIDs.

pgAdmin4

The pgAdmin4-compatible PolarDB version was released on August 26, 2024. This version can be deployed on Windows systems and is designed for PL/SQL debugging in PolarDB.

• Debugging PL/SQL stored procedures is supported.

DTS migration

ADAM evaluation

The latest version of ADAM evaluation is released to improve the accuracy of migration compatibility assessment and keep up with PolarDB syntax updates. The following improvements are included in this update:

• The identification accuracy of built-in package and built-in object in ADAM evaluation is enhanced.

• The identification of some keywords in the syntax tree is enhanced.

DTS structure migration

The collaboration between PolarDB and DTS has been significantly boosted. The following improvements are included:

• The sequence synchronization feature is supported for incremental data.

• Structure migration compatibility is enhanced, the case-insensitivity feature is supported, and the issues related to index migration and partitioned table adaptation are fixed.

• The compatibility issue of the DATE type is fixed, and the handling of the character type NaN is improved.