New Features in 8.3.0.100

SQL

Last access time of a table object can be recorded.

• Added the enable_save_dataaccess_timestamp parameter to specify whether to record the last access time of a table.
• Added the last_reference_timestamp field to the PGXC_STAT_OBJECT view. It can be used to query the last access time of a table.
• Added the pg_stat_get_all(regclass) and pgxc_stat_get_all(regclass) functions to return table tuple information on CNs recorded in pg_stat_object.

• "GUC Parameters" > "Statistics During the Database Running" > "Query and Index Statistics Collector" in the Developer Guide
• "System Catalogs and Views" > "System Catalogs" > "PGXC_STAT_OBJECT" in the Developer Guide
• "Functions and Operators > Statistics Information Functions" in SQL Syntax Reference

Security

OneAccess authentication

• Added OneAccess authentication to the rolauthinfo field of PG_AUTHID.
• Added the following description to the AUTHINFO parameter: When the OneAccess authentication mode is supported, authinf must contain oneaccessClientId and domain information in the format of 'oneaccessClientId=xxxx, domain=xxxx'.

• "System Catalogs" > "PG_AUTHID" in the Developer Guide
• "DDL Syntax" > "CREATE ROLE" in SQL Syntax Reference

Usability

pg_job can record error information.

Added the PG_JOB_INFO system catalog to record the execution results of scheduled tasks.

"System Catalogs and Views" > "System Catalogs" > "PG_JOB_INFO" in the Developer Guide

High Performance

Pooler can connect to the standby node.

The GUC parameter enable_connect_standby is added to set the CN to connect to the standby DN.

"GUC Parameters" > "Connection and Authentication" > "Communication Library Parameters" in the Developer Guide

CMC

Sunset of historical system catalog and system function interfaces

Users can learn about the product evolution through the sunset plan. This improves product experience, and improves product reliability and usability.

Added section "Metadata Sunset Description" to summarize sunsetted views and functions.

HA

Enhanced O&M views

Added functions:

1. pgxc_get_xlog_stats(), which runs on CNs and the types and numbers of Xlogs on DNs from the DN startup time.

2. pgxc_get_wal_speed(), which runs on CNs and obtains the WAL generation rate of each DN and the receive, write, flush, and redo rates of the standby DN.

3. pg_xlog_display_one_lsn(start_lsn), which runs on CNs or DNs and parses the Xlog in the current location based on the start LSN.

4. pg_xlogdump (tablename), which runs on CNs or DNs and parses and filters Xlog files based on table names.

5. pg_xlogdump (xid), which runs on CNs or DNs and parses and filters Xlog files based on transaction IDs.

6. pg_xlogdump (start_lsn, end_lsn), which runs on CNs or DNs and parses Xlog files based on the start and end LSNs.

"Functions and Operators" > "Statistics Information Functions" in SQL Syntax Reference

HA

Clearing residual files

Added the following functions to scan residual files:

• pg_scan_residualfiles()
• pgxc_scan_residualfiles(query_flag)
• pg_get_scan_residualfiles()
• pgxc_get_scan_residualfiles(query_flag)
• pg_archive_scan_residualfiles()
• pgxc_archive_scan_residualfiles(query_flag)
• pg_rm_scan_residualfiles_archive()
• pgxc_rm_scan_residualfiles_archive(query_flag)

"Functions and Operators" > "Residual File Management Functions" > "Functions for Scanning Residual Files" in SQL Syntax Reference

High Performance

Column-store CU level-2 partitions

Added the secondary_part_column and secondary_part_num parameters to specify the column names and number of level-2 partition columns in a column-store table.

"Hybrid Data Warehouse" > "Hybrid Data Warehouse Syntax" > "CREATE TABLE" in the Developer Guide

High Performance

Table-level oldestxmin

Added the GUC parameter enable_table_level_oldestxmin to control whether to enable the table-level oldestxmin feature.

Added the GUC parameter old_txn_threshold. When the table-level oldestxmin is calculated, transactions whose running duration exceeds the value of this parameter are regarded as long transactions.

"GUC Parameters" > "Automatic Cleanup" in the Developer Guide

Foreign tables

sql on hudi

Added the Hudi system function hudi_set_sync_commit to set the start timestamp of the Hudi automatic synchronization task.

Added the cow_improve parameter is added to optimize the COPY_ON_WRITE table. This parameter can be specified only when format is set to hudi.

"Functions and Operators" > "Hudi System Functions" in SQL Syntax Reference

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

Lock

Distributed deadlock detection

Added the GUC parameters enable_global_deadlock_detector and

global_deadlock_detector_period to specify whether to enable the distributed deadlock detection function and the detection interval.

"GUC Parameters" > "Lock Management" in the Developer Guide

Real-time queries

Approximate computing: column-store vectorized functions based on the HLL algorithm

Added the approx_count_distinct(col_name) function.

Added the approx_count_distinct_precision parameter, which indicates the number of buckets in the HyperLogLog++ (HLL++) algorithm. This parameter can be used to adjust the error rate of the approx_count_distinct aggregate function.

"Functions and Operators" > "Aggregate Functions" in SQL Syntax Reference

"GUC Parameters" > "Developer Options" in the Developer Guide

Real-time queries

Subplan vectorization

Added the GUC parameter vector_engine_strategy to control the vectorization enhancement policy.

"GUC Parameters" > "Miscellaneous Parameters" in the Developer Guide

SQL

Funnel functions and retention functions

Added the funnel functions window_funnel, retention, range_retention_count, and range_retention_sum.

"Functions and Operators" > "Funnel and Retention Functions" in SQL Syntax Reference

SQL

Job backpressure

Added the GUC parameter max_queue_statements to set the maximum queue length of queuing jobs.

"GUC Parameters" > "Resource Management" in the Developer Guide

SQL

Releasing locks at query end

Added the GUC parameter enable_release_scan_lock to control whether to release the level-1 lock after the SELECT statement is executed.

"GUC Parameters" > "Lock Management" in the Developer Guide

SQL

Replication tables

Added disable_update_returning_check to the GUC parameter behavior_compat_options.

"GUC Parameters" > "Miscellaneous Parameters" in the Developer Guide

Replicability

Optimized the rounding rule of the cast function.

Added options for the GUC parameter behavior_compat_options: enable_banker_round: specifies how numeric types round their values, using either the rounding or the banker method.

"GUC Parameters" > "Miscellaneous Parameters" in the Developer Guide

SQL

string_agg behavior compatibility

Added the enable_full_string_agg option to the GUC parameter behavior_compat_options.

"GUC Parameters" > "Miscellaneous Parameters" in the Developer Guide

Import and export

Upgraded the ORC third-party library and optimized its performance.

Added the GUC parameter dfs_max_memory to specify the maximum memory that can be occupied during ORC export.

"GUC Parameters" > "Resource Consumption" > "Memory" in the Developer Guide

Partition management

The COPY statement spilling behavior is optimized.

Added the GUC parameter default_partition_cache_strategy to control the default policy of partition cache.

"GUC Parameters" > "Resource Management" in the Developer Guide

Hybrid data warehouse

Batch upsert is optimized for hstore tables.

Added the GUC parameter enable_hstore_keyby_upsert, which controls the optimization of batch upsert in the hstore table.

"Hybrid Data Warehouse" > "Hybrid Data Warehouse GUC Parameters" in the Developer Guide

Partition management

The automatic partition reducing operation of automatic partition management blocks real-time data import to the database.

Added the pg_partition_management_time function to modify the invoking time of the automatic partition increasing tasks.

"Functions and Operators" > "Database Object Functions" > "Partition Management Functions" in SQL Syntax Reference

Compatibility

Integer division result compatible with PG (added in 8.3.0.100)

The enable_int_division_by_truncate option is added to the GUC parameter behavior_compat_options to control whether the result set is an integer or a floating point number. The behavior is compatible with PostgreSQL or ORA.

"GUC Parameters" > "Version and Platform Compatibility" in the Developer Guide

Compatibility

New parameter that controls whether the case when condition can contain functions that return multiple result sets.

Added the unsupported_set_function_case option to the GUC parameter behavior_compat_options.

Specifies whether the case when condition can contain functions that return multiple result sets.

"GUC Parameters" > "Version and Platform Compatibility" in the Developer Guide

Compatibility

The window function last_value supports the ignore nulls feature.

The LAST_VALUE function supports the IGNORE NULLS syntax.

"Functions and Operators" > "Window Functions" in SQL Syntax Reference

Performance

LEFT JOIN estimation (added in 8.3.0.100)

The GUC parameter left_join_estimation_enhancement is added to determine whether to use the optimized estimated number of rows for left join. The default value is off.

"GUC Parameters" > "Query Planning" > "Optimizer Method Configuration" in the Developer Guide

SQL syntax enhancement

Clearing and rebuilding indexes in the column-store index area

(added in 8.3.0.100)

The WITHOUT UNUSABLE syntax option is added.

• ALTER INDEX index_name REBUILD [ PARTITION partition_name ] [WITHOUT UNUSABLE ]
• ALTER TABLE REBUILD PARTITION partition_name [ WITHOUT UNUSABLE ]
• REINDEX { INDEX | [INTERNAL] TABLE } name [ FORCE | WITHOUT UNUSABLE ]
• REINDEX { INDEX | [INTERNAL] TABLE } name PARTITION partition_name [ FORCE | WITHOUT UNUSABLE ]

"ALTER INDEX/ALTER TABLE/REINDEX" in SQL Syntax Reference

Deleted

1

policy_oid

Previously, the value of the policy_oid field was NULL post-upgrade to version 8.3.0. Now, a script automatically populates this field during the upgrade process.

Modified

2

gs_dump

gs_dump includes the unusable status of indexes.

3

pg_get_tabledef

pg_get_tabledef includes the unusable status of indexes.

4

pg_get_indexdef

pg_get_indexdef carries the unusable status of the index.

5

create table like

When copying an index using the INCLUDE clause, the new index inherits the unusable status from the source index.

Modified

1

CREATE TABLE

New support scenario: create <common table >like <partition table >INCLUDING INDEXES. The index creation logic has been updated to set the index type based on the actual table type, not the 'like' table type.

Added

2

last_value ignore nulls

The last_value ignore nulls syntax is introduced to exclude NULL values during calculations.

3

ALTER INDEX index_name REBUILD [ PARTITION partition_name ] [WITHOUT UNUSABLE ]

Enhanced syntax to rebuild unusable indexes.

4

ALTER TABLE REBUILD PARTITION partition_name [ WITHOUT UNUSABLE ]

Enhanced syntax to rebuild unusable indexes.

5

REINDEX { INDEX | [INTERNAL] TABLE } name [ FORCE | WITHOUT UNUSABLE ]

Enhanced syntax to rebuild unusable indexes.

6

REINDEX { INDEX | [INTERNAL] TABLE } name PARTITION partition_name [ FORCE | WITHOUT UNUSABLE ]

Enhanced syntax to rebuild unusable indexes.

7

CREATE INDEX ... [ UNUSABLE ] ... PARTITION index_partition_name [ UNUSABLE ]

Added support for creating indexes in the unusable.

Modified

1

pg_stat_object

Renamed the reserved field extra1 to last_autovacuum_csn.

2

gs_wlm_session_info

Added a new field parse_time.

3

pg_job_info

Added a new column jobdb to store the database information of jobs.

Added

1

dbms_job.submit_node

Added a new function dbms_job.submit_node to designate the execution node, which is defaulted to null, representing the current CN node.

2

dbms_job.change_node

Added a new function dbms_job.change_node to designate the execution node, which is defaulted to null, representing the current CN node.

3

Funnel function

Added the funnel function windowfunnel.

4

Retention function

Added retention functions.

5

Retention extension function

Added the retention extension function range_retention_count.

6

Retention extension function

Added the retention extension function range_retention_sum.

7

approx_count_distinct

Added an approximate calculation aggregate function.

8

pg_scan_residualfiles

Scans all residual file records in the database where the current node resides

9

pgxc_scan_residualfiles

Scans all nodes for the residual files of the current database

10

pg_get_scan_residualfiles

Obtains all residual file records of the current node.

11

pgxc_get_scan_residualfiles

Obtains residual file records on all nodes.

12

pg_archive_scan_residualfiles

Archives all residual file records of the current node.

13

pgxc_archive_scan_residualfiles

Archives residual file records on all nodes.

14

pg_rm_scan_residualfiles_archive

Deletes files from the archived file list on the current node.

15

pgxc_rm_scan_residualfiles_archive

Deletes files in the archive directory from all nodes.

16

pg_partition_management_time

Modifies the invoking time of the auto-increment partition task.

17

uniq

Assists in UV calculation of data, such as precise deduplication.

18

reload_cold_partition

Changes a cold partition into a hot partition.

Modified

19

pgxc_get_small_cu_info

Added the feature of querying the number of CUs in each level-2 partition.

20

pgxc_get_wlm_session_info_bytime

Added the parse_time field.

Deleted

21

pg_sync_cstore_delta(text)

Deprecated the system function.

22

pg_sync_cstore_delta

Deprecated the system function.

23

pgxc_pool_check

Deprecated the system function.

24

pg_delete_audit

Deprecated the system function.

25

pg_log_comm_status

Deprecated the system function.

26

pgxc_log_comm_status

Deprecated the system function.

27

signal_backend

Deprecated the system function.

28

pg_stat_get_realtime_info_internal

Deprecated the system function.

29

pg_stat_get_wlm_session_info_internal

Deprecated the system function.

30

pg_stat_get_wlm_session_info

Deprecated the system function.

31

pg_stat_get_wlm_statistics

Deprecated the system function.

32

pg_user_iostat

Deprecated the system function.

33

pg_stat_get_wlm_session_iostat_info

Deprecated the system function.

Added

1

pgxc_memory_debug_info

This view displays the memory error information of each node in the current cluster during job execution, helping locate memory errors.

Modified

2

pgxc_stat_object

Changed with pg_stat_object. The reserved field extra1 is renamed last_autovacuum_csn.

3

gs_wlm_session_statistics

Added the parse_time field.

4

pgxc_wlm_session_statistics

Added the parse_time field.

5

gs_wlm_session_history

Added the parse_time field.

6

pgxc_wlm_session_history

Added the parse_time field.

7

gs_wlm_session_info

Added the parse_time field.

8

information_schema.tables

Modified the view definition and changed the filter criteria so that the views whose reloptions is empty can be queried.

9

gs_table_stat

Optimized the query performance of the gs_table_stat view.

10

gs_row_table_io_stat

Optimized the query performance of equivalence logic rewriting.

11

gs_column_table_io_stat

Optimized the query performance of equivalence logic rewriting.

Deleted

12

gs_wlm_session_info_all

Deprecated the system view.

13

pg_wlm_statistics

Deprecated the system view.

14

pg_session_iostat

Deprecated the system view.

Added

1

max_queue_statements

Default: -1 (unlimited queue length). Triggers an error and exits the job when the queue length exceeds this value. This parameter does not take effect for newly installed and upgraded clusters.

2

job_retention_time

Maximum days to store pg_job execution results. Default: 30 days.

3

vector_engine_strategy

Controls vectorization of the operator. Default: improve (maximize vectorization). Alternate: force (roll back to row storage plan).

4

enable_release_scan_lock

Determines if the SELECT statements release the level-1 lock post-execution. It is disabled by default.

5

job_queue_naptime

Interval for scheduling task checks and starting task threads. Default: 1 second.

6

approx_count_distinct_precision

Number of buckets in HLL++ algorithm, affecting the error rate of the approx_count_distinct function. The number of buckets affects the precision of estimating the distinct value. More buckets make the estimation more accurate. Default: 17 buckets.

7

llvm_compile_expr_limit

Limits the number of compiled LLVM expressions.

8

llvm_compile_time_limit

Sets a threshold for LLVM compilation time as a percentage of executor running time, triggering an alarm if exceeded.

9

max_opt_sort_rows

Maximum rows for optimized limit+offset in an ORDER BY statement. If the number of rows exceeds the value of this parameter, the original logic is used. If the number of lines is less than the value of this parameter, the optimized logic is used. Default: 0 (use original logic).

10

dfs_max_memory

Maximum memory for ORC export. Default: 262144 KB.

11

default_partition_cache_strategy

Default policy for partition cache control.

12

enable_connect_standby

Allows CN to connect to a standby DN for O&M operations. This parameter applies only to O&M operations.

13

enable_stream_sync_quit

Determines synchronous exit of stream thread after plan execution. This parameter is disabled by default so that sync quit is not blocked.

14

full_group_by_mode

Behavior after enabling disable_full_group_by_mysql:

• nullpadding indicates that NULL values in a non-aggregation column are replaced with values and non-null values in the column are used. The result set may contain different rows.
• notpadding indicates that NULL values are not processed for non-aggregation columns and the entire row of data is obtained. The result set of non-aggregation columns is a random row.

Modified

15

behavior_compat_options

Added the alter_distribute_key_by_partition option to control INSERT INTO execution by partition post ALTER TABLE.

16

behavior_compat_options

Added the enable_full_string_agg option for full or incremental aggregation logic in string_agg(a, delimiter) over (partition by b order by c).

17

behavior_compat_options

Added the unsupported_set_function_case option to control case when conditions with multi-result set functions.

18

behavior_compat_options

Added the enable_unknown_datatype option, disallowing creation of tables with unknown type columns if unset.

19

behavior_compat_options

Added the enable_whole_row_var option, intercepting scenarios where a table name is used as an expression (SELECT T FROM T, or SELECT .... FROM T GROUP BY T) if unset.

20

time_track_strategy

Parameter level changed from USERSET to SIGHUP. The default value is timer at the kernel level, which can be set to tsc on the management console.

21

rewrite_rule

Controls whether to use case when rewriting.