2022 PolarDBO engine feature updates

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

Converts GeoHash strings to integer values.

ST_GeoHashID

ST_GeoHashFromID function

Converts integer values to GeoHash strings.

ST_GeoHashFromID

ST_UpdatePyramid function

Updates a vector pyramid.

ST_UpdatePyramid

ST_SetUpdatePyramidRules function

Sets rules for vector pyramid update operations.

ST_SetUpdatePyramidRules

ST_BuildPyramidUseGeomSideLen function

Builds a vector pyramid based on the size of a vector bounding box.

ST_BuildPyramidUseGeomSideLen

ST_InterpolateRaster function

Performs spatial interpolation.

ST_InterpolateRaster

ST_Contour function

Generates contours.

ST_Contour

ST_Statistics function

Performs statistical analysis on raster data with custom weights using multipoint geometries.

ST_Statistics

ST_Resample function

Resamples trajectory data.

ST_Resample

ST_Density function

Calculates trajectory density statistics.

ST_Density

ST_StayPoint function

Identifies stay points in a trajectory.

ST_StayPoint

Bug fix

Fixed an issue where the raster object became empty after the ST_BuildHistogram function was called.

Fixed an issue where passing a null value to the ST_AsTable function for a trajectory caused a crash and returned incorrect z-values.

Fixed an issue where the ST_Split function returned incorrect results when splitting data by GeoHash.

Fixed an attribute field encoding error in the ST_Tile function.

Category

Parameter

Description

References

New feature

smlar plugin

Calculates data similarity efficiently.

smlar (approximate vector)

Adjusted default values for several parameters

Minor version V1.1.28 of adjusts default parameter values to significantly improve performance. Applies to new clusters only. Modify in the console as needed.

Adjusted default parameter values

hypopg plugin

Evaluates whether hypothetical indexes would improve query performance.

hypopg (hypothetical indexes)

pg_bigm plugin

Provides full-text search with 2-gram GIN index acceleration.

pg_bigm

hll plugin

Adds the HyperLogLog (hll) data type for estimating metrics such as page views (PVs) and unique visitors (UVs).

hll (approximate set)

Support for holdable cursors in stored procedures

Keeps cursors open after transaction commits, allowing operations to span multiple transactions.

Use holdable cursors in stored procedures

varbitx plugin

extends the varbit plugin with additional BIT operations for broader use cases.

varbitx

ZomboDB plugin

A PostgreSQL extension providing text indexing and analytics through native access methods.

ZomboDB

Category

Feature

Description

References

New feature

real-time materialized view

Real-time materialized views stay consistent with base tables during DML operations, improving query performance.

real-time materialized view

Simplified usage of sql_mapping

You can now use the polar_sql_mapping plugin without modifying the configuration file.

polar_sql_mapping

pg_sphere plugin

pg_sphere is a PostgreSQL plugin that supports spherical data types, functions, and operators.

pg_sphere

Executor pruning for global partitioned indexes

global partitioned indexes now support index pruning optimization, including optimizer pruning and executor pruning.

Create a GLOBAL INDEX

logical replication slot failover

Synchronizes logical replication slots from the primary to a secondary instance, enabling slot failover for and .

Logical Replication Slot Failover

Set the session-level isolation level

You can set the isolation level for the current session. This setting applies only to the current session. Use the following syntax:

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

None

Tuple-level scanning for parallel execution

The polar_px_scan_granularity parameter controls the scan granularity for parallel execution (PX). Valid values:

• row: Scans data at the tuple level.

• block (Default): Scans data at the block level.

None

polar_enable_strategy_reject_buffer parameter

The polar_enable_strategy_reject_buffer parameter controls whether the ring buffer limit can be exceeded in batch read scenarios. Valid values:

• off (Default): The ring buffer is used in batch read scenarios. If a buffer retrieved from the ring is a dirty page, the backend process must flush the dirty page to the disk.

• on: The ring buffer limit can be exceeded in batch read scenarios. If a buffer retrieved from the ring is a dirty page, a new buffer can be retrieved from the buffer pool. Enabling this option reduces the number of flushes performed by the backend process, which can improve query speed. However, exceeding the ring buffer limit may cause the buffer pool to fill up with data from the current query table. When multiple connections query different tables, buffer eviction and I/O operations can still occur.

None

Category

Feature

Description

References

New features

Support for DBMS_METADATA

Use the methods in DBMS_METADATA to retrieve object definitions for reconstruction.

DBMS_METADATA

Support for nls_timestamp(_tz)_format

Supports the nls_timestamp(_tz)_format time format.

nls_timestamp(_tz)_format

Support for logon and logoff triggers

A logon and logoff trigger is an event trigger that executes a specified function or stored procedure when a user logs on or off.

Logon and logoff triggers

Full caching for batch writes

The new polar_enable_strat_bulkwrite parameter controls how much cache space data from a session can occupy. The default value is true. Valid values:

• true: Matches native PostgreSQL behavior. Data from each session is restricted to a portion of the cache.

• false: Data written by a session can use the entire cache space.

None

Support for ORDER BY clauses with aggregate functions

You can now use an ORDER BY clause with an aggregate function, even without a GROUP BY clause.

• Syntax

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

• How to enable

  In the console, set the polar_enable_aggfunc_allowed_sort_target parameter to on.

ORDER BY clause

Support for expressions on DISTINCT columns in the ORDER BY clause

The ORDER BY clause can now use expressions based on columns from the SELECT DISTINCT list.

• Syntax

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

• How to enable

  In the console, set the polar_enable_distinct_order_by_expr parameter to on.

Support for aliased expressions in the ORDER BY clause

You can now use expressions with aliases in an ORDER BY clause.

• Syntax

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

• How to enable

  In the console, set the polar_enable_order_by_use_alias_expr parameter to on.

All legacy compatibility features enabled by default

The following parameters are enabled by default for new 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 features

ST_Intersection aggregate function

Calculates the intersection of a set of geometry objects.

ST_Intersection

ST_AddZ function

Converts a 2D or 3DM geometry object to a 3D or 4D object by using an array of Z-coordinates.

ST_AddZ

ST_SetValue function

Sets pixel values at specified positions or within the spatial extent of a given geometry object.

ST_SetValue

Improvements

ST_ExportTo function

You can now export raster objects as COG files.

ST_ExportTo

ST_CreateRast function

Converts multiple records or an array of regular grid points into a raster object.

ST_CreateRast

ST_Split function

When you split a trajectory by time, you can now specify a start time. You can also split a trajectory by the sequence number of its points.

ST_Split

ST_Union function

Now executes in batches based on memory usage to prevent out-of-memory (OOM) errors when processing large datasets.

ST_Union

ST_CreateRast function

You can now create raster objects from raster data stored in HDFS.

ST_CreateRast

ST_ImportFrom function

You can now import raster data from HDFS.

ST_ImportFrom

ST_ExportTo function

You can now export raster data to the HDFS file system.

ST_ExportTo

Bug fixes

Fixed an issue where the ST_makeTrajectory function could cause a crash.

Fixed an issue where the ST_lcsSimilarity function did not recognize time intervals specified in days.

Fixed an issue where the ST_AsEWKB function failed to convert trajectories built from data tables.

Fixed an issue where the ST_BuildPyramid function consumed excessive memory when building a vector pyramid from a very large dataset.

Category

Feature

Description

References

New feature

pgAudit plugin

Supports the pgAudit plugin for generating audit logs.

pgAudit

Support for DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

The DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function returns the formatted content of the call stack from where an exception was thrown.

DBMS_UTILITY

Support for modifying data types of a base table with a view

You can directly modify the column data types of a base table if a simple view is built on it.

ALTER TABLE

Support for DML from subquery

Supports using subqueries in DML statements, such as INSERT, UPDATE, and DELETE.

• INSERT

• UPDATE

• DELETE

DML support for global partitioned index scan

Supports GLOBAL PARTITIONED INDEX SCAN in DML operations.

DML support for GLOBAL PARTITIONED INDEX SCAN

Case insensitivity

Adds a case insensitivity feature. When enabled, the system treats unquoted, all-uppercase, and all-lowercase identifiers for database objects as equivalent.

Case insensitivity

New Oracle built-in functions

adds the following Oracle built-in functions:

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

• wm_concat

• bitand/bitor

• to_dsinterval/to_yminterval

None

Plugins installed by default

installs the following plugins by default:

• polar_from_tz

• polar_nlssort

• polar_regexp_like

• polar_utility

• sys_guid

• polar_constraint

• polar_parameter_check

• polar_worker

• roaringbitmap

• polar_csn

None

Category

Parameter

Description

References

New feature

plan_cache_mode

Adds the plan_cache_mode parameter, which allows you to set the plan cache mode. The default value is auto. Valid values:

• auto: Automatically selects between the custom and generic modes.

• force_custom_plan: Forces the optimizer to use a custom plan.

• force_generic_plan: Forces the optimizer to use a generic plan from the plan cache.

None

Category

Parameter

Description

References

New feature

ST_makeTrajectory function

Converts table rows to a trajectory.

ST_makeTrajectory

ST_AsTable function

Converts a trajectory to a table.

ST_AsTable

Fixed issue

Fixed an issue that prevented the automatic deletion of anonymous temporary tables during raster data operations.

Category

Feature

Description

References

New

Support for DBMS_SQL.define_array

The DBMS_SQL package now includes the DEFINE_ARRAY stored procedure.

DBMS_SQL

Support for using both %TYPE and := to specify default values

You can now specify default values in a stored procedure by using both %TYPE and the := assignment operator.

Use %TYPE in variable declarations

Global Partitioned Index

You can now create a Global Partitioned Index on a partitioned table.

Create a GLOBAL INDEX

pg_hint_plan

Adjusts execution plans using hints in special comment statements.

pg_hint_plan

Optimization

Optimization of the resource manager's memory release process

• Adds the mem_release_policy parameter, which specifies the memory release policy. Default: default. Valid values:

  • default: If a database's resource usage exceeds its limit, the resource manager activates an adaptive policy to release memory. The system first releases memory from idle processes, and then from active processes. This value cannot be combined with other values.

  • cancel_query: Cancels queries only for active processes.

  • terminate_idle_backend: Terminates idle user processes.

  • terminate_any_backend: Terminates any user process.

  • none: Takes no action.

• Adds the total_memcontext_dump_rate parameter, which specifies the output threshold for memory context information. Default: 75. Range: 20 to 95. The change takes effect after a restart or configuration reload.

• Adds the max_proc_mcxtdump_num parameter, which specifies the number of processes for which to output memory context information. Default: 1. Range: 1 to 100. The change takes effect after a restart or configuration reload.

• Adds the interval_max_num parameter, which specifies the number of intervals for outputting memory context information. Default: 0. Range: 0 to INT32_MAX. The change takes effect after a restart or configuration reload.

N/A

Category

Feature

Description

References

New feature

Implicit type conversion

Adds support for implicit type conversion for compatibility with the Oracle implicit conversion matrix. This feature also supports converting high-precision numeric types to low-precision types.

None

PX Analyze feature

• Adds the polar_px_enable_explain_allstat parameter. In parallel queries, you can use this parameter with the explain analyze command to print execution details for each worker, including the number of tuples processed and the time consumed.

• Adds the polar_px_explain_memory_verbosity parameter. In parallel queries, you can use this parameter with the explain analyze command to print the memory usage of each worker. Valid values:

  • summary: Displays a summary of worker memory usage.

  • detail: Displays detailed memory usage for each worker.

None

Support for changing column types in global temporary tables

Now supports ALTER statements for global temporary tables (GTTs).

None

Support for Clang compilation

Now supports compilation with Clang.

None

Category

Feature

Description

References

New feature

Object storage service paths

You can now access raster data from self-managed MinIO servers.

Object storage service path

ST_PointValues function

Adds the ST_PointValues function for raster data, returning the pixel values of all bands.

ST_PointValues

Fixed issues

Corrected the band order when using the ST_Clip function for raster data.

Improved the performance of raster data updates by adding a missing primary key index.

The system now retries reading trajectory data from OSS upon failure.

Category

Feature

Description

References

New feature

GanosBase now supports the PostgreSQL 14 series.

None

Enhancement

Enhanced the cache for trajectory objects in external storage to improve access performance.

None

Fixed issues

Ensured that file extensions are included when exporting raster data to OSS.

Fixed statistics collection failures for some raster data.

Category

Parameter

Description

References

New

Drop flashback for tables

Introduces the drop flashback feature, which lets you quickly restore deleted tables, view the recycle bin, and clear its contents.

Drop flashback

OPT_PARAM hint enhancement

The enhanced OPT_PARAM hint lets you specify parameter values at the SQL statement level.

OPT_PARAM Hints

Parallel query for multi-level partitioned tables

Supports parallel query for multi-level partitioned tables.

Use parallel query on partitioned tables

DML operations with GLOBAL INDEX

Enables the use of GLOBAL INDEX SCAN in DML operations.

GLOBAL INDEX SCAN for DML operations

Category

Parameter

Description

References

New

ST_LinearStretch function

Introduces the ST_LinearStretch function, which performs a linear stretch on a raster and returns the result as a new raster.

ST_LinearStretch

ST_BuildPercentiles function

Calculates the pixel value percentiles for a raster.

ST_BuildPercentiles

ST_ComputeStatistics function

Computes raster statistics, histograms, and percentiles in a single operation.

ST_ComputeStatistics

ST_Percentile function

Retrieves the pixel value percentiles for a raster.

ST_Percentile

Improved

Parallel computing

The ST_SummaryStats and ST_BuildHistogram functions now support parallel computing.

N/A

ST_ExportTo function

The ST_ExportTo function now returns false instead of an error if the destination file already exists.

N/A

ST_ImportFrom function

The ST_ImportFrom function is enhanced to return detailed error information on import failures, simplifying troubleshooting.

ST_ImportFrom

Fixed

Prevents the loss of half a pixel from rounding errors when cropping a raster.

Resolves errors that occur during the import and statistics collection of raster data containing values such as NAN and INF.