New Features in 9.1.0.100

9.1.0 feature changes

Ease of use – stability

Dynamic SQL resource pool switching based on queryId/PID

Added function: gs_switch_respool(query_id int8, resource_pool_name name), which is used to switch the resource pool of a job.

SQL Syntax Reference > Functions and Operators > System Administration Functions > Resource Management Functions

High performance

Enhanced jsonb_extract_path udf function

Added functions:

jsonb_extract((jsonb, VARIADIC text[]): If you enter any object-jsonb or array-jsonb type, the value of the path specified by $2 is returned.

jsonb_extract_text((jsonb, VARIADIC text[]): If you enter any object-jsonb or array-jsonb type, the value of the path specified by $2 is returned.

SQL Syntax Reference > Functions and Operators > JSON/JSONB Functions

Ease of use – ecosystem compatibility

CREATE SCHEMA IF NOT EXISTS

Modified content:

CREATE INDEX [IF NOT EXISTS]

CREATE SCHEMA IF NOT EXISTS schema_name

CREATE SEQUENCE [ IF NOT EXISTS ] name

SQL Syntax Reference > DDL > CREATE INDEX/CREATE SCHEMA/CREATE SEQUENCE

Memory

Memory statistics on memory context

Added functions: Query information about all chunks requested by the memory context in a specified shared memory.

• pg_shared_chunk_detail(contextname char(64))
• pv_session_chunk_detail(tid int, contextname char(64))
• pg_shared_chunk_dump(contextname char(64))
• pv_session_chunk_dump(tid int, contextname char(64))

SQL Syntax Reference > Functions and Operators > System Administration Functions > Memory Management Functions

High availability (HA)

HA dual-channel optimization

Added the GUC parameter enable_delayed_unlinks to specify whether to enable delayed checkpoint deletion.

Developer Guide > GUC Parameters > Write Ahead Logs > Checkpoints

Security

Security administrator added for data masking

Added a preset role gs_redaction_policy, which has the permission to create, modify, and delete data masking policies.

Developer Guide > Database Security Management > Managing Users and Their Permissions > Permissions Management

SQL Syntax Reference >DDL Syntax > ALTER REDACTION POLICY/CREATE REDACTION POLICY/DROP REDACTION POLICY

Security

Audit logs recording cascadingly deleted objects

If the audit_object_name_format parameter is set to all, multiple object names are displayed: DROP USER... CASCADE, DROP OWNED BY... CASCADE, DROP SCHEMA... CASSCADE, DROP TABLE... CASCADE, DROP FOREIGN TABLE... CASCADE, DROP VIEW... CASCADE. "

GUC Parameters > Auditing > Audit Switch

Security

ALTER and DROP permissions supported by functions

1. Clause that grants permissions to a function. Change the grant_on_functions_clause syntax to GRANT { { EXECUTE | ALTER | DROP } [, ...] | ALL [ PRIVILEGES ] }.
2. Clause that revokes permissions on a function. The revoke_on_functions_clause syntax is changed to REVOKE [ GRANT OPTION FOR ] { { EXECUTE | ALTER | DROP } [, ...] | ALL [ PRIVILEGES ] }.
3. Syntax that grants the function access permission to a specified role or user. GRANT { { EXECUTE | ALTER | DROP } [, ...] | ALL [ PRIVILEGES ] }
4. Syntax that revokes permissions on a specified function.

   REVOKE [ GRANT OPTION FOR ] { { EXECUTE | ALTER | DROP } [, ...] | ALL [ PRIVILEGES ] }

SQL Syntax Reference > DCL Syntax > ALTER DEFAULT PRIVILEGES /GRANT/REVOKE

Ease of use

Viewing and modifying the cache value of a sequence

Added the PG_SEQUENCES view to display the attributes of sequences on which the current user has permission.

Developer Guide > System Catalogs and System Views > System Views

Ease of use

Indexes for column-store tables supported by the stat function

The constraint that only row-store tables are counted has been removed from the seq_scan/seq_tuple_read/index_scan/index_tuple_read column in the GLOBAL_TABLE_STAT/GS_TABLE_STAT view.

Developer Guide System > Catalogs and System Views > System Views > GLOBAL_TABLE_STAT/GS_TABLE_STAT

Ecosystem compatibility

IF NOT EXISTS supported by CREATE SCHEMA/INDEX

CREATE INDEX [IF NOT EXISTS]

CREATE SCHEMA IF NOT EXISTS schema_name

CREATE SEQUENCE [ IF NOT EXISTS ] name

SQL Syntax Reference > DDL > CREATE INDEX/CREATE SCHEMA/CREATE SEQUENCE

Performance

More hint parameters supported

The following parameters are supported by GUC hints:

• enable_array_optimization
• enable_csqual_pushdown
• enable_hashfilter
• enable_mergejoin
• enable_mixedagg
• enable_seqscan
• enable_sonic_hashagg
• enable_sonic_hashjoin
• enable_tidscan
• stream_ctescan_max_estimate_mem
• stream_ctescan_pred_threshold
• stream_ctescan_refcount_threshold
• windowagg_pushdown_enhancement

Performance Tuning > SQL Tuning Guide > Tuning Using Plan Hints > Configuration Parameter Hints

Ecosystem compatibility

The issue where a "report not found" error occurs when querying the cache after deleting the schema and rebuilding the table has been fixed in PBE mode.

behavior_compat_options added an option enable_change_search_path to control whether the search path can be modified after generic_plan is formed.

Developer Guide > GUC Parameters > Version and Platform Compatibility > Platform and Client Compatibility

Performance

Top SQL added the parse_time field to indicate the total time required for statement parsing and optimization.

The parse_time field is added to the GS_WLM_SESSION_HISTORY/GS_WLM_SESSION_STATISTICS/PGXC_WLM_SESSION_INFO/PGXC_WLM_SESSION_STATISTICS view to display the total parsing time before statements are queued.

Developer Guide > System Catalogs and System Views > System Views > GS_WLM_SESSION_HISTORY GS_WLM_SESSION_STATISTICS/PGXC_WLM_SESSION_INFO/PGXC_WLM_SESSION_STATISTICS

Import and export

GDS now supports the fault tolerance parameter compatible_illegal_chars for exporting foreign tables.

GDS now supports the fault tolerance parameter compatible_illegal_chars for exporting foreign tables.

SQL Syntax Reference > DDL Syntax > CREATE FOREIGN TABLE (for GDS Import and Export)

Import and export

GDS-exported fixed-length files can be exported as a single row. Alignment modes for GDS-exported data files are supported.

You can set the eol parameter to an empty string '' to export data in FIXED format in one row.

The OPTIONS parameter added an option out_fix_num_alignment to control the alignment mode (left or right alignment) of character data and numeric data.

SQL Syntax Reference > DDL Syntax > CREATE FOREIGN TABLE (for GDS Import and Export)

High performance

Moving up subquery conditions

rewrite_rule added an option subquery_qual_pull_up to deal with subqueries that cannot be pulled up. If the subquery is associated with a column from another table and that column has filter conditions within the subquery, those filter conditions can now be extracted from the subquery and transferred to the other side of the association condition.

Developer Guide > Developer Options > rewrite_rule

High performance

Inlist hash optimization

• Added the GUC parameter enable_inlist_hashing to control whether to use inlist hash optimization.
• The default value of qrw_inlist2join_optmode is changed to disable.

Developer Guide > GUC Parameters > Query Planning > Optimizer Method Configuration

Ease of use

Enhanced scheduler functions (Hudi)

Added features: querying past synchronization information, querying synchronization task status, and resetting consecutive failed tasks

Developer Guide > SQL on Hudi > Synchronizing Hudi Tasks

Ease of use

Configurable default temporary table type (volatile)

• Added the GUC parameter default_temptable_type to specify the type of the temporary table when CREATE TABLE creating it without specifying the table type before TEMP or TEMPORARY.
• Added CREATE TABLE note: If default_temptable_type is set to local, temporary tables created without specifying keywords are local temporary tables. If default_temptable_type is set to volatile, temporary tables created without specifying keywords are volatile temporary tables.

• Developer Guide > GUC Parameters > Miscellaneous Parameters
• SQL Syntax Reference > DDL Syntax > CREATE TABLE

Compatibility

MERGE operations on specified partitions

Added the partition_clause clause, partition_clause parameter description, and constraints.

SQL Syntax Reference > DML Syntax > MERGE INTO

Compatibility

Ignoring spaces at the end of a string for comparison supported in TD-compatible mode

The CREATE DATABASE syntax added the [DBCOMPATIBILITY_BEHAVIOR [=] opt_compat_behavior] parameter.

SQL Syntax Reference > DDL Syntax > CREATE DATABASE

Compatibility

The GUC parameter controls whether the n in varchar(n) represents the maximum number of characters.

The GUC parameter behavior_compat_options added an option enable_varchar_to_nvarchar2 to control whether the varchar field created or updated using DDL statements is automatically switched to the nvarchar2 field.

Developer Guide > GUC Parameters > Version and Platform Compatibility > Platform and Client Compatibility

High performance

ANALYZE optimization

Added the EXPLAIN PERFORMANCE syntax and examples.

SQL Syntax Reference > DML Syntax > EXPLAIN

Scaling

Exclusive redistribution for certain tables during scale-in

The in_redistribution field added an option s indicating that NodeGroup will skip redistribution.

Developer Guide > System Catalogs and System Views > System Catalogs > PGXC_GROUP

High performance

Added KEEP_SAMPLE_DATA in analysis_options

The analysis_options parameter added an option KEEP_SAMPLE_DATA, indicating that the sampled data used in each analyze operation is retained in a temporary table.

Developer Guide > GUC Parameters > Developer Options

Hybrid data warehouse

Asynchronous sorting of column-store hstore tables

Added the gs_hstore_asyncsort and gs_get_sorted_cu_info functions to obtain the sequence of the target table.

Added the GUC parameters autovacuum_asyncsort_rows_limit and autovacuum_asyncsort_time_limit to obtain the number of rows and time of automatic asynchronous sorting.

• Developer Guide > Hybrid Data Warehouse > Hybrid Data Warehouse Functions
• Developer Guide > Hybrid Data Warehouse > Hybrid Data Warehouse GUC Parameters

Lakehouse

HiveMetaStore interconnection

Added the METAADDRESS and CONFIGURATION parameters to provide the HiveMetaStore communication interface and configuration file storage path.

Cross-cluster access to HiveMetaStore is supported. External schemas are created to remotely access the Hive data source of MRS.

• SQL Syntax Reference > DDL Syntax > ALTER EXTERNAL SCHEMA
• SQL Syntax Reference > DDL Syntax > CREATE EXTERNAL SCHEMA
• Developer Guide > Data Migration > Data Import

Lakehouse

Parquet write

Data can be exported as a Parquet file.

SQL Syntax Reference > DDL Syntax > CREATE FOREIGN TABLE (SQL on OBS or Hadoop)

High performance

Enhanced hot and cold tables

Added the reload_cold_partition function to support conversion between cold and hot data. Added an example that shows conversion from cold partition data to hot partition data.

• SQL Syntax Reference > Functions and Operators > Database Object Functions > Hot and Cold Table User Functions
• Developer Guide > Hot and Cold Data Management

High performance

Cache dual-write

New GUC parameters and views allow you to write column-store tables and hstore opt tables to local disks after asynchronously write them to OBS.

Developer Guide > GUC Parameters > Resource Consumption > Asynchronous I/O Operations

Developer Guide > System Catalogs and System Views > System Views

High performance

Asynchronous write service logic adaptation

Added GUC parameters and view: obs_worker_pool_size sets the maximum value of the I/O scheduler resource pool. enable_aio_scheduler controls whether to enable asynchronous I/O scheduling.

PGXC_OBS_IO_SCHEDULER_STATS queries real-time statistics about read/write requests of the OBS I/O Scheduler.

Developer Guide > GUC Parameters > Asynchronous I/O Operations

Developer Guide > System Catalogs and System Views > System Views

9.1.0.100 feature changes

Log archiving

Audit log dumping phase 1

Added GUC parameters:

• obs_audit_enabled enables or disables audit dumping.
• obs_audit_space_limit specifies the total OBS space occupied by dumped audit files.
• obs_audit_file_remain_time specifies the minimum duration for recording dumped audit logs on OBS.

Developer Guide > GUC Parameters > Auditing > Audit Switch

Upgrade

Optimized JSON function performance

Added functions:

• json_to_record_array(anyelement, array-json [, bool])
• jsonb_to_record_array(anyelement, array-json [, bool])

SQL Syntax Reference > Functions and Operators > JSON/JSONB Functions and Operators > JSON/JSONB Functions

SQL

Plan management

Added the parameter pgxc_reset_planmgmt_hashtable() to release the memory used by plan management to cache outlines on all CNs.

SQL Syntax Reference > Functions and Operators > System Administration Functions > Plan Management Functions

Real time

runtime filter

Added GUC parameters:

• runtime_filter_type specifies the runtime filter type.
• runtime_filter_ratio specifies whether the runtime filter uses the bloom filter for fine-grained row-level filtering in the join scenario.

Developer Guide > GUC Parameters > Query Planning > Other Optimizer Options

Real time

Optimized turbo engine performance

• turbo_engine_version added an option 3 to accelerate most common operators with the turbo execution engine, except for merge join and sort aggregate operators.
• Added the GUC parameter spill_compression to control the compression algorithm used when the running data of the executor operator is flushed to disks due to insufficient memory.

Developer Guide > GUC Parameters > Query Planning > Optimizer Method Configuration

I/O

Enhanced resource management I/O monitoring

• Added the GS_QUERY_RESOURCE_INFO view to display the resource information about all running jobs on the current DN.
• Added the pgxc_query_resource_info(query_id bigint) function to display resource monitoring information about the statement with a specified query ID on all DNs.

• Developer Guide > System Catalogs and System Views > System Views > GS_QUERY_RESOURCE_INFO
• SQL Syntax Reference > Functions and Operators > System Administration Functions > Resource Management Functions

Connections

Idle connection governance

Added the GUC parameter syscache_clean_policy to set the policy for clearing the idle connections and memory of DNs.

Developer Guide > GUC Parameters > Connection Pool Parameters

Exception rules

Enhanced query of filter blocking rules

• Added PG_BLOCKLISTS to record query filtering rules.
• Added the DDL syntax CREATE BLOCK RULE/ ALTER BLOCK RULE /DROP BLOCK RULE.

• Developer Guide > System Catalogs and Views > System Catalogs > PG_BLOCKLISTS
• SQL Syntax Reference > DDL Syntax

Performance

Enhanced NUMA core binding performance

Added GUC parameters:

• enable_numa_bind specifies whether to enable NUMA binding. This is enabled by default.
• numa_bind_node specifies the NUMA node where a process is bound and run after NUMA binding is enabled.

Developer Guide > GUC Parameters > Resource Consumption > Kernel Resource Usage

Disk

pgstat persistence, last access time

• Added functions: pgxc_stat_get_last_data_access_timestamp() returns the last access time of a specified table on all nodes. pgxc_stat_flush_object_data() is used to manually execute persistence.
• Added DBMS_JOBDBMS_JOB.ISUBMIT interface note: When using the dbms_job.isubmit interface, ensure that the ID is different from the ID of an existing pgstats persistence task. Otherwise, the task registration fails.

• SQL Syntax Reference > Functions and Operators > Statistics Information Functions
• Developer Guide > Stored Procedure >Advanced Package

GIS

PostGIS 3.2.2 function interface alignment

Added the content about protobuf-c compilation for the installation of the PostGIS dependency library.

Developer Guide > PostGIS Extension > PostGIS/PostGIS-3.2.2 Installation

Intelligent O&M

Enhanced ANALYZE performance

• Added GUC parameters:

  enable_expr_skew_optimization controls whether to use expression statistics in the skew optimization policy.

  analyze_predicate_column_threshold determines whether to enable analyze operations for predicate columns and specifies the allowed minimum number of columns.

  enable_runtime_analyze_concurrent controls whether concurrent runtime analyze operations can be performed on a table.

  analyze_max_columns_count specifies the maximum number of columns supported by analyze operations.

• The CREATE TABLE syntax added the incremental_analyze parameter to control whether to enable the incremental analyze mode for partitioned tables.
• Added ANALYZE | ANALYSE syntax note: If the enable_analyze_partition parameter is enabled and the table-level incremental_analyze parameter is set for a partitioned table, the ANALYZE statement is executed on partitions lacking statistics or with data changes. The statistics of the partition main table are then generated by combining the partition statistics.
• Added predicate for the ANALYZE | ANALYSE syntax. If it is enabled, predicate columns are analyzed.

• Developer Guide > GUC Parameters > Query Planning > Optimizer Method Configuration
• SQL Syntax Reference > DDL Syntax > CREATE TABLE
• SQL Syntax Reference > DCL Syntax > ANALYZE | ANALYSE

Lakehouse

Integrated read and write operations on foreign tables

Added the READ WRITE syntax to read and write foreign tables.

SQL Syntax Reference > DDL Syntax > CREATE FOREIGN TABLE (SQL on OBS or Hadoop)

Real time

Enhanced bitmap index

Added the enable_hstoreopt_auto_bitmap parameter to determine whether to automatically set bitmap columns by default when creating HStore Opt tables.

Developer Guide > Hybrid Data Warehouse > Hybrid Data Warehouse GUC Parameters

Real time

Enhanced hybrid data warehouse positioning

Added the pgxc_get_cstore_dirty_ratio function to obtain the cu, delta, and cudesc dirty page rates of the target table on each DN. Only HStore_opt tables are supported.

Developer Guide > Hybrid Data Warehouse > Hybrid Data Warehouse Functions

Decoupled storage and compute

cudesc streaming writes

The cudesc streaming path is selected by default for logical cluster access. The default value of the GUC parameter enable_cudesc_streaming is changed from off to on.

Developer Guide > GUC Parameters > Developer Options

Decoupled storage and compute

Optimized code read performance of V3 tables

The read performance of column-store 3.0 tables is optimized so that the cold read performance of column-store V3 tables deteriorates by less than 30% compared with that of full cache, improving performance competitiveness.

• Added the pgxc_clear_aio_resource_pool(force_cleanup bool) function to clear resources in asynchronous resource pools.
• Added the PGXC_AIO_RESOURCE_POOL_STATS view to query the usage status of asynchronous I/O resource pools of all nodes in a cluster.
• Added the GUC parameter cu_preload_max_distance to specify the maximum number of CU groups to prefetch. Added the GUC parameter async_io_acc_max_memory to specify the maximum memory that can be used by asynchronous read/write acceleration in a single task thread.
• Added the disk cache information in explain performance.

• SQL Syntax Reference > Functions and Operators > System Administration Functions > Other Functions
• Developer Guide > GUC Parameters > Resource Consumption > Asynchronous I/O Operations
• Developer Guide > GUC Parameters > Resource Consumption > Memory
• Developer Guide > System Catalogs and System Views > System Views > PGXC_DISK_CACHE_ALL_STATS
• Performance Tuning > SQL Execution Plan