PolarDB:2020 updates

Category

Parameter

Description

References

New features

partitioned table

Adds the Merge Partitions feature to merge two partitioned tables.

• ALTER TABLE…MERGE PARTITION

• ALTER TABLE...MERGE SUBPARTITION

Oracle-style decoding compatibility

Supports Oracle-style Base64 decoding. The BASE64_DECODE function can automatically identify malformed strings and decode only the valid leading portion of the string.

polar_enable_base64_decode

Date format

Supports inserting or updating dates in the YYYYMMDDHH24MISS format.

None

SYS.***_TAB_COLS views

Adds the SYS.***_TAB_COLS views to query information about all columns in tables and views.

• ALL_TAB_COLS

• DBA_TAB_COLS

• USER_TAB_COLS

polar_utility extension

Adds the polar_utility extension.

polar_utility

stored procedure

Stored procedures can now return a holdable refcursor.

None

DBMS_JOB package

Adds the DBMS_JOB package, which you can use with a high-privilege account to perform cross-database scheduling.

DBMS_JOB

Improvements

UTL_ENCODE

Newly created regular users can now use the UTL_ENCODE extension.

UTL_ENCODE

polar_agg extension

The polar_agg extension is enabled by default.

None

fdw extension

Enhances the stability of the fdw extension.

None

Bug fixes

Fixes an error that occurred when querying a null value using SELECT INTO in a stored procedure.

Fixes an issue where an index scan after an ALTER TABLE… EXCHANGE PARTITION operation invalidated a constraint.

Category

Parameter

Description

References

New features

DBLink

Create a DBLink from PolarDB for PostgreSQL (Oracle-compatible) to a self-managed PostgreSQL database on an ECS instance to enable cross-database data exchange from PolarDB for PostgreSQL (Oracle-compatible) to PostgreSQL. By using the DBLink feature in PostgreSQL, you can enable bidirectional data exchange between the two different databases.

On the , choose Configuration and Management > Databases.

Create a DBLink from PolarDB for PostgreSQL (Oracle Compatible) to PostgreSQL

Category

Parameter

Description

References

New features

ST_AsPNG

Adds support for returning images as a stream from a vector tile pyramid for fast graphical rendering of vector data.

ST_AsPNG

raster data

Adds the JPEG2000 compression algorithm for raster data types. This algorithm supports compressed storage for 16-bit raster data.

None

Improvements

st_dwithin

Improves the performance of st_dwithin distance queries.

None

Bug fixes

Fixes an error in time intersection calculations for trajectory data types.

Fixes a bug in the Ganos in-memory topological index.

Category

Parameter

Description

Improvements

CreateDBLink

Adds support for creating a DBLink from PolarDB for PostgreSQL (Oracle Compatible) to a self-managed PostgreSQL database on an ECS instance.

Category

Name

Description

References

New features

Compatibility

Compatible with PostgreSQL 11.9.

What is PolarDB for PostgreSQL (Oracle-compatible)?

Failover

Enables the OnlinePromote feature by default to reduce HA switchover time and improve cluster availability.

Automatic/manual failover

DBLink

You can now use a DBLink to connect to a database in another PolarDB for PostgreSQL (Oracle-compatible) cluster.

DBLink overview

Bug fixes

Fixes hangs caused by node restarts in certain scenarios.

Fixes cluster crashes caused by out-of-memory errors in certain scenarios.

Fixes read-only node crashes in certain scenarios.

Fixes an encoding conversion error from UTF-8 to GBK when running the client_encoding=gbk command.

Fixes an error that occurs when the first argument of the NVL function is NULL.

Category

Name

Description

References

New features

DBLink

You can create a DBLink from PolarDB for PostgreSQL (Oracle-compatible) to PolarDB for PostgreSQL (Oracle-compatible) to enable cross-database data access and ensure data interoperability between different PolarDB for PostgreSQL (Oracle-compatible) databases.

On the cluster details page, choose Configuration and Management > Databases.

DBLink overview

Category

Name

Description

New features

CreateDBLink

Creates a DBLink for PolarDB for PostgreSQL (Oracle-compatible).

DescribeDBLinks

Retrieves information about DBLinks for PolarDB for PostgreSQL (Oracle-compatible).

DeleteDBLink

Deletes a DBLink for PolarDB for PostgreSQL (Oracle-compatible).

Category

Name

Description

Related documents

New features

Audit log

Enables logging prepared SQL statements as executable SQL statements in the audit log.

None

Hash partitioning

Adds syntax to create a hash-partitioned table with a specified number of partitions.

CREATE TABLE ... PARTITION BY HASH

Partitioned table

Enables querying data from a specific partition or subpartition by its name.

Query a specified partition

Host parameter

Adds the host parameter, which retrieves the client's host value.

None

Improvements

View

Improves the polar_stat_activity view by adding the query_id field.

None

Compatibility

This minor kernel version is now compatible with PostgreSQL 11.8 and addresses several stability issues.

None

Bug fixes

Fixes an issue where semi-synchronous replication could not time out.

Fixes an issue where using a CONNECT BY clause with a WITH clause would fail.

Category

Name

Description

Related documents

New features

Trajectory data type

Adds GiST index support for trajectory data types and provides six operator families for different dimensions to support various analytical requirements.

None

Adds the BoxND spatiotemporal bounding box type to represent spatiotemporal ranges and store trajectories.

None

Adds intersection (&&), contains (@>), and contained by (<@) operators for different dimensions.

None

Adds four categories of trajectory processing functions: ST_ndIntersects, ST_ndDWithin, ST_ndContains, and ST_ndWithin.

• ST_{Z|T|2D|2DT|3D|3DT}Intersects

• ST_{2D|2DT|3D|3DT}DWithin

• ST_{T|2D|2DT|3D|3DT}Contains

• ST_{T|2D|2DT|3D|3DT}Within

Enables collecting statistics on the trajectory data type to estimate scan costs.

None

Introduces a new indexing method, TrajGist, to improve index selection.

None

ganos_update function

Adds the ganos_update function. Run select ganos_update() ; to upgrade all Ganos extensions to the latest version.

None

Improvements

Spatiotemporal range query

Improves the performance of spatiotemporal range queries by optimizing two-phase queries on GiST indexes.

None

Vector pyramid

Supports source data with any SRID coordinate and provides tile output for SRID 3857 and 4326.

None

Adds the pixelSize parameter to aggregate point data, reducing the number of tiles.

None

Bug fixes

Fixes an issue where updating Ganos Raster could fail in certain scenarios.

Fixes an issue where updating the Ganos binary to a new version could cause a crash.

Fixes an issue where the top-level tile contained an excessive number of points after a vector pyramid was built for point data using default parameters.

Category

Parameter

Description

Related documentation

New

GROUP_ID function

Adds the GROUP_ID() function.

GROUP_ID

Partitioned table

Adds support for creating indexes concurrently on partitioned tables by using CREATE INDEX CONCURRENTLY.

None

Invisible index

Adds support for making an index on a conventional table invisible by using ALTER INDEX [NAME] INVISIBLE.

None

ali_decoding plugin

Adds the ali_decoding plugin, which enables incremental synchronization of DML statements between databases.

None

pg_cron plugin

Adds support for querying scheduled task records for the pg_cron plugin.

pg_cron

to_char()

The to_char() function now supports string inputs.

None

Data type

The prid_ data type of ROWID can now be compared with string data types like text by using the following operators:

• <

• <=

• >

• >=

• =

• <>

None

View

Adds a view for process status information.

None

Adds execution time statistics to the SQL information view.

None

Audit log

Audit logs now include SQL statements that resulted in an error.

None

Bug fixes

Fixed an issue where data page prefetching could cause a deadlock in specific scenarios.

Fixed an issue where JOIN queries failed to find the correct partition during partition pruning for partitioned tables.

Category

Parameter

Description

Related documentation

New

Support for NetCDF data with subsets

Now supports NetCDF data that contains subsets. You can import the data by specifying a layer name.

None

Custom metadata and time-series information for raster data

Adds the ST_MetaItems function to retrieve the names of all custom metadata items.

ST_MetaItems

Adds the ST_MetaData function to retrieve custom metadata items and return them in JSON format.

ST_MetaData

Adds the ST_SetMetaData function to set metadata items.

ST_SetMetaData

Adds the ST_BeginDateTime function to retrieve the start time of raster data.

ST_BeginDateTime

Adds the ST_EndDateTime function to retrieve the end time of raster data.

ST_EndDateTime

Adds the ST_SetBeginDateTime function to set the start time for raster data.

ST_SetBeginDateTime

Adds the ST_SetEndDateTime function to set the end time for raster data.

ST_SetEndDateTime

Adds the ST_SetDateTime function to set the start time, end time, and band acquisition time for raster data.

ST_SetDateTime

Support for returning raster data as stream-based images

Adds the ST_AsImage function to retrieve raster data as a stream-based image.

ST_AsImage

Adds the ST_AsPNG function to retrieve raster data in the stream-based PNG format.

ST_AsPNG

Adds the ST_AsJPEG function to retrieve raster data in the stream-based JPEG format.

ST_AsJPEG

Support for the geometric grid data type and its operations

Adds the GeomGrid data type.

GeomGrid SQL reference

Adds the ST_AsText function to convert geometric grid data to its text representation.

ST_AsText

Adds the ST_AsGeometry function to convert geometric grid data to the geometry data type.

ST_AsGeometry

Adds the ST_AsBinary function to convert geometric grid data to a binary data type.

ST_AsBinary

Adds the ST_AsBox function to convert geometric grid data to the BOX data type.

ST_AsBox

Adds the ST_AsGrid function to convert a geometry object into its corresponding geometric grid.

ST_AsGrid

Adds the ST_GridFromText function to convert a text-based grid representation to the GeomGrid data type.

ST_GridFromText

Adds the ST_GridFromBinary function to convert a binary-based grid representation to the GeomGrid data type.

ST_GridFromBinary

Adds the ST_Intersects function to determine whether a raster data object and a geometry data object intersect.

ST_Intersects

Adds the ST_Contains function to determine if a raster object contains another raster or a geometry object.

ST_Contains

Adds the ST_Within function to determine if a grid object is within another grid or a geometry object.

ST_Within

Fast display and storage of vector data

Adds the ST_BuildPyramid function to create a data pyramid, which accelerates rendering.

ST_BuildPyramid

Adds the ST_DeletePyramid function to delete a data pyramid.

ST_DeletePyramid

Adds the ST_Tile function to retrieve vector data in Mapbox Vector Tile (MVT) format.

ST_Tile

Bug fixes

Fixed an out-of-memory issue that could occur during pyramid creation in specific scenarios.

Fixed an issue where a moving object could not be created with the 2000-01-01 timestamp.

Fixed an issue where ST_Intersection returned an incorrect sub-trajectory for a moving object in some scenarios.

Fixed an issue that could cause Ganos to crash during an upgrade.

Upgraded PostGIS compatibility to version 2.5.4.

Category

Parameter

Description

Related documentation

New

View

Added a SQL execution statistics view.

None

Added a QPS statistics view.

None

Added a CGroup status information view.

None

Privileged user

Granted the SELECT permission on dba_* views to the polar_superuser user.

None

Bug fix

Fixed a display error in the polar_stat_io_info view.

Fixed an issue with missing system wait information in AWR.

Fixed an error in partitioned table pruning.

Fixed an error in SLRU memory size calculation.

Fixed an issue of RO rebuilds caused by replay delay.

Category

Parameter

Description

Related documentation

New

COG file format support

Added support for the COG (Cloud Optimized GeoTIFF) file format, including reading pyramid information from COG files.

None

ST_AddZ function

Added the ST_AddZ function to add a z-value to a geometric object by using pixel values from raster data.

ST_AddZ

Enhanced spatial extent retrieval for raster objects

Added the ST_Extent function to return the spatial extent of a raster object as a BOX.

ST_Extent

Added the ST_Envelope function to return the spatial extent of a raster object as a geometric object.

ST_Envelope

Added the ST_ConvexHull function to return the spatial extent of a raster object as a geometric object.

ST_ConvexHull

Added the ST_Height function to retrieve the pixel height of a raster object.

ST_Height

Added the ST_Width function to retrieve the pixel width of a raster object.

ST_Width

Bug fix

Resolved a performance issue caused by a fixed 1*n chunk size for external raster data. This size is now customizable through storage options.

Fixed an issue that caused the ST_Values function to return results with an incorrect coordinate order when querying line objects in certain directions.

Prevented the ST_BestPyramidLevel function from returning a negative number.

Prevented the ST_BuildPyramid function from creating duplicate pyramids.

Ensured that using TRUNCATE on a raster table also cleans up the corresponding chunk table.

Fixed an issue where the CreateOption parameter for the ST_ExportTo function was ineffective.

Fixed an issue where the ST_ClearChunks function incorrectly handled table names that contain uppercase letters.

Fixed an issue that prevented overview creation for external pyramids.

Enabled creating an internal pyramid for a raster object that already has an external pyramid.

Fixed incorrect statistics calculations for raster data that contains NaN values.

Category

Name

Description

Related documentation

New

PIVOT syntax

The PIVOT syntax now supports using a table or subquery as an alias.

None

ROWID pseudocolumn

Supports the ROWID pseudocolumn and the ROWIDTOCHAR and CHARTOROWID functions.

None

Built-in function

Supports the LNNVL, DUMP, SYS_CONTEXT, USERENV, TZ_OFFSET, and VSIZE functions.

None

DBMS_JOB and DBMS_SCHEDULER

Supports DBMS_JOB and DBMS_SCHEDULER for maintaining and managing job queues.

None

Temporary table

Supports writing temporary table files to local storage, significantly improving the performance of temporary table operations.

None

Event trigger

The polar_superuser role can now create a logical subscription for an event trigger.

None

Custom resource isolation

Supports sending requests from custom resource isolation groups.

None

Optimization

Crash recovery process

Optimized the crash recovery process to reduce recovery time in extreme cases.

None

Bug fix

Fixed an issue where the polar_superuser role could not use the index advisor.

Category

Name

Description

Related documentation

New

Enhanced APIs for raster data metadata access

Adds the ST_XMin function for retrieving the minimum X-coordinate of raster data.

ST_XMin

Adds the ST_YMin function for retrieving the minimum Y-coordinate of raster data.

ST_YMin

Adds the ST_XMax function for retrieving the maximum X-coordinate of raster data.

ST_XMax

Adds the ST_YMax function for retrieving the maximum Y-coordinate of raster data.

ST_YMax

Adds the ST_ChunkHeight function for retrieving the chunk height of raster data.

ST_ChunkHeight

Adds the ST_ChunkWidth function for retrieving the chunk width of raster data.

ST_ChunkWidth

Adds the ST_ChunkBands function for retrieving the number of bands in a raster data chunk.

ST_ChunkBands

ST_SrFromEsriWkt function

Adds the ST_SrFromEsriWkt function, which converts an Esri-format spatial reference string to an OGC-format spatial reference string.

ST_SrFromEsriWkt

Raster data type

The raster data type now supports Zstandard (Zstd) and Snappy compression.

None

Point cloud data type

The point cloud data type now supports binary copy.

None

Environment variable

Adds support for setting the PROJ_LIB and GDAL_DATA environment variables and deploys the related data.

None

Bug fixes

Fixed an issue where an invalid OSS path could cause the database to crash.

Fixed an issue where, in some cases, the SRID of imported raster data was inconsistent with its definition.

Category

Parameter

Description

Related documentation

New feature

nvl2 function

Adds the nvl2 function, which returns the second or third argument based on whether the first argument is NULL or an empty string.

None

polar_proxy_utils extension

Adds the polar_proxy_utils extension to manage proxy-related features. This extension lets you configure read-only UDFs and read-only tables, routing requests for them to a read-only node through the cluster endpoint.

None

polar_resource_group extension

Adds the polar_resource_group extension, which supports custom resource groups to provide resource isolation based on CPU and memory usage at the user, database, and session levels.

None

Improvement

Reliability and availability

Decouples the database compute node from the file system. This allows for independent file system management, which significantly improves database reliability and availability.

None

Transaction processing

Replaces the active transaction list snapshot with a monotonically increasing version number, significantly improving database transaction processing performance.

None

Execution plan

Optimizes the execution plan to avoid using stale statistics.

None

Bug fix

Fixed a memory allocation exception in the timescaledb extension process.

Fixed a bug where I/O monitoring statistics failed to aggregate after a process exited.

Fixed a potential null pointer exception when lock_debug is enabled.

Fixed an issue that could cause the pg_cron extension to fail under specific conditions.

Fixed a known DSM deadlock.

Fixed a bug that allowed the number of user connections to exceed the configured limit.

Category

Name

Description

References

New features

TO_SINGLE_BYTE function

Adds the TO_SINGLE_BYTE function, which converts multi-byte characters in a string to their single-byte counterparts.

Full-width and half-width conversion functions (TO_SINGLE_BYTE and TO_MULTI_BYTE)

TO_MULTI_BYTE function

Adds the TO_MULTI_BYTE function, which converts single-byte characters in a string to their multi-byte counterparts.

Full-width and half-width conversion functions (TO_SINGLE_BYTE and TO_MULTI_BYTE)

REGEXP_LIKE function

Adds the REGEXP_LIKE function, which is similar to the LIKE condition but allows you to specify a POSIX-compatible regular expression pattern.

None

RATIO_TO_REPORT function

Adds the RATIO_TO_REPORT analytic function, which calculates the ratio of a value to the sum of a set of values.

None

DBMS_UTILITY.FORMAT_ERROR_STACK function

Adds the DBMS_UTILITY.FORMAT_ERROR_STACK function to format and return the current stack trace.

None

UTL_I18N.ESCAPE_REFERENCE and UTL_I18N.UNESCAPE_REFERENCE functions

Adds the UTL_I18N.ESCAPE_REFERENCE and UTL_I18N.UNESCAPE_REFERENCE functions to escape and unescape HTML or XML characters.

None

INSERT ALL syntax

Adds support for the INSERT ALL syntax, which allows you to insert data into multiple tables with a single statement.

None

polar_concurrency_control plugin

Adds the polar_concurrency_control plugin. This plugin provides concurrency control for transactions, SQL queries, stored procedures, and DML operations, letting you limit the concurrency of large queries to improve performance in high-concurrency environments.

None

pldebugger plugin

Adds the pldebugger plugin for debugging stored procedures with a client such as pgAdmin4 4.19 or later.

Use the pldebugger plugin

oss_fdw plugin

Adds the oss_fdw plugin to support external tables for Alibaba Cloud Object Storage Service (OSS). You can use OSS external tables to read data from and write data to OSS. These tables support parallel processing and compression, significantly improving data import and export performance. This feature also enables tiered storage for hot and cold data across different storage media.

Use oss_fdw to read from and write to external text files

Enhancements

ROUND function

Enhances the ROUND function to support the Interval parameter type and return values in days.

ROUND

polar_stat_activity view

Enhances the polar_stat_activity view by adding the wait_info and wait_time columns. These columns track the duration a process waits for an object and provide identifiers for that object, such as a process ID (PID) or file descriptor (fd).

None

Index

Introduces an index page pre-extension feature for index insertions. This feature improves data insertion performance for indexed tables.

None

Category

Name

Description

References

New features

MD5 operation functions

Adds the ST_MD5Sum function to retrieve the MD5 hash value of a raster object.

ST_MD5Sum

Adds the ST_SetMD5Sum function to set the MD5 hash value for a raster object.

ST_SetMD5Sum

OSS authentication for spatial raster objects

Adds the ST_AKId function to retrieve the AccessKey ID of a raster object stored in OSS.

ST_AKId

Adds the ST_SetAccessKey function to set the AccessKey ID and AccessKey Secret for a raster object stored in OSS.

ST_SetAccessKey

Adds the ST_SetAKId function to set the AccessKey ID for a raster object stored in OSS.

ST_SetAKId

Adds the ST_SetAKSecret function to set the AccessKey Secret for a raster object stored in OSS.

ST_SetAKSecret

Spatial raster metadata functions

Adds the ST_ScaleX function to retrieve the pixel width of a raster object along the X-axis in its spatial reference system.

ST_ScaleX

Adds the ST_ScaleY function to retrieve the pixel height of a raster object along the Y-axis in its spatial reference system.

ST_ScaleY

Adds the ST_SetScale function to set the pixel dimensions of a raster object within its spatial reference system.

ST_SetScale

Adds the ST_SkewX function to retrieve the rotation (skew) of a raster object along the X-axis in its spatial reference system.

ST_SkewX

Adds the ST_SkewY function to retrieve the rotation (skew) of a raster object along the Y-axis in its spatial reference system.

ST_SkewY

Adds the ST_SetSkew function to set the rotation (skew) of a raster object within its spatial reference system.

ST_SetSkew

Adds the ST_UpperLeftX function to retrieve the X-coordinate of the upper-left corner of a raster object within its spatial reference system.

ST_UpperLeftX

Adds the ST_UpperLeftY function to retrieve the Y-coordinate of the upper-left corner of a raster object within its spatial reference system.

ST_UpperLeftY

Adds the ST_SetUpperLeft function to set the coordinates of the upper-left corner of a raster object within its spatial reference system.

ST_SetUpperLeft

Adds the ST_PixelWidth function to retrieve the pixel width of a raster object.

ST_PixelWidth

Adds the ST_PixelHeight function to retrieve the pixel height of a raster object.

ST_PixelHeight

Bug fix

Fixes a plugin upgrade failure caused by an aggregate function.