PolarDB:2022

Category

Feature

Description

References

New feature

Oracle-compatible DATE types

Supports 64-bit DATE types that are compatible with Oracle.

64-bit DATE types

Category

Feature

Description

References

New feature

ST_GeoHashID function

This function is added to convert GeoHash strings to INT-type values.

ST_GeoHashID

ST_GeoHashFromID function

This function is added to convert INT-type values to Geohash strings.

ST_GeoHashFromID

ST_UpdatePyramid function

This function is added to update vector pyramids.

ST_UpdatePyramid

ST_SetUpdatePyramidRules function

This function is added to configure a rule to update a vector pyramid.

ST_SetUpdatePyramidRules

ST_BuildPyramidUseGeomSideLen function

This function is added to build a vector pyramid based on the size of the vector bounding box.

ST_BuildPyramidUseGeomSideLen

ST_InterpolateRaster function

This function is added to perform spatial interpolation operations.

ST_InterpolateRaster

ST_Contour function

This function is added to generate contours.

ST_Contour

ST_Statistics function

This function is added to collect statistics on and analyze custom weights for raster data in MultiPoint mode.

ST_Statistics

ST_Resample function

This function is added to resample a trajectory.

ST_Resample

ST_Density function

This function is added to collect statistics on the density of a trajectory.

ST_Density

ST_StayPoint function

This function is added to identify the stay points of a trajectory.

ST_StayPoint

Fixed issue

The following issue is fixed: The raster object becomes empty after the ST_BuildHistogram function is called.

The following issue is fixed: Downtime and Z-value errors occur due to the null values of the ST_AsTable function.

The following issue is fixed: Incorrect results are returned when the GeoHash mode is used for the ST_Split function.

The following issue is fixed: Errors occur in attribute field encoding for the ST_Tile function.

Category

Feature

Description

References

New feature

smlar plug-in

The smlar plug-in is supported. This plug-in is used to calculate the similarity of database data in an efficient manner.

smlar

Default value modification for some parameters

The default values of some parameters in the minor engine version 1.1.28 or later of PolarDB for PostgreSQL (Compatible with Oracle) are modified. The new default values are valid only for new clusters. The new default values can significantly improve the database performance. You can modify the default values of some parameters in the console based on your business requirements.

Release notes on modified default values for some parameters

hypopg plug-in

The hypopg plug-in is supported. This plug-in is used to help you check whether a type of indexes accelerate one or more queries.

hypopg

pg_bigm plug-in

The pg_bigm plug-in is supported. This plug-in supports full-text search. You can use this plug-in to create 2-gram Generalized Inverted Index (GIN) indexes. The indexes accelerate full-text search queries.

pg_bigm

hll plug-in

The hll plug-in is supported. This plug-in supports the HyperLogLog (hll) data type. You can use this to estimate page views (PV) and unique visitors (UV).

hll

Support for holdable cursors in stored procedures

The holdable mode is supported to ensure that the current cursor is not released immediately. This way, cursor operations can be performed across transactions.

Use holdable cursors in stored procedures

varbitx plug-in

The varbitx plug-in is supported. This plug-in is an extension of the varbit plug-in provided by PolarDB for Oracle. The varbitx plug-in supports a larger number of BIT operations and is ideal for more scenarios.

varbitx

ZomboDB plug-in

ZomboDB is supported. ZomboDB is a PostgreSQL extension plug-in that supports the access methods provided by native PostgreSQL. ZomboDB also provides powerful text search and analytics features.

ZomboDB

Category

Feature

Description

References

New feature

Real-time materialized view

Real-time materialized views are supported by PolarDB. When data manipulation language (DML) operations are performed on dependency tables, data in real-time materialized views is consistent with the data in the dependency tables. This significantly improves query performance.

Real-time materialized views

Use of polar_sql_mapping without modification

The polar_sql_mapping plug-in is supported. You can use this plug-in without modifying the configuration file.

polar_sql_mapping

pg_sphere plug-in

The pg_sphere plug-in is supported. This plug-in is a PostgreSQL extension that supports spherical data types, calculation functions, and operators.

pg_sphere

Partition pruning supported by global partitioned indexes

Global partitioned indexes can be used to implement partition pruning based on optimizers and executors.

Create a global index

Support for the Logical Replication Slot Failover feature

The Logical Replication Slot Failover feature of PolarDB for PostgreSQL (Compatible with Oracle) is supported. If the primary instance of your database system fails, this feature can synchronize all logical replication slots to the secondary instance.

Logical replication slot failover

Isolation level for sessions

The following syntax can be used to configure the isolation level for sessions. The configurations take effect only for the current session.

ALTER SESSION SET ISOLATION_LEVEL = {SERIALIZABLE | READ COMMITTED};

None

Scanning by tuples in parallel execution

The polar_px_scan_granularity parameter is added to specify the granularity by which data can be scanned in parallel execution. Default value: block. Valid values:

• row: scans data by tuples in parallel execution.

• block: scans data by blocks in parallel execution.

None

polar_enable_strategy_reject_buffer parameter

The polar_enable_strategy_reject_buffer parameter is added to specify whether the ring buffer limit can be exceeded in batch read scenarios. Default values: off. Valid values:

• off: The ring buffer is still used in batch read scenarios. If the buffer obtained from the ring is a dirty page, the backend process needs to save the dirty page to the disk.

• on: The ring buffer limit can be exceeded in batch read scenarios. If the buffer obtained from the ring is a dirty page, a new buffer can be obtained from the buffer pool. If this parameter is set to on, the number of backend flush operations decreases, which accelerate queries. However, because the ring buffer limit is exceeded, the buffer pool may be occupied by the table data of the current query. When multiple connections are used to query different tables, buffer eviction and I/O operations may occur.

N/A

Category

Feature

Description

References

New feature

Support for DBMS_METADATA

You can use the method provided in the DBMS_METADATA package to obtain the metadata of an object in the database to reconstruct the object.

DBMS_METADATA

Support for nls_timestamp(_tz)_format

The nls_timestamp(_tz)_format format is supported.

nls_timestamp(_tz)_format

Support for logon and logoff triggers

Logon and logoff triggers are event triggers that can be used to trigger specified functions or stored procedures for logon and logoff.

Logon and logoff triggers

Batch writes to cache

The polar_enable_strat_bulkwrite parameter is added to control the cache size for the data written by the session. Default value: true. Valid values:

• true: Only partial cache space is provided for the data written by the session. This is consistent with the native PostgreSQL.

• false: All cache space is provided for the data written by the session.

None

Support for ORDER BY clauses in aggregate functions

ORDER BY clauses are supported in aggregate functions in which GROUP BY clauses are not included.

• Syntax

  SELECT aggfunc(xx) FROM yy ORDER BY any target;

• Method used to enable the feature

  Set the polar_enable_aggfunc_allowed_sort_target parameter to on in the console.

ORDER BY clause

Support for expressions that contain distinct objects in ORDER BY clauses

Expressions that contain distinct objects are supported in ORDER BY clauses.

• Syntax

  SELECT distinct xx, yy FROM zz ORDER BY expr(xx, yy);

• Method used to enable the feature

  Set the polar_enable_distinct_order_by_expr parameter to on in the console.

Support for expressions that contain aliases in ORDER BY clauses

Expressions that contain aliases are supported in ORDER BY clauses.

• Syntax

  SELECT xx yy, mm nn FROM zz ORDER BY expr(yy,nn);

• Method used to enable the feature

  Set the polar_enable_order_by_use_alias_expr parameter to on in the console.

All historical compatibility features enabled by default

By default, the following parameters are enabled for new PolarDB for Oracle clusters:

• polar_comp_redwood_greatest_least

• polar_comp_redwood_strings

• polar_enable_rownum_connectby_operator

• polar_enable_rownum_rewrite_for_write

• polar_plsql_auto_unpin_failed_portal

• polar_plsql_auto_unpin_failed_portal_stmt_tx

• polar_enable_inline_cte

• polar_enable_comp_numeric_format

None

Category

Feature

Description

References

New feature

ST_Intersection aggregate function

This function is added to aggregate an array of geometry objects.

ST_Intersection

ST_AddZ function

This function is supported. This function accepts z-axis values as the value of an input parameter and converts a 2D or 3DM geometry to a 3D or 4D geometry.

ST_AddZ

ST_SetValue function

This function is added to specify pixel values at specified pixel positions and set pixel values based on the spatial range of a geometry object.

ST_SetValue

Optimized feature

ST_ExportTo function

This function is optimized to export raster objects as COG files.

ST_ExportTo

ST_CreateRast function

This function is optimized to convert multiple raster records or arrays to raster objects.

ST_CreateRast

ST_Split function

This function is optimized to support the settings of the start time when you want to split a trajectory by time. The optimized function also supports the splitting of a trajectory by trajectory point index.

ST_Split

ST_Union function

This function is optimized to support batch execution based on memory usage to prevent out-of-memory (OOM) errors caused by large data volumes.

ST_Union

ST_CreateRast function

This function is optimized to create raster data based on Hadoop Distributed File System (HDFS).

ST_CreateRast

ST_ImportFrom function

This function is optimized to import raster data based on HDFS.

ST_ImportFrom

ST_ExportTo function

This function is optimized to export raster data to HDFS.

ST_ExportTo

Fixed issue

The following issue is fixed: The ST_makeTrajectory function may cause system downtime.

The following issue is fixed: The ST_lcsSimilarity function cannot identify the time range measured in days.

The following issue is fixed: The ST_AsEWKB function cannot convert trajectories that are created from data tables.

The following issue is fixed: Memory usage problems occur when the ST_BuildPyramid function is called to create a vector pyramid based on a large amount of data.

Category

Feature

Description

References

New feature

pgAudit plug-in

The pgAudit plug-in is supported. This plug-in is used to generate audit logs.

pgAudit

Support for DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

The DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function is added to return the formatted contents of the call stack in which the exception is thrown.

DBMS_UTILITY

Modification of data types in a base table for a view

If a simple view is built on a base table, you can directly modify the data types of columns in the base table.

ALTER TABLE

Support for DML from subquery

Subqueries can be used in the following DML statements: INSERT, UPDATE, and DELETE.

• INSERT

• UPDATE

• DELETE

Global partitioned index scan in DML statements

GLOBAL PARTITIONED INDEX SCAN can be used in the following DML statements: DELETE and UPDATE.

Use global partitioned index scan in DML operations

Case insensitivity

When this feature is enabled, the following object names are determined to be equivalent: object names that are not enclosed in double quotation marks, object names that are enclosed in double quotation marks and written in uppercase letters, and object names that are enclosed in double quotation marks and written in lowercase letters.

Case insensitivity

Oracle built-in functions

The following Oracle built-in functions are added to PolarDB for Oracle clusters:

• to_timestamp/to_char/to_date(.., nlsparam)

• wm_concat

• bitand/bitor

• to_dsinterval/to_yminterval

None

Plug-ins installed by default

By default, the following plug-ins are installed on PolarDB for Oracle clusters:

• polar_from_tz

• polar_nlssort

• polar_regexp_like

• polar_utility

• sys_guid

• polar_constraint

• polar_parameter_check

• polar_worker

• roaringbitmap

• polar_csn

None

Category

Feature

Description

References

New feature

plan_cache_mode

The plan_cache_mode parameter is added to configure the plan cache mode. Default value: auto. Valid values:

• auto: automatically chooses between the custom and generic modes.

• force_custom_plan: uses the custom plan generated by the optimizer.

• force_generic_plan: uses the generic plan in the plan cache.

None

Category

Feature

Description

References

New feature

ST_makeTrajectory function

This function is added to convert rows in a table to trajectories.

ST_makeTrajectory

ST_AsTable function

This function is added to convert trajectories to a table.

ST_AsTable

Fixed issue

The following issue is fixed: Anonymous temporary tables cannot be automatically deleted during raster data operations.

Category

Feature

Description

References

New feature

Support for DBMS_SQL.define_array

The DEFINE_ARRAY stored procedure is added to the DBMS_SQL package.

DBMS_SQL

Support for both %TYPE and :=

Default values in stored procedures can be specified by using both %TYPE and the := operator.

Use %TYPE in variable declarations

Global Partitioned Index

Global partitioned indexes can be created in partitioned tables.

Create a global index

pg_hint_plan

The pg_hint_plan plug-in is supported. This plug-in is used to add hints to SQL statements. The hints specify how you want the SQL statements to be executed. This way, you can optimize the execution plans of the SQL statements.

pg_hint_plan

Optimized feature

Performance optimization for the memory release of the resource manager

• The mem_release_policy parameter is added to specify the memory release policy. Default value: default. Valid values:

  • default: When the database thresholds are reached, the resource manager starts the adaptive policy. This parameter cannot be combined with other parameters. The memory is released based on the default policy. Processes are divided into idle processes and active processes. Idle processes are first released and then active processes.

  • cancel_query: cancels queries of active processes.

  • terminate_idle_backend: terminates idle processes.

  • terminate_any_backend: terminates any processes.

  • none: takes no actions.

• The total_memcontext_dump_rate parameter is added to specify the threshold for memory context output. Default value: 75. Valid values: 20 to 95. The parameter value takes effect after you restart the cluster or perform a reload operation.

• The max_proc_mcxtdump_num parameter is added to specify the number of processes for memory context output. Default value: 1. Valid values: 1 to 100. The parameter value takes effect after you restart the cluster or perform a reload operation.

• The interval_max_num parameter is added to specify the number of intervals for memory context output. Default value: 0. Valid values: 0 to INT32_MAX. The parameter value takes effect after you restart the cluster or perform a reload operation.

None

Category

Feature

Description

References

New feature

Implicit type conversion

The implicit type conversion feature is provided to ensure that PolarDB is compatible with the matrix of Oracle implicit conversions. You can use this feature to convert high-precision numerical data to low-precision numerical data.

None

Analyze operations in parallel execution

• The polar_px_enable_explain_allstat parameter is added to display the execution result (such as the number of tuples processed by each worker and the execution duration) of each worker when the explain analyze command is run in parallel queries.

• The polar_px_explain_memory_verbosity parameter is added to display the memory usage of each worker when the explain analyze command is run in parallel queries. Default value: on. Valid values:

  • summary: the collective information of the memory usage of each worker.

  • detail: the detailed memory usage information of each worker.

None

Support for changing column types in GTTs

ALTER statements are supported in global temporary tables (GTTs).

None

Support for Clang

Clang is supported.

None

Category

Feature

Description

References

New feature

Object storage paths

Raster data can be accessed from self-managed Multi-Cloud Object Storage (MinIO) servers.

Object storage paths

ST_PointValues function

This function is added to return the pixel values of all bands.

ST_PointValues

Fixed issue

The following issue is fixed: The band order is incorrect when the ST_Clip function is executed.

The following issue is fixed: The performance is low due to lack of primary key indexes during raster updates.

The following issue is fixed: Retries are not supported when you cannot access the trajectory objects stored in an Object Storage Service (OSS) bucket.

Category

Feature

Description

References

New feature

GanosBase is supported for PostgreSQL 14.

None

Optimized feature

The cache for trajectory objects in external storage is increased to improve access performance.

None

Fixed issue

The following issue is fixed: File suffixes are missing when raster data is exported to OSS.

The following issue is fixed: Statistics are not collected for specific raster data.

Category

Feature

Description

References

New feature

Drop flashback

The drop flashback feature can be used to quickly restore deleted tables and view and clear the recycle bin.

Drop flashback

Advancement of the OPT_PARAM hint

The OPT_PARAM hint can be used to configure a specific parameter during the execution of a single SQL statement.

OPT_PARAM hints

Multi-level partitioned tables in parallel execution

The parallel execution feature can be used to query data from multi-level partitions in parallel.

Query data from partitioned tables

DML use GLOBAL INDEX

The global index scan feature can be used in the following DML operations: DELETE and UPDATE.

Use global index scan in DML operations

Category

Feature

Description

References

New feature

ST_LinearStretch function

This function is added to linearly stretch a raster and return a new raster.

ST_LinearStretch

ST_BuildPercentiles function

This function is added to return the percentage of pixels for a set of raster bands.

ST_BuildPercentiles

ST_ComputeStatistics function

This function is added to return statistics on a raster object, including histograms and percentage.

ST_ComputeStatistics

ST_Percentile function

This function is added to return the percentage of pixels for a raster band.

ST_Percentile

Optimized feature

Parallel computing

The ST_SummaryStats and ST_BuildHistogram functions can be used to perform parallel computing.

None

ST_ExportTo function

This function returns the value false instead of an error message if the specified file already exists.

None

ST_ImportFrom function

If an import error occurs, this function returns the details of the error. You can troubleshoot the error based on the error details.

ST_ImportFrom

Fixed issue

The following issue is fixed: Half a pixel is lost due to number rounding when a raster is cropped.

The following issue is fixed: The data of a raster cannot be properly imported or collected if the data contains values such as NAN and INF.