Hologres:Default behavior changes

Default behavior changes in Hologres V2.1 released in March 2024

When you use Realtime Compute for Apache Flink to consume Hologres binary logs, the HoloHub mode is no longer supported, and the 'sdkMode'='holohub' configuration becomes invalid. Only the Java Database Connectivity (JDBC) mode is supported. The JDBC mode is more stable than the HoloHub mode and supports more data types. Before you upgrade your Hologres instance to V2.1, use one of the following solutions to check the Realtime Compute for Apache Flink deployment and your Hologres instance and ensure that the Realtime Compute for Apache Flink deployment can run as expected. For more information, see Use JDBC to consume Hologres binary logs.

• Solution 1: Recommended. Upgrade the VVR version of Realtime Compute for Apache Flink to 8.0.6 or later, and then upgrade your Hologres instance. In this case, Realtime Compute for Apache Flink automatically changes the HoloHub mode to the JDBC mode.

• Solution 2: Upgrade the VVR version of Realtime Compute for Apache Flink to a version that ranges from 6.0.7 to 8.0.5, add the 'sdkMode'='jdbc' configuration for the source table of Real-time Compute for Apache Flink, and then restart the deployment. Grant one set of the following permissions to the user account that is used to log on to the Hologres instance. After you confirm that the deployment runs properly, upgrade your Hologres instance.

  • Superuser permissions on the Hologres instance

  • The permissions of the table owner, the CREATE DATABASE permission, and the permissions of the replication role of the Hologres instance

• Optional. If you have a large number of VVR-based Realtime Compute for Apache Flink deployments, you can obtain information about the deployments and tables by following the instructions in Use Realtime Compute for Apache Flink or Blink to consume Hologres binary logs in real time.

Default behavior changes in Hologres V2.1 released in February 2024

In Hologres V2.1.19, the multiplication and division of data of the DECIMAL type are corrected. In versions earlier than V2.1.19, a maximum of 18 decimal places are supported for basic operations on data of the DECIMAL type. If the calculation result has more than 18 decimal places after the multiplication or division, the data is truncated before the calculation. As a result, the calculation result is invalid.

For example, the multiplication operation is performed on two decimal values. The total number of digits before and after the decimal point (precision_ans) and the number of decimal places (scale_ans) in the multiplication result of the two decimal values are calculated by using the following formulas:

precision_ans = precision_l + precision_r
scale_ans  = scale_l + scale_r

If the value of scale_ans is greater than 18, only 18 decimal places are reserved. The decimal values that are used for multiplication are truncated to the specified number of decimal places based on the following rules:

• If scale_l ≤ 9, the related decimal value is not truncated, and the number of decimal places for the decimal value in the multiplication operation is scale_l.

• If scale_l > 9, the related decimal value is truncated to the number of decimal places returned by max(9,18-scale_r).

• If scale_r ≤ 9, the related decimal value is not truncated, and the number of decimal places for the decimal value in the multiplication operation is scale_r.

• If scale_r > 9, the related decimal value is truncated to the number of decimal places returned by max(9,18-scale_l).

The following sample code provides an example:

CREATE TABLE t (a decimal(30,10), b decimal(30,10));
INSERT INTO t VALUES (1.1111111111, 1.0000000000),(1.1111111112, 1.0000000000);

SELECT a, b , a*b FROM t;

• In versions earlier than V2.1.19, if a multiplier has more than 9 valid decimal places, the multiplier is truncated to the specified number of decimal places, which negatively affects the precision. As a result, the calculation result is invalid.

  -- The calculation result in Hologres is invalid.
  1.1111111111, 1.0000000000,1.111111111000000000
  1.1111111112, 1.0000000000,1.111111111000000000
  
  -- The calculation result in PostgreSQL is valid.
  1.1111111111, 1.0000000000,1.111111111100000000
  1.1111111112, 1.0000000000,1.111111111200000000

• In Hologres V2.1.19 and later, this issue is fixed. The multiplication operation is performed on the original decimal values, and then the multiplication result is truncated to the specified number of decimal places. This ensures high accuracy of the result.

  -- The calculation result is valid.
  1.1111111111, 1.0000000000,1.111111111100000000
  1.1111111112, 1.0000000000,1.111111111200000000

Default behavior changes in Hologres V2.1 released in October 2023

Specific features are optimized.

• In Hologres V2.1.12 and later, if you use the fixed plan feature to write data to a column of the DECIMAL type, the precision is not specified, and the precision of the source data is higher than that of the destination column, Hologres rounds the source data based on the precision of the destination column. In Hologres V2.1.11 and earlier, Hologres truncates the source data based on the precision of the destination column. Sample statements:

  Note

  In Hologres V2.1.12 and later, the process is the same no matter whether the fixed plan feature is used or not.

  create table fixed_plan_decimal (col decimal(3,2));
  
   - In Hologres V2.1.12 and later, the data 2.56 is written. In versions earlier than V2.1.12, the data 2.55 is written.
  insert into fixed_plan_decimal values (2.555);
  
  -- In all Hologres versions, the data 2.55 is written.
  insert into fixed_plan_decimal values (2.554);

• In Hologres V2.1 and later, the create table with property syntax is supported to simplify the configuration of table properties. For more information, see Overview.

• In Hologres V2.1 and later, the compaction policy for DELETE and UPDATE operations is modified. After the modification, tagged files on which the DELETE operation is performed are reclaimed in a timely manner. In scenarios in which the DELETE and UPDATE operations are frequently performed on column-oriented tables, the storage space may be reduced and the query performance may be improved.

• In Hologres V2.1 and later, a mechanism is added to check whether all columns specified in CONFLICT in the INSERT INTO <table_name> ON CONFLICT(<col_name>,...) DO syntax are primary key columns. If any column is not a primary key column, the SQL statement execution fails. For more information, see INSERT ON CONFLICT(UPSERT).

• In Hologres V2.1 and later, the dlf_fdw extension is created by default for you to access data lakes by using foreign tables. You do not need to manually create the dlf_fdw extension. The dlf_region parameter does not need to be specified when you create a foreign server. Only the dlf_endpoint, oss_endpoint, and dlf_catalog parameters need to be specified. A format verification is added for the dlf_endpoint and oss_endpoint parameters to prevent errors. Format requirements:

  • dlf_endpoint: dlf-share.<naition>-<region>.aliyuncs.com

  • oss_endpoint:

    • OSS bucket: oss-<naition>-<region>-internal.aliyuncs.com

    • OSS-HDFS bucket: oss-<naition>-<region>.oss-dls.aliyuncs.com

• The following keywords are used as non-reserved keywords: system_time, proctime, and dynamic. You cannot use non-reserved keywords as column names in SQL statements, and can only use them as aliases. You must use non-reserved keywords after AS. Sample statements:

  -- In Hologres V2.0 and earlier, the three keywords can be used as column names or aliases in SQL statements.
  select xxxx  system_time from t;
  
  select xxxx  as system_time from t;
  
  -- In Hologres V2.1 and later, the three keywords can only be used as aliases in SQL statements.
  select xxxx  as system_time from t;
  

Default behavior changes in Hologres V2.0 released in June 2023

Specific features are optimized.

• When you use Realtime Compute for Apache Flink to consume Hologres binary logs, the JDBC mode is supported. The HoloHub mode specified by the 'sdkMode'='holohub' configuration will be discontinued in phases. The JDBC mode is more stable than the HoloHub mode and supports more data types. Before you upgrade your Hologres instance to V2.0, use one of the following solutions to check the Realtime Compute for Apache Flink deployment and your Hologres instance and ensure that the Realtime Compute for Apache Flink deployment can run as expected. For more information, see Use JDBC to consume Hologres binary logs.

  • Solution 1: Recommended. Upgrade the VVR version of Realtime Compute for Apache Flink to 8.0.6 or later, and then upgrade your Hologres instance. In this case, Realtime Compute for Apache Flink automatically changes the HoloHub mode to the JDBC mode.

  • Solution 2: Upgrade the VVR version of Realtime Compute for Apache Flink to 8.0.4 or 8.0.5 and restart the deployment. Grant one set of the following permissions to the user account that is used to log on to the Hologres instance. After you confirm that the deployment runs properly, upgrade your Hologres instance.

    • Superuser permissions on the Hologres instance

    • The permissions of the table owner, the CREATE DATABASE permission, and the permissions of the replication role of the Hologres instance

  • Solution 3: Upgrade the VVR version of Realtime Compute for Apache Flink to a version that ranges from 6.0.7 to 8.0.3, and then upgrade your Hologres instance. In this case, Realtime Compute for Apache Flink still uses the HoloHub mode to consume Hologres binary logs.

• The remote procedure call (RPC) mode that is specified by the 'sdkMode'='rpc' or 'rpcMode'='true' configuration is no longer supported by dimension tables and result tables of Realtime Compute for Apache Flink. The JDBC mode is used instead. Before you upgrade your Hologres instance to V2.0, perform the following operations to check the Realtime Compute for Apache Flink deployment and your Hologres instance and ensure that the Realtime Compute for Apache Flink deployment can run as expected. For more information, see Hologres connector.

  • If the VVR version of Realtime Compute for Apache Flink is 6.0.7 or later, the system automatically changes the RPC mode to the JDBC mode. No operation is required.

  • If the VVR version of Realtime Compute for Apache Flink is from 6.0.3 to 6.0.6, change the 'sdkMode'='rpc' configuration to 'sdkMode'='jdbc' or change the 'rpcMode'='true' configuration to 'rpcMode'='false' for Realtime Compute for Apache Flink deployments.

  • If the VVR version of Realtime Compute for Apache Flink is 6.0.2 or earlier, change the 'rpcMode'='true' configuration to 'rpcMode'='false' for Realtime Compute for Apache Flink deployments.

  • If connections to your Hologres instance are insufficient, we recommend that you configure the connectionPoolName parameter to share connections in the connection pool. You can also upgrade the VVR version of Realtime Compute for Apache Flink to 6.0.7 or later and use the 'sdkMode'='jdbc_fixed' configuration. In this case, no connection is occupied.

  • The RPC mode does not deduplicate data with the same primary key in the same batch. The JDBC mode automatically deduplicates the data. If you need to retain complete data in business scenarios, use the 'jdbcWriteBatchSize'='1' configuration to prevent deduplication.

• In Hologres V2.0 and later, you cannot use Blink to perform real-time point queries on data in Hologres or write data to Hologres. We recommend that you migrate your Blink deployments to Realtime Compute for Apache Flink before you upgrade your Hologres instance.

Default behavior changes in Hologres V2.0 released in April 2023

Specific features are optimized.

• In Hologres V2.0 and later, data in the segment format cannot be stored in column-oriented storage mode. Hologres instances that contain data in the segment format cannot be upgraded to V2.0 or later. You can use the hg_convert_segment_orc function to convert multiple tables in the segment format into tables in the Optimized Row Columnar (ORC) format at the same time. For more information, Update the data storage format of existing column-oriented tables.

• Upper limits are imposed on the numbers of shards for a table group and for an instance. This helps prevent resource waste caused by misuse of table groups. For more information, see User guide of table groups and shard counts.

• Data is written to DataHub in JDBC mode instead of the SDK mode. The JDBC mode is more stable than the SDK mode and supports more data types.

• By default, the binary log extension is configured. When you consume binary log data in JDBC mode, you do not need to create the binary log extension. If you consume binary log data in JDBC mode, the maximum number of walsenders that can be used is increased by 10 times. For an instance that is configured with 32 CPU cores, the maximum number of walsenders is increased from 200 to 2,000. Walsenders are counted as slots. For more information, see Use JDBC to consume Hologres binary logs.

• The public preview of the backup and restoration feature and the tiered storage feature is complete, and the features can be used in production environments.

• The public preview of the share-level replication feature is complete, and the feature can be used in production environments. For more information, see Shard-level replication for high throughout.

• The parameters that specify table properties are standardized. A new syntax is used to configure table properties if column names contain uppercase letters. For more information, see Overview. The value auto is not supported for the bitmap_columns property. This change does not affect the use of existing tables.

• The HG_CREATE_TABLE_LIKE function can inherit created indexes, columns of the SERIAL data type, and columns for which proxima vector indexes are created.

Default behavior changes in Hologres V1.3 released in June 2023

• In Hologres V1.3.53 and later, the replica count must be less than or equal to the number of worker nodes. For more information, see Shard-level replication for high throughout.

• The computing result of the Avg function is optimized. In Hologres versions earlier than V1.3, the Avg function returns the computing result with a maximum of six decimal places. If the computing result contains more than six decimal places, the computing result is truncated. In Hologres V1.3 and later, the Avg function returns the computing result with complete decimal places.

• The value of the TEXT type that is converted from a value of the DECIMAL type is optimized. In Hologres versions earlier than V1.3.46, the value of the TEXT type that is converted from a value of the DECIMAL type is displayed in scientific notation format. In Hologres V1.3.46 and later, the value of the TEXT type that is converted from a value of the DECIMAL type is directly displayed. Sample SQL statements:

  create table t (a int, b decimal(38,10));
  insert into t values (1,1);
  insert into t values (1,0);
  select a,b,b::text from t;
  
  -- In Hologres V1.3.46 or later, the following result is returned:
  a	|  b	        |b
  --+-------------+------
  1 |0.0000000000	|0.0000000000
  1 |1.0000000000	|1.0000000000
  
  
  -- In versions earlier than Hologres V1.3.46, the following result is returned:
  a	|  b	        |b
  --+-------------+------
  1 |0.0000000000	|0.E-10
  1 |1.0000000000	|1.0000000000

Default behavior changes in Hologres V1.3 released in February 2023

Specific features are optimized.

In Hologres V1.3.36 and later, you can create a view across schemas by using the schema-level permission model (SLPM). For more information, see Use the SLPM.

Default behavior changes in Hologres V1.3 released in January 2023

Specific features are optimized.

• In Hologres V2.0 and later, tables in the segment format are no longer supported. In Hologres V1.3.35 and later, a statement is provided to convert multiple tables in the segment format into tables in the ORC format at the same time. This conversion facilitates the migration of data in existing tables. For more information, see Update the data storage format of existing column-oriented tables. Hologres instances that contain data in the segment format cannot be upgraded to a later version.

• In Hologres V1.3.35 and later, more Grand Unified Configuration (GUC) parameters for the fixed plan feature are set to on by default. This improves the system usability and performance. For more information, see Accelerate the execution of SQL statements by using fixed plans.

Default behavior changes in Hologres V1.3 released in December 2022

Specific features are optimized.

• From December 26, 2022 on, Alibaba Cloud no longer provides virtual private cloud (VPC) endpoints for new instances, including the instances that are created by using the backup or restoration feature. You can specify VPC endpoints that are more secure. The VPC endpoint that you specify is connected to only the VPC that you select when you purchase a Hologres instance. This provides better security and isolation. Existing instances are not affected. We recommend that you disable the original VPC endpoint and specify a VPC endpoint instead.

• In Hologres V1.3.31 and later, you can encrypt data in Hologres and query encrypted data from MaxCompute by default. You no longer need to configure additional settings or submit a ticket. For more information, see Encrypt data in Hologres and Query MaxCompute data encrypted based on BYOK.

Default behavior changes in Hologres V1.3 released in November 2022

Default behavior changes in Hologres V1.3 released in October 2022

Default behavior changes in Hologres V1.3 released in September 2022

Default behavior changes in Hologres V1.3 released in July 2022

Default behavior changes in Hologres V1.1 released in July 2022

Default behavior changes in Hologres V1.1 released in April 2022

The memory usage is optimized.

In Hologres V1.1.53 and later, the memory usage is optimized. O&M metrics are also reported in an optimized manner at the backend. This occupies fewer memory resources and leaves more memory resources for computing. You can update the version of your Hologres instance to V1.1.53 based on your business requirements.

Default behavior changes in Hologres V1.1 released in March 2022

The performance of point queries that are initiated by using fixed plans is optimized.

In Hologres V1.1.49 and later, the performance of point queries initiated by using fixed plans is optimized. In point queries that involve a large amount of data, the throughput is improved by more than 30%. You can update the version of your Hologres instance to V1.1.49 or later based on your business requirements. For more information about fixed plans, see Accelerate the execution of SQL statements by using fixed plans.

Default behavior changes in Hologres V1.1 released in March 2022

Properties are verified when a child table is attached to a parent table.

In Hologres V1.1.42 and later, the properties of a child table and a parent table are strictly verified after you initiate a request to attach the child table to the parent table. If a property of the child table is inconsistent with that of the parent table, an error is reported and the attaching operation fails. The properties that the system verifies include primary keys, indexes, and the NOT NULL constraint. Before you create a child table, make sure that the properties of the child table are consistent with those of the parent table. For more information, see CREATE PARTITION TABLE.

-- In this example, the following data definition language (DDL) statements are executed to create a parent table and a child table.
BEGIN;
CREATE TABLE public.hologres_parent(
  a int,
  b text not null,
  c timestamptz not null,
  ds text,
  PRIMARY KEY(a,ds)
)
 PARTITION BY LIST(ds);
CALL set_table_property('public.hologres_parent', 'orientation', 'column');
CALL set_table_property('public.hologres_parent', 'distribution_key', 'a');
CALL set_table_property('public.hologres_parent', 'clustering_key', 'b');
CALL set_table_property('public.hologres_parent', 'event_time_column', 'c');

CREATE TABLE public.hologres_child PARTITION OF public.hologres_parent FOR VALUES IN('20201103');

COMMIT;

-- Create a temporary child table.
BEGIN;
CREATE TABLE IF NOT EXISTS public.tmp_hologres_child(
  a int,
  b text not null,
  c timestamptz not null,
  ds text,
  PRIMARY KEY (a,ds)
) ;
CALL set_table_property('public.tmp_hologres_child', 'orientation', 'column');
CALL set_table_property('public.tmp_hologres_child', 'distribution_key', 'a');
CALL set_table_property('public.tmp_hologres_child', 'clustering_key', 'a,b');
CALL set_table_property('public.tmp_hologres_child', 'event_time_column', 'c');
COMMIT;

-- Import data from a foreign table to the temporary child table.
insert into public.tmp_hologres_child select * from foreign_table where ds='20201103';

-- Delete the original child table and attach the temporary child table to the parent table.
BEGIN;
DROP TABLE IF EXISTS  public.hologres_child;
ALTER TABLE public.tmp_hologres_child RENAME TO hologres_child;
ALTER TABLE public.hologres_parent ATTACH PARTITION public.hologres_child
FOR VALUES IN ('20201103');
COMMIT ;

-- Error cause.
ERROR: partition index hologres_child's immutable properties(e.g. clustering_key, event_time_column) is consistent with parent.
  Hint: create partition with [create table ... partition of ...] to be consistent with parent.
                

Default behavior changes in Hologres V1.1 released in December 2021

By default, the public endpoint of a new Hologres instance is disabled.

To meet the security requirements for data access control, the public endpoints of new Hologres instances are automatically disabled and Internet access capabilities are not provided by default from 00:00:00 on December 7, 2021. If you want to connect to your Hologres instance by using a public endpoint, you can enable the public endpoint in the Hologres console.

Default behavior changes in Hologres V1.1 released in November 2021

The maximum memory that is used for computing is no longer limited to 20 GB per node.

Default behavior changes in Hologres V1.1 released in October 2021

• By default, the auto-analyze feature is enabled.

  The auto-analyze feature is introduced in Hologres V0.10. This feature is verified by users online and is proven to be stable in production environments. By default, the auto-analyze feature is enabled in Hologres V1.1. The status of the auto-analyze feature remains unchanged in Hologres instances that are updated to V1.1. By default, the auto-analyze feature is enabled for new instances that are created in Hologres V1.1. The relevant parameter name is also changed in Hologres V1.1. The following table describes the changes.

  Original parameter name	New parameter name	Default value
  hg_experimental_enable_start_auto_analyze_worker	hg_enable_start_auto_analyze_worker	on

  For more information about how to use the auto-analyze feature, see ANALYZE and auto-analyze.

• The name of a function that is related to a table group is changed.

  The resharding feature is introduced in Hologres V0.10. This feature is verified by users online and is proven to be stable in production environments. In Hologres V1.1, the name of a function that is related to a table group is changed. The following table describes the changes.

  Original function name	New function name
  hg_update_table_shard_count('table_name','table_group_name')	hg_move_table_to_table_group('table_name','table_group_name')

  • For more information about how to use the resharding feature, see User guide of table groups and shard counts.

  • For more information about how to use table groups, see Best practices for specifying table groups.

• The engine for accelerated access to MaxCompute foreign tables in Hologres is changed.

  A new engine for accelerated access to MaxCompute foreign tables is introduced in Hologres V0.10. The engine improves performance by more than 30%. The engine is verified by users online and proven to be stable in production environments. In Hologres V1.1, the new engine is used as the default engine for accelerated access to MaxCompute foreign tables. The engine for accelerated access to MaxCompute foreign tables remains unchanged in Hologres instances that are updated to V1.1. For new instances that are created in Hologres V1.1, the new engine is used as the default engine. The relevant parameter names are also changed in Hologres V1.1. The following table describes the changes.

  Original parameter name	New parameter name	Remarks
  hg_experimental_enable_access_odps_orc_via_holo	hg_enable_access_odps_orc_via_holo	Default value: on.
  hg_experimental_foreign_table_executor_max_dop	hg_foreign_table_executor_max_dop	The default value is changed to the number of CPU cores of an instance. The maximum value is 128.
  None	hg_foreign_table_executor_dml_max_dop	This parameter is added in Hologres V1.1. The default value is 32. This parameter applies to DML statements that are related to foreign tables.
  hg_experimental_foreign_table_split_size	hg_foreign_table_split_size	Default value: 64. Unit: MB.
  hg_experimental_foreign_table_max_partition_limit	hg_foreign_table_max_partition_limit	The default value is 512. The value indicates that up to 512 partitions can be scanned for a query.
  hg_experimental_enable_write_maxcompute	None	In Hologres V1.1, the default value is on. The value on indicates that data can be written back to MaxCompute. For more information, see Export data to MaxCompute by executing SQL statements.

  For more information about the parameters, see Optimize the performance of querying MaxCompute tables in Hologres.

• The pg_stat_activity table records the status of active connections for all frontend nodes.

  In versions that are earlier than Hologres V1.1, the pg_stat_activity table records the status of active connections for only a single FE node. This makes it inconvenient to check and process active queries. In Hologres V1.1, the pg_stat_activity table records the status of active connections for all FE nodes. For more information about how to manage active queries by using the pg_stat_activity table, see Manage queries.

• The connection management mechanism is adjusted.

  In Hologres V1.1 and later, connections are reserved for the superuser. The logic of the HoloWeb connection pool is also optimized. This allows the superuser to connect to a Hologres instance by using HoloWeb and manage or release connections when the number of connections exceeds the maximum number of the connections supported by the instance type. For more information, see Manage connections.

• The default value of the idle_in_transaction_session_timeout parameter is changed.

  The idle_in_transaction_session_timeout parameter specifies the timeout period after a transaction enters the idle state. If you do not configure this parameter, a transaction that times out is not rolled back by default. As a result, deadlocks may occur during queries. In Hologres V1.1, the idle_in_transaction_session_timeout parameter is set to 10 minutes by default. For more information, see Manage queries.