Version 8.1.1

2048 nodes in a cluster

Supported up to 2048 nodes in a cluster.

Technical Specifications

Resource monitoring performance tuning

1. Added functions pgxc_wlm_get_schema_space(cstring) and pgxc_wlm_analyze_schema_space(cstring).
2. Added system views PGXC_TOTAL_SCHEMA_INFO, PGXC_TOTAL_SCHEMA_INFO_ANALYZE, and GS_WLM_SQL_ALLOW.

• Functions pgxc_wlm_get_schema_space(cstring) and pgxc_wlm_analyze_schema_space(cstring)
• System views PGXC_TOTAL_SCHEMA_INFO, PGXC_TOTAL_SCHEMA_INFO_ANALYZE, and GS_WLM_SQL_ALLOW

Lightweight UPDATE

1. Added descriptions about column-store tables and the lightweight UPDATE operation on column-store tables to "Precautions".
2. Added the GUC parameter enable_light_colupdate.

• UPDATE
• enable_light_colupdate

Primary key CU of column-store tables

Supported primary key constraints of column-store tables.

• Constraint Design
• ALTER TABLE
• CREATE INDEX

Space management

Added the GUC parameter bi_page_reuse_factor.

bi_page_reuse_factor

GDS productization

Added system views PGXC_BULKLOAD_PROGRESS, PGXC_BULKLOAD_STATISTICS, and PG_BULKLOAD_STATISTICS.

• PGXC_BULKLOAD_PROGRESS
• PGXC_BULKLOAD_STATISTICS
• PG_BULKLOAD_STATISTICS

Hot and cold data storage

1. Added the description of REFRESH STORAGE to "ALTER TABLE".
2. Added the functions and descriptions of COLVERSION and STORAGE_POLICY to "CREATE TABLE".
3. Added the function and description of the OBS tablespace to "CREATE TABLESPACE".

• ALTER TABLE
• CREATE TABLE

C function in SM4 CBC mode

Added security functions gs_encrypt(encryptstr, keystr, cryptotype, cryptomode, hashmethod), gs_decrypt(decryptstr, keystr, cryptotype, cryptomode, hashmethod), and gs_hash(hashstr, hashmethod).

Modified the description of gs_encrypt_aes128(encryptstr, keystr) and gs_decrypt_aes128(decryptstr, keystr).

Security Functions

Support for the built-in MEDIAN function

Added the function median(expression).

Aggregate Functions

Adjusting the cascade delete for tables with views

1. Added the description of rebuilding a view.
2. Added the following description to the REBUILD parameter of ALTER VIEW: Only columns of the character, number, and time types in the base table can be modified. When view_independent is set to on, views can be automatically rebuilt.

• Creating and Managing Views
• ALTER VIEW

Custom data redaction

1. Optimized data redaction.
2. Provided data redaction functions in different forms.
3. Updated the columns in the system catalog PG_REDACTION_COLUMN and system view REDACTION_COLUMNS.
4. Added the syntax ALTER REDACTION POLICY, CREATE REDACTION POLICY, and DROP REDACTION POLICY.

• Data Redaction
• Data Redaction Functions
• PG_REDACTION_COLUMN and REDACTION_COLUMNS
• ALTER REDACTION POLICY
• CREATE REDACTION POLICY
• DROP REDACTION POLICY

After the password expires, a user can log in to the database only after changing the password.

1. "Keyword" in the SQL Syntax

Added EXPIRATION (non-reserved).

2. Added the security function gs_password_expiration, and the description that the system prompts users to change their passwords after the gs_password_deadline password expires. This is related to the GUC parameter password_effect_time.

3. Added PASSWORD EXPIRATION period to the syntax ALTER ROLE, ALTER USER, and CREATE USER.

4. Added the following description to CREATE ROLE: number of days before the login password of a role expires. A user needs to change the password before it expires. If the login password expires, the user cannot log in to the system. In this case, the user needs to ask the administrator to set a new login password and use it to log in to the system.

5. Added the columns rolauthinfo, rolpwdexpire, and rolpwdtim to the system catalog PG_AUTHID.

• Keywords
• Security Functions
• ALTER ROLE, ALTER USER, and CREATE USER
• CREATE ROLE
• PG_AUTHID

Increasing the hash table size

Added the GUC parameter expand_hashtable_ratio.

expand_hashtable_ratio

SMP adaptation enabled

Added "Suggestions for SMP Parameter Settings".

Suggestions for SMP Parameter Settings

PRETTY as the default value of EXPLAIN

Changed the default display format of EXPLAIN to PRETTY.

Added the statistics of filtering and projection time to the new operator. The statistics and the operator execution time are displayed in the same row.

SQL Execution Plan Details

Enhanced concurrent SMP

1. Added the constraint of SMP: the short query scenario where the plan generation time accounts for a large proportion.
2. Added the description of setting query_dop to 1 in the short query scenario.
3. Added the GUC parameter query_dop_ratio.

• SMP Application Scenarios and Restrictions
• SMP Manual Optimization Suggestions
• query_dop_ratio

row2vec optimization

Added the GUC parameter enable_row_fast_numeric.

enable_row_fast_numeric

MySQL compatibility

1. Added the negative processing of concat, log(x), left, and right, last_day and next_day return types of int + unknown operations, and compatibility differences of the operator ^.
2. Added the keywords IFNULL and TIMESTAMPDIFF.
3. Added the description of processing CASE, COALESCE, IF, and IFNULL in MySQL-compatible mode.
4. Added the MySQL-compatible schema and example of the behavior_compat_options option.
5. [ OFFSET start [ ROW | ROWS ] ] | LIMIT start, { count | ALL } } ] supported by SELECT, SELECT INTO, and VALUES.
6. Added MySQL compatibility to the DBCOMPATIBILITY of CREATE DATABASE.
7. Added the following content: compatibility differences between concat(str1, str2), left(str text, n int), and right(str text, n int) for character processing functions and operators; compatibility differences between log(x) and ^ for numeric operation functions and operators; time and date processing functions and operators, timestampdiff(field, timestamp1, timestamp2) /to_days(timestamp) /data_format; conditional expression functions, including if(bool_expr, expr1, expr2), ifnull(expr1, expr2), and isnull(expr).

• Syntax Compatibility Differences Among Oracle, Teradata, and MySQL
• Keywords
• UNION, CASE, and Related Constructs
• behavior_compat_options
• SELECT, SELECT INTO, and VALUES
• CREATE DATABASE
• Functions and Operators

Support for UPSERT

Added the UPSERT syntax.

Added IGNORE, AS, ON DUPLICATE KEY, and ON CONFLICT to the INSERT syntax.

UPSERT

Support for the INSERT, UPDATE, and DELETE operations of views

Added Updatable Views to "CREATE VIEW".

CREATE VIEW

One-click onsite information collection

Added the STATS boolean parameter to the EXPLAIN syntax.

EXPLAIN

One-click relationship analysis of a lock wait

1. Added the lock information function pgxc_get_lock_conflicts().
2. Added the system views PGXC_DEADLOCK and PGXC_LOCK_CONFLICTS.

• System Information Functions
• System views PGXC_DEADLOCK and PGXC_LOCK_CONFLICTS

Combination with an empty string of the CHAR type

Added the configuration item bpchar_text_without_rtrim to the parameter behavior_compat_option.

behavior_compat_options

Support for Python 3.x

Supported Python 3.

Using GDS to Import Data from a Remote Server

When to_date and to_timestamp process an empty string, 0001-01-01 is returned, and null is returned for TD.

1. Added "Syntax Compatibility Differences Among Oracle, Teradata, and MySQL".
2. Added the configuration item convert_empty_str_to_null_td to behavior_compat_options.

• Syntax Compatibility Differences Among Oracle, Teradata, and MySQL
• behavior_compat_options

Support for INSERT OVERWRITE

Supported INSERT OVERWRITE.

INSERT

Obtaining all views queried by the current user

Added the view GS_VIEW_DEPENDENCY_PATH.

GS_VIEW_DEPENDENCY_PATH

Support for percentile_disc and percentile_cont

Added the following aggregate functions:

percentile_disc(const) within group(order by expression)

percentile_cont(const) within group(order by expression)

Aggregate Functions

Add

1

LIMIT offset, count

The LIMIT offset,count syntax is supported.

2

EXPLAIN(STATS ON)...

The EXPLAIN(STATS ON) syntax is supported. It is used to export information for regenerating a plan.

3

CREATE/ALTER/DROP REDACTION POLICY

Added the syntax for masking DDL statements.

4

INSERT IGNORE INTO

INSERT INTO ON DUPLICATE KEY UPDATE

INSERT INTO ON CONFLICT DO UPDATE

INSERT INTO ON CONFLICT DO NOTHING

UPSERT is supported.

5

INSERT OVERWRITE INTO

INSERT OVERWRITE is supported.

6

ALTER TABLE REFRESH STORAGE

Users can change hot data to cold data in tables.

7

ALTER VIEW ONLY view_name REBUILD

ALTER VIEW ONLY view_name REBUILD is supported.

8

ALTER SERVER REFRESH

You can update the configuration file of the HDFS server if the HDFS configuration was modified.

Modify

9

Operator

In MySQL compatibility mode, XOR is used. In ORA or TD mode, exponentiation is used.

10

MERGE INTO

An error will be reported if the target table of the MERGE INTO statement contains triggers.

11

CREATE/ALTER Table

Added options about cold_tablespace and storage_policy in Reloptions.

Add

1

TIMESTAMPDIFF

The COL_NAME_KEYWORD keyword is added.

2

IFNULL

The COL_NAME_KEYWORD keyword is added.

3

REFRESH

Non-reserved keyword

Modify

4

IF

UNRESERVED_KEYWORD is changed to COL_NAME_KEYWORD.

5

ISNULL

UNRESERVED_KEYWORD is changed to COL_NAME_KEYWORD.

6

VERIFY

This keyword is changed from reserved to non-reserved.

Modify

1

PG_REDACTION_COLUMN

The func_expr column is added.

2

PG_AUTHID

The rolpwdexpire and rolpwdtime columns are added.

Add

1

pgxc_wlm_readjust_schema_space()

You can perform parallel calibration in schema space.

2

pgxc_wlm_get_schema_space()

You can query the schema space information of the cluster.

3

pgxc_wlm_analyze_schema_space()

You can analyze the schema space information of the cluster.

4

gs_roach_enable_delay_ddl_recycle

You can enable DDL delay by specifying a backup set name.

5

gs_roach_disable_delay_ddl_recycle

You can disable DDL delay by specifying a backup set name.

6

gs_roach_stop_backup

You can disable row-store backup by specifying a backup set name.

7

gs_roach_switch_xlog

You can configure whether to perform the checkpoint operation for xlog switch.

8

pgxc_get_lock_conflicts()

This function returns information about conflicting locks on nodes.

9

mask_none/mask_full/mask_partial

A built-in masking function is added.

10

median

The median agg function is added.

11

pgxc_node_stat_reset_time()

You can query the reset time of each node.

12

pgxc_rel_iostat()

You can query the I/O statistics of each node.

13

pgxc_redo_stat()

You can query the redo statistics of each node.

14

pgxc_instance_time()

You can query the time statistics of each instance.

15

pgxc_settings()

You can query the GUC settings of each node.

16

pgxc_replication_slots()

You can query the replication slot statistics of each node.

17

pgxc_stat_replication()

You can query the replication statistics of each node.

18

pgxc_stat_bgwriter()

You can query the statistics of the bgwriter process on each node.

19

pgxc_wait_events()

You can query statistics on the wait events of each node.

20

pgxc_os_run_info()

You can query the OS performance statistics of the servers where instances are deployed.

21

get_node_stat_reset_time()

You can query the time when the current node status is reset.

22

get_local_rel_iostat()

You can query the I/O statistics of the current node.

23

get_instr_wait_event()

You can query statistics on the wait events of the current node.

24

pg_stat_get_db_total_blk_write_time()

You can query the total block write time.

25

pg_stat_get_db_total_blk_read_time()

You can query the total block read time.

26

pg_stat_get_db_total_temp_bytes()

You can query the total size of temporary files.

27

pg_stat_get_db_total_temp_files()

You can query the total number of temporary files.

28

pg_stat_get_db_total_deadlocks()

You can query the total number of deadlocks.

29

pg_stat_get_db_total_conflict_all()

You can query the total number of conflicts.

30

pg_stat_get_db_total_tuples_deleted()

You can query the total number of deleted tuples.

31

pg_stat_get_db_total_tuples_updated()

You can query the total number of updated tuples.

32

pg_stat_get_db_total_tuples_inserted()

You can query the total number of inserted tuples.

33

pg_stat_get_db_total_tuples_fetched()

You can query the total number of fetched tuples.

34

pg_stat_get_db_total_tuples_returned()

You can query the total number of returned tuples.

35

pg_stat_get_db_total_blocks_hit()

You can query the total number of hit data blocks in the memory.

36

pg_stat_get_db_total_blocks_fetched()

You can query the total number of read data blocks.

37

pg_stat_get_db_total_xact_rollback()

You can query the total number of rollback transactions.

38

pg_stat_get_db_total_xact_commit()

You can query the total number of commit transactions.

39

pg_stat_get_db_total_numbackends()

You can query the total number of backends.

40

gs_encrypt()

Encryption function

41

gs_decrypt()

Decryption function

42

gs_hash()

Hash function

43

gs_password_expiration()

You can query the remaining password validity period (rolpwdtime and rolpwdexpire columns in the pg_authid system catalog) of the current user.

44

to_char(date)

The to_char(date) function is added to be compatible with the return format of the DATE timestamp processed by to_char in TD mode.

45

gs_wlm_set_queryband_action

You can configure the query_band load behavior.

46

gs_wlm_set_queryband_order

You can configure the search priority of query_band.

47

gs_wlm_get_queryband_action

You can query the query_band load behavior.

48

CONCAT

A MYSQL compatibility rule is added. If the input parameter contains NULL, NULL will be returned.

49

TIMESTAMPDIFF

The TIMESTAMPDIFF function can be used to return the time difference in a specified unit.

50

TO_DAYS

The TO_DAYS function can return the number of days between the input time and 0.

51

DATE_FORMAT

The DATE_FORMAT function can convert an input date and time into a string in a specified format. The string is in MYSQL format and starts with %.

52

IF

The IF function can return the corresponding value based on the first input.

53

IFNULL

The IFNULL function can return the first non-NULL value.

54

ISNULL

The ISNULL function can check whether the input is NULL.

Modify

55

pg_resume_bkp_flag

You can obtain the delay DDL flag during backup and restoration.

56

pg_query_audit

The begintime, operation_type, command_text, transaction_xid and query_id fields are added to audit logs. The time field is changed to endtime, and the type field is changed to audit_type. In addition, detail_info is used to store the command execution results instead of commands, which have been moved to command_text.

57

pg_delete_audit

For security purposes, the interface for deleting audit logs cannot be provided. If this function is called, an error will be reported.

58

log()

Logarithmic function. In ORA or TD mode, it represents a logarithm to base 10. In MySQL mode, it represents a natural logarithm.

59

to_number

The to_number function instead of the numeric_in function is called. In TD mode, if the input is an empty string, null will be returned.

60

left()

You can truncate a string. If the input parameter is a negative number, -n for example, all characters except the last |n| character will be returned in ORA or TD mode, and an empty string will be returned in MySQL mode.

61

right()

You can truncate a string. If the input parameter is a negative number, -n for example, all characters except the first |n| character will be returned in ORA or TD mode, and an empty string will be returned in MySQL mode.

62

last_day

Input parameters support the timestamptz type. In MySQL mode, the return type is date. In ORA or TD mode, the return type is timestamp.

63

next_day

Input parameters support the timestamptz type. In MySQL mode, the return type is date. In ORA or TD mode, the return type is timestamp.

64

add_months

Input parameters support the timestamptz type. In MySQL mode, the return type is timestamptz, while in ORA or TD mode, it is timestamp.

65

add_months

The date type is added to the input parameter. In MySQL compatibility mode, the return type is date, whereas in ORA or TD mode, it is timestamp.

66

pg_cbm_recycle_file

This parameter is added so that the concurrent backup and disaster recovery features can recycle CBM.

67

pgxc_query_audit

The begintime, operation_type, command_text, transaction_xid and query_id fields are added to audit logs. The time field is changed to endtime, and the type field is changed to audit_type. In addition, detail_info is used to store the command execution results instead of commands, which have been moved to command_text.

68

login_audit_messages

Enhanced the audit log feature.

Delete

69

add_policy/drop_policy/alter_policy/enable_policy/disable_policy

The old data making interface was deleted.

Add

1

GS_WLM_SQL_ALLOW

You can query the existing whitelist.

2

PG_TOTAL_SCHEMA_INFO

You can query the schema space information of a node.

3

PGXC_TOTAL_SCHEMA_INFO

You can query the schema space information of a cluster.

4

PGXC_TOTAL_SCHEMA_INFO_ANALYZE

You can analyze the schema space information of the cluster.

5

PGXC_LOCK_CONFLICTS

You can query the information about conflicting locks on each node.

6

PGXC_DEADLOCK

You can query the information about lock waits in a distributed deadlock (including information about lock objects and lock holders).

7

PGXC_NODE_STAT_RESET_TIME

You can query the node reset time.

8

GS_NODE_STAT_RESET_TIME

You can check the reset time of the current node.

9

GLOBAL_STAT_DATABASE

You can query global database statistics.

10

GLOBAL_REL_IOSTAT

You can query global I/O statistics.

11

PGXC_REL_IOSTAT

You can query node I/O statistics.

12

GS_REL_IOSTAT

You can query the I/O statistics of the current node.

13

GLOBAL_REDO_STAT

You can query global redo statistics.

14

PGXC_REDO_STAT

You can query node redo statistics.

15

GLOBAL_WORKLOAD_TRANSACTION

You can query global workload transaction statistics.

16

PGXC_INSTANCE_TIME

You can query instance time statistics.

17

PGXC_SETTINGS

You can query node GUC settings.

18

PGXC_REPLICATION_SLOTS

You can query the replication slot statistics of the nodes.

19

PGXC_STAT_REPLICATION

You can query the replication statistics of the nodes.

20

PGXC_STAT_BGWRITER

You can query node bgwriter statistics.

21

PGXC_WAIT_EVENTS

You can query wait events on the nodes.

22

GS_WAIT_EVENTS

You can query wait event statistics of the current node.

23

PGXC_OS_RUN_INFO

You can query the OS performance statistics of the servers where instances are deployed.

24

PG_LIFECYCLE_DATA_DISTRIBUTE

You can query the data distribution statistics of OBS cold and hot tables.

25

PG_BULKLOAD_STATISTICS

You can encapsulate the pg_stat_get_node_bulkload_statistics function.

26

PGXC_BULKLOAD_STATISTICS

You can encapsulate the pgxc_stat_get_node_bulkload_statistics function.

27

PGXC_BULKLOAD_PROGRESS

This function can aggregate the query results of the pgxc_bulkload_statistics view and calculate the GDS service progress percentage.

28

PG_QUERYBAND_ACTION

You can query all the query_band load behaviors.

29

GS_VIEW_DEPENDENCY_PATH

You can query the dependency between cascading query views.

Modify

30

REDACTION_COLUMNS

The function_infos column is added to the system view definition.

31

PGXC_GET_TABLE_SKEWNESS

Fixed the problem that the round-robin table is not displayed in the PGXC_GET_TABLE_SKEWNESS view.

32

PGXC_STAT_DATABASE

You can query database statistics on each instance.

Modify

1

If the Not-null and CHECK constraints conflict, the details of the entire row are not printed.

If the Not-null and CHECK constraints conflict, the details of the entire row are not printed.

2

Compatible with C80 behavior. By default, the implicit type conversion from interval to text is not matched.

If behavior_compat_options is set to enable_interval_to_text, this implicit type conversion is supported.

3

Date type

In ORA mode, the date type is reversely parsed as pg_catalog.date.

4

Agg plan

Required columns are generated when agg generates a stream plan.

5

Median is used with collate.

Collate cannot be used with median, percentile_cont, or percentile_disc.

6

Modifications on multi-column partitioning

The rule to check the boundary values of multiple columns during partition modification. If the boundary value of a column is the maximum value, the boundary values of other columns are ignored.

7

Generate a plan with enable_nestloop is set to off and no association conditions.

Assume there are two tables that do not have equivalent join conditions with each other, but have equivalent join conditions with other tables. If the number of joined rows increases, the nestloop plan can be executed. After the rectification, the nestloop plan cannot be executed. For equivalent join columns that do not support hashjoin, if mergejoin is set to off, the nestloop cost will change. The execution plan will change from Nestloop + Indexscan to Nestloop + Materialize.

Add

1

behavior_compat_options

• The convert_empty_str_to_null_td option is added to be compatible with the to_date, to_timestamp, and to_number functions that return null after processing an empty string in TD mode.
• The enable_interval_to_text option is added to determine whether to support the implicit conversion from interval to text.

2

debug_group_dop

You can configure the DOP of each stream group for statement-level optimization during SMP adaptation. This value is left empty by default.

3

enable_row_fast_numeric

Numeric data in row-store tables is flushed to disks in bigint format.

4

expand_hashtable_ratio

Dynamic extension of the hash table

5

query_dop_ratio

A logic DOP makes decisions based on the system resources and cost. If the decisions are inaccurate, a parameter is added for escape. The default value is 1.

6

show_unshippable_warning

A parameter is added to determine whether to print logs that have not been pushed down to the client. By default, the function is disabled.

7

assign_abort_xid

Session-level parameters are added. If data is deleted by mistake, incomplete recovery can be performed quickly. This parameter is left blank by default. The GUC parameter indicates rolling back the transaction submitted by a specific xid.

8

bi_page_reuse_factor

Idle FSM factor during batch insert reuse (0 indicates returning to the old logic)

9

check_cu_size_threshold

Userset level. When data is inserted to a column-store table, if the amount of data inserted to a CU is greater than check_cu_size_threshold, row size will be checked to avoid generating CUs greater than 1 GB.

10

enable_light_colupdate

Userset level, indicating whether lightweight update is enabled.

Modify

11

cost_model_version

The parameter value option 2 is added. The default value 1 remains unchanged. If the value is less than 2, the original function will be used for fixed value sampling. If the value is greater than 2, the function improved to enhance randomness is used for fixed value sampling.

12

expected_computing_nodegroup

After an in-place upgrade, set expected_computing_nodegroup to bind.

13

query_dop

For a newly installed cluster, the default value is changed to 1. For an upgraded cluster, the default value remains unchanged.