MogDB 5.0.6

1. Version Description

MogDB 5.0.6 is a patch version of MogDB 5.0.0, released on 2024-03-30. It adds new features and fixes some defects based on MogDB 5.0.5, with the following details:

2. New Features

2.1 Ustore Storage Engine Commercial Use

The Astore storage engine, which uses the Append-Update mode, suffers from inefficient space management in large-scale high-concurrency update scenarios, leading to an order of magnitude deterioration in update latency, significantly constraining key businesses such as finance and security.

The new Ustore storage engine, which supports Inplace-Update, is officially released. It offers excellent performance in space management and high hotspot updates while maintaining consistent read-write performance with Astore, effectively addressing customer pain points.

Related Page: In-place Update Storage Engine Ustore

2.2 SELECT Auto Commit

MogDB processes all read and write requests under a transaction mechanism, which is significantly different from Oracle's behavior where reads do not start a transaction and writes implicitly start and explicitly commit a transaction. This is unfriendly for Oracle applications and long read-only connections.

This feature implements the Oracle transaction management mechanism, significantly reducing the complexity of Oracle application migration.

Related Page: SELECT Auto Commit Transaction

2.3 Import and Export Enhancements

Import and export capabilities are crucial for data backup and recovery, data migration, data synchronization, data analysis, and data exchange scenarios. Their performance directly affects the smooth progress of key businesses.

To strengthen the support of tools for business ecosystems, parallelism at both the table level and within the table has been implemented on the basis of existing gs_dump and gs_restore tools. This has resulted in several times performance improvement in common database scenarios, and even dozens of times performance improvement in scenarios with large and ultra-large tables.

Related Page: Enhanced Logical Backup and Recovery Efficiency

2.4 Compatibility Enhancements

1. Support for using string constants after group by, support for nested use of aggregate functions, compatibility with Oracle syntax

   Related Page: ORDER BY/GROUP BY Scenario Compatibility、Support for Nested Aggregate Functions

2. A new option 'accept_empty_str' has been added to the parameter behavior_compat_options, allowing users to decide whether empty strings in all data types are converted to null.

3. New versions of gsql, libpq, and odbc for AIX systems are available, allowing clients or drivers to access the database on AIX systems.

4. Compatibility with PG's INSERT...ON CONFLICT syntax, changing the INSERT action to UPDATE or DO NOTHING when a unique constraint conflict occurs to avoid errors.

   Related Page: INSERT Supports ON CONFLICT Clause

5. After setting the parameter behavior_compat_options to 'convert_string_digit_to_numeric', floating-point strings and integers can be directly operated on.

6. Support for granting stored procedures with the AUTHID CURRENT_USER attribute to other users, and the tables involved in the stored procedures are also converted to tables with the same name under the corresponding user's ownership.

   Related Page: Support for AUTHID CURRENT_USER

7. When the parameter of mod(val1, val2) function is (float, int), the implicit conversion is modified from mod(int, int) to mod(numeric, numeric), which improves the calculation precision of mod function

   Related Page: mod Function Compatibility

8. Support for ALTER SEQUENCE functionality, allowing modifications to be made after sequence creation.

9. Support for combining UPDATE/DELETE statements with the return into clause, enhancing the functionality of update/delete statements.

10. Support for cursor backward retrieval capabilities, expanding the usage scenarios of cursors.

2.5 Performance Improvements

1. During query execution, columns in subqueries that are not used by the outer query are deleted to improve query performance.

   Related Page: Support for Pruning Subquery Projection Columns

2. Support for modifying the logged attribute of a table after table creation, supporting the modification of log tables to non-log tables, not recording wal logs, and improving insertion efficiency.

   Related Page: Support for Modifying Table Log Attributes After Table Creation

3. Support for optimizing statements like 'like'xxx' and 'like'xxx%' through SET sql_beta_feature = index_like_prefix_opt; to improve execution performance.

4. Support for eliminating useless order by clauses in subqueries of queries to improve execution performance.

   Related Page: Order By Column Pruning

5. Modification of the default behavior of cte tables, allowing them to be promoted to upper queries to generate better query plans and improve execution performance.

6. A new option 'bpchar_coerce_compat' has been added to the parameter behavior_compat_options, allowing implicit conversion of text to bpchar when bpchar and text are operated on, enabling conditions like bpchar_col = 'xxx'::text to directly use indexes or partition pruning, improving query efficiency.

2.6 Usability Improvements

1. Addition of parameters such as work_mem, rewrite_rule to hints, enhancing users' ability to control the execution plan of individual statements with hints.

   Related Page: Optimizer GUC Parameters Hint

2. Support for returning the number of rows and error messages when the insert fails in BatchMode Insert, enhancing users' error identification capabilities.

3. Addition of the gs_xlog_keepers function, which is used to query the reasons for xlog cleanup within the database, helping users better clean up xlog and reduce disk space occupation.

4. Support for fields in the MERGE INTO statement without table name prefixes, enhancing SQL syntax compatibility and reducing application modifications for users.

5. Support for viewing the number of active autonomous transactions and related details, supporting the release of autonomous transactions without stopping the database, strengthening users' control over autonomous transactions.

   Related Page: Autonomous Transaction Management Views and Non-database Stopping Release of Autonomous Transactions

6. Adaptation of the wal2json wal parsing plugin, which can convert wal into json format, improving the readability of wal content and facilitating users to understand the log status of standby machines.

   Related Page: wal2json User Guide

7. Support for rollback of a single SQL error, when the behavior_compat_options parameter is set to the 'sql_implicit_savepoint' option, an error in a single SQL in a transaction does not affect the submission of other SQLs, and the submission will retain the results of correctly executed SQLs.

2.7 Stability Enhancements

1. Addition of the new parameter protect_standby, which is used to protect standby machines from being activated as the master by the switchover or failover commands, allowing users to control the promotion of designated standby machines.

2. Addition of the new parameter data_sync_failed_ignore, which is used to control the behavior of the database when data page synchronization to disk fails, whether to stop the database or keep running.

   Related Page: Corrupt Files Handling

3. Addition of the new parameter damage_page_ignore, which controls the database's ability to forcibly start during recovery when encountering damaged or missing pages, for data recovery work.

   Related Page: Corrupt Files Handling

4. CM has added support for dual-network segment deployment of stream replication, for high-availability disaster recovery at the network card level (PTK 1.4 and higher versions).

   Related Page: MogDB/CM/PTK Support for Dual Network Segments

5. CM has added a new parameter enable_async_standby_promotion to control whether asynchronous standby machines are allowed to be promoted as the master in two-node deployment modes, enhancing users' control over HA behavior.

3. Defect Fixes

1. [3098] Fixed the issue of pg_get_userbyid() having poor performance compared to PG.
2. [2949] Fixed the error "failed to find plan for subquery xx" when querying system views with explain.
3. [2952] Fixed the issue where the DDL returned by the pg_get_tabledef function could not be executed normally.
4. [3917] Fixed the error with MogDB creating temporary tables with IF NOT EXISTS.
5. [4159] Fixed the issue where the char function failed to convert specific data types in dolphin mode.
6. [3759] Fixed the issue where uint8 type and ordinary numbers could not be operated directly under B mode.
7. [3726] Fixed the issue where the definition of Hash sub-partitioned tables could not be obtained through pg_get_tabledef.
8. [3643] Fixed the error that occurred when the function or procedure name in the package was the same as the synonym name.
9. [3567] Fixed the issue where convert_string_digit_to_numeric was not effective for expressions.
10. [3566] Fixed the compatibility issue with the all/any (list) syntax under A mode.
11. [3588] Fixed the error of insufficient permissions to access the template1 database in the database log.
12. [3456] Fixed the conflict between custom packages and parameters proc_outparam_override.
13. [3401] Fixed the error with date and bigint type calculations.
14. [3328] Fixed the issue where the table creation statement exported by gs_dump was different from the original.
15. [3360] Fixed the ambiguity of the return value of dbms_utility.format_call_stack.
16. [3280] Fixed the issue of garbled characters when operating json in dolphin mode.
17. [3374] Fixed the issue where date_format did not support time zone time formats.
18. [3191] Fixed the issue where the calculation priority of the dolphin numeric operator was incorrect, leading to unexpected results.
19. [3576] Fixed the issue where the data dictionary query failed when the parameter dolphin.b_compatibility_mode was set to on.
20. [3259] Fixed the issue where the sys_guid() function built into the MogDB database only generated one piece of the same data.
21. [3045] Fixed the issue where the DDL for creating a table in MySQL mode added double quotes to the keyword 'key', but the DDL seen in the gs_dump generated SQL file and 'SHOW CREATE TABLE' did not have double quotes, causing the exported SQL file to fail when importing.
22. [3180] Fixed the issue where the gs_dump logical backup could not restore the owner and creator of the trigger.
23. [3307] Fixed the issue where the DETAIL information defined by RAISE EXCEPTION USING DETAIL was not returned for troubleshooting.
24. [3950] Fixed the issue where a type object already exists, and the prompt should indicate that the object already exists, but currently, it indicates a syntax error in the statement.
25. [3922] Fixed the issue where 'completion' could not be used as a non-reserved keyword.
26. [3419] Fixed the issue where the backup compression of gs_probackup and brm was ineffective under a custom tablespace.
27. [3367] Fixed the issue where an error occurred when using the decode function column as a correlation condition in a subquery with group by.
28. [2952] Fixed the issue where the order of the table creation statement returned by the pg_get_tabledef() function was incorrect.
29. [3053] Fixed the issue where an error occurred in the return value of Hint after enabling query_dop.
30. [3337] Fixed the issue where the "cached plan must not change result type" error occurred after modifying plan_cache_type_validation and not restarting.
31. [4006] Fixed the issue where there was a low probability of client links getting stuck after automatic failover of a database cluster configured with a VIP.
32. [3736] Fixed the issue where the CM (Cluster Manager) cluster's own failover might cause the database cluster to automatically failover in a VIP configuration scenario.
33. [3283] Fixed the issue where the parameter most_available_sync could not take effect immediately after modification and executing the reload command.
34. [3167] Fixed the issue where there was a chance of causing a query to fail to find the CSN file when using the select ... for share syntax.

4. Upgrade Instructions

If you need to upgrade your current lower version of MogDB to version 5.0.6, please be sure to read the pre-upgrade guide, and for detailed introduction on database upgrade, please refer to the upgrade guide of the latest version.