Updated on 2024-11-05 GMT+08:00

Miscellaneous Parameters

enable_cluster_resize

Parameter description: Indicates whether the current session is a scale-out redistribution session. This parameter applies only to scale-out redistribution sessions. Do not set this parameter for other service sessions.

Parameter type: SUSET

Value range: Boolean

  • on indicates that the current session is for scaling or redistributing data, and allows the execution of specific SQL statements for redistribution.
  • off indicates that the current session is not for scaling or redistributing data, and does not allow the execution of specific SQL statements for redistribution.

Default value: off

This parameter is used for internal O&M. Do not set it to on unless absolutely necessary.

dfs_partition_directory_length

Parameter description: Specifies the largest directory name length for the partition directory of a table partitioned by VALUE in the HDFS.

Type: USERSET

Value range: 92 to 7999

Default value: 512

enable_hadoop_env

Parameter description: Sets whether local row- and column-store tables can be created in a database while the Hadoop feature is used. In the GaussDB(DWS) cluster, it is set to off by default to support local row- and column- based storage and cross-cluster access to Hadoop. You are not advised to change the value of this parameter.

Type: USERSET

Value range: Boolean

  • on or true, indicating that local row- and column-store tables cannot be created in a database while the Hadoop feature is used.
  • off or false, indicating that local row- and column-based tables can be created in a database while the Hadoop feature is used.

Default value: off

enable_upgrade_merge_lock_mode

Parameter description: If this parameter is set to on, the delta merge operation internally increases the lock level, and errors can be avoided when update and delete operations are performed at the same time.

Type: USERSET

Value range: Boolean

  • If this parameter is set to on, the delta merge operation internally increases the lock level. In this way, when any two of the DELTAMERGE, UPDATE, and DELETE operations are concurrently performed, an operation can be performed only after the previous one is complete.
  • If this parameter is set to off, and any two of the DELTAMERGE, UPDATE, and DELETE operations are concurrently performed to data in a row in the delta table of the HDFS table, errors will be reported during the later operation, and the operation will stop.

Default value: off

job_queue_processes

Parameter description: Specifies the number of jobs that can be concurrently executed.

Type: POSTMASTER

Value range: 0 to 1000

Functions:

  • Setting job_queue_processes to 0 indicates that the scheduled task function is disabled and that no job will be executed. (Enabling scheduled tasks may affect the system performance. At sites where this function is not required, you are advised to disable it.)
  • Setting job_queue_processes to a value that is greater than 0 indicates that the scheduled task function is enabled and this value is the maximum number of tasks that can be concurrently processed.

After the scheduled task function is enabled, the job_scheduler thread at a scheduled interval polls the pg_jobs system catalog. The scheduled task check is performed every second by default.

Too many concurrent tasks consume many system resources, so you need to set the number of concurrent tasks to be processed. If the current number of concurrent tasks reaches job_queue_processes and some of them expire, these tasks will be postponed to the next polling period. Therefore, you are advised to set the polling interval (the interval parameter of the submit interface) based on the execution duration of each task to avoid the problem that tasks in the next polling period cannot be properly processed because overlong task execution time.

Note: If the number of parallel jobs is large and the value is too small, these jobs will wait in queues. However, a large parameter value leads to large resource consumption. You are advised to set this parameter to 100 and change it based on the system resource condition.

Default value: 10

ngram_gram_size

Parameter description: Specifies the length of the ngram parser segmentation.

Type: USERSET

Value range: an integer ranging from 1 to 4

Default value: 2

ngram_grapsymbol_ignore

Parameter description: Specifies whether the ngram parser ignores graphical characters.

Type: USERSET

Value range: Boolean

  • on: Ignores graphical characters.
  • off: Does not ignore graphical characters.

Default value: off

ngram_punctuation_ignore

Parameter description: Specifies whether the ngram parser ignores punctuations.

Type: USERSET

Value range: Boolean

  • on: Ignores punctuations.
  • off: Does not ignore punctuations.

Default value: on

zhparser_dict_in_memory

Parameter description: Specifies whether Zhparser adds a dictionary to memory.

Type: POSTMASTER

Value range: Boolean

  • on: Adds the dictionary to memory.
  • off: Does not add the dictionary to memory.

Default value: on

zhparser_multi_duality

Parameter description: Specifies whether Zhparser aggregates segments in long words with duality.

Type: USERSET

Value range: Boolean

  • on: Aggregates segments in long words with duality.
  • off: Does not aggregate segments in long words with duality.

Default value: off

zhparser_multi_short

Parameter description: Specifies whether Zhparser executes long words composite divide.

Type: USERSET

Value range: Boolean

  • on: Performs compound segmentation for long words.
  • off: Does not perform compound segmentation for long words.

Default value: on

zhparser_multi_zall

Parameter description: Specifies whether Zhparser displays all single words individually.

Type: USERSET

Value range: Boolean

  • on: Displays all single words separately.
  • off: Does not display all single words separately.

Default value: off

zhparser_multi_zmain

Parameter description: Specifies whether Zhparser displays important single words separately.

Type: USERSET

Value range: Boolean

  • on: Displays important single words separately.
  • off: Does not display important single words separately.

Default value: off

zhparser_punctuation_ignore

Parameter description: Specifies whether the Zhparser segmentation result ignores special characters including punctuations (\r and \n will not be ignored).

Type: USERSET

Value range: Boolean

  • on: Ignores all the special characters including punctuations.
  • off: Does not ignore all the special characters including punctuations.

Default value: on

zhparser_seg_with_duality

Parameter description: Specifies whether Zhparser aggregates segments in long words with duality.

Type: USERSET

Value range: Boolean

  • on: Aggregates segments in long words with duality.
  • off: Does not aggregate segments in long words with duality.

Default value: off

acceleration_with_compute_pool

Parameter description: Specifies whether to use the computing resource pool for acceleration when OBS is queried.

Type: USERSET

Value range: Boolean

  • on indicates that the query covering OBS is accelerated based on the cost when the computing resource pool is available.
  • off indicates that no query is accelerated using the computing resource pool.

Default value: off

behavior_compat_options

Parameter description: Specifies database compatibility behavior. Multiple items are separated by commas (,).

Type: USERSET

Value range: a string

Default value: In upgrade scenarios, the default value of this parameter is the same as that in the cluster before the upgrade. When a new cluster is installed, the default value of this parameter is check_function_conflicts to prevent serious problems caused by incorrect function attributes defined by users.

  • Currently, only Table 1 is supported.
  • Multiple items are separated by commas (,), for example, set behavior_compat_options='end_month_calculate,display_leading_zero';
  • strict_concat_functions and strict_text_concat_td are mutually exclusive.
Table 1 Compatibility configuration items

Configuration Item

Behavior

Applicable Compatibility Mode

display_leading_zero

Specifies how floating point numbers are displayed.

  • If this item is not specified, for a decimal number between -1 and 1, the 0 before the decimal point is not displayed. For example, 0.25 is displayed as .25.
  • If this item is specified, for a decimal number between -1 and 1, the 0 before the decimal point is displayed. For example, 0.25 is displayed as 0.25.

For example, during data migration, if this parameter is not set during data import, when floating numbers are displayed or converted to strings, the leading zeros of the floating point numbers are omitted, causing an error message like this:

1
2
ERROR: xxx invalid input syntax for type xxx
DETAIL: Token "." is invalid

ORA

TD

end_month_calculate

Specifies the calculation logic of the add_months function.

Assume that the two parameters of the add_months function are param1 and param2, and that the sum of param1 and param2 is result.

  • If this item is not specified, and the Day of param1 indicates the last day of a month shorter than result, the Day in the calculation result will equal that in param1. For example:
1
2
3
4
5
SELECT add_months('2018-02-28',3) FROM dual;
add_months
---------------------
2018-05-28 00:00:00
(1 row)
  • If this item is specified, and the Day of param1 indicates the last day of a month shorter than result, the Day in the calculation result will equal that in result. For example:
1
2
3
4
5
SELECT add_months('2018-02-28',3) FROM dual;
add_months
---------------------
2018-05-31 00:00:00
(1 row)

ORA

TD

compat_analyze_sample

Specifies the sampling behavior of the ANALYZE operation.

If this item is specified, the sample collected by the ANALYZE operation will be limited to around 30,000 records, controlling CN memory consumption and maintaining the stability of ANALYZE.

ORA

TD

MySQL

bind_schema_tablespace

Binds a schema with the tablespace with the same name.

If a tablespace name is the same as sche_name, default_tablespace will also be set to sche_name if search_path is set to sche_name.

ORA

TD

MySQL

bind_procedure_searchpath

Specifies the search path of the database object for which no schema name is specified.

If no schema name is specified for a stored procedure, the search is performed in the schema to which the stored procedure belongs.

If the stored procedure is not found, the following operations are performed:

  • If this item is not specified, the system reports an error and exits.
  • If this item is specified, the search continues based on the settings of search_path. If the issue persists, the system reports an error and exits.

ORA

TD

MySQL

correct_to_number

Controls the compatibility of the to_number() result.

If this item is specified, the result of the to_number() function is the same as that of PG11. Otherwise, the result is the same as that of Oracle.

ORA

unbind_divide_bound

Controls the range check on the result of integer division.

  • If this item is not specified, the division result is checked. If the result is out of the range, an error is reported. In the following example, an out-of-range error is reported because the value of INT_MIN/(-1) is greater than the value of INT_MAX.
SELECT (-2147483648)::int / (-1)::int;
ERROR:  integer out of range
  • If this item is specified, the range of the division result does not need to be checked. In the following example, INT_MIN/(-1) can be used to obtain the output result INT_MAX+1.
SELECT (-2147483648)::int / (-1)::int;
  ?column?
------------
 2147483648
(1 row)

ORA

TD

merge_update_multi

Specifies whether to perform an update when MERGE INTO is executed to match multiple rows.

If this item is specified, no error is reported when multiple rows are matched. Otherwise, an error is reported (same as Oracle).

ORA

TD

disable_row_update_multi

Specifies whether to perform an update when multiple rows of a row-store table are matched.

If this item is specified, an error is reported when multiple rows are matched. Otherwise, multiple rows can be matched and updated by default.

ORA

TD

return_null_string

Specifies how to display the empty result (empty string '') of the lpad(), rpad(), repeat(), regexp_split_to_table(), and split_part() functions.

  • If this item is not specified, the empty string is displayed as NULL.
1
2
3
4
5
SELECT length(lpad('123',0,'*')) FROM dual;
length
--------

(1 row)
  • If this item is specified, the empty string is displayed as single quotation marks ('').
1
2
3
4
5
SELECT length(lpad('123',0,'*')) FROM dual;
length
--------
0
(1 row)

ORA

compat_concat_variadic

Specifies the compatibility of variadic results of the concat() and concat_ws() functions.

If this item is specified and a concat function has a parameter of the variadic type, different result formats in Oracle and Teradata are retained. If this item is not specified and a concat function has a parameter of the variadic type, the result format of Oracle is retained for both Oracle and Teradata.

ORA

TD

convert_string_digit_to_numeric

Specifies the type casting priority for binary BOOL operations on the CHAR type and INT type.

  • If this item is not specified, the type casting priority is the same as that of PG9.6.
  • After this item is configured, all binary BOOL operations of the CHAR type and INT type are forcibly converted to the NUMERIC type for computation.

    After this configuration item is set, the CHAR types that are affected include BPCHAR, VARCHAR, NVARCHAR2, and TEXT, and the INT types that are affected include INT1, INT2, INT4, and INT8.

CAUTION:

This configuration item is valid only for binary BOOL operation, for example, INT2>TEXT and INT4=BPCHAR. Non-BOOL operation is not affected. This configuration item does not support conversion of UNKNOWN operations such as INT>'1.1'. After this configuration item is enabled, all BOOL operations of the CHAR and INT types are preferentially converted to the NUMERIC type for computation, which affects the computation performance of the database. When the JOIN column is a combination of affected types, the execution plan is affected.

ORA

TD

MySQL

check_function_conflicts

Controls the check of the custom plpgsql/SQL function attributes.

  • If this parameter is not specified, the IMMUTABLE/STABLE/VOLATILE attributes of a custom function are not checked.
  • If this parameter is specified, the IMMUTABLE attribute of a custom function is checked. If the function contains a table or the STABLE/VOLATILE function, an error is reported during the function execution. In a custom function, a table or the STABLE/VOLATILE function conflicts with the IMMUTABLE attribute, thus function behaviors are not IMMUTABLE in this case.

For example, when this parameter is specified, an error is reported in the following scenarios:

CREATE OR replace FUNCTION sql_immutable (INTEGER)
RETURNS INTEGER AS 'SELECT a+$1 FROM shipping_schema.t4 WHERE a=1;'
LANGUAGE SQL IMMUTABLE
RETURNS NULL
ON NULL INPUT;
select sql_immutable(1);
ERROR:  IMMUTABLE function cannot contain SQL statements with relation or Non-IMMUTABLE function.
CONTEXT:  SQL function "sql_immutable" during startup
referenced column: sql_immutable

ORA

TD

MySQL

varray_verification

Indicates whether to verify the array length and array type length. Compatible with GaussDB(DWS) versions earlier than 8.1.0.

If this parameter is specified, the array length and array type length are not verified.

Scenario 1
CREATE OR REPLACE PROCEDURE varray_verification 
AS 
    TYPE org_varray_type IS varray(5) OF VARCHAR2(2); 
    v_org_varray org_varray_type; 
BEGIN 
    v_org_varray(1) := '111'; --If the value exceeds the limit of VARCHAR2(2), the setting will be consistent with that in the historical version and no verification is performed after configuring this option.
END; 
/ 
Scenario 2
CREATE OR REPLACE PROCEDURE varray_verification_i3_1 
AS 
    TYPE org_varray_type IS varray(2) OF NUMBER(2); 
    v_org_varray org_varray_type; 
BEGIN 
    v_org_varray(3) := 1; --If the value exceeds the limit of varray(2) specified for array length, the setting will be consistent with that in the historical version and no verification is performed after configuring this option.
END; 
/ 

ORA

TD

strict_concat_functions

Indicates whether the textanycat() and anytextcat() functions are compatible with the return value if there are null parameters. This parameter and strict_text_concat_td are mutually exclusive.

In MySQL-compatible mode, this parameter has no impact.

  • If this configuration item is not specified, the returned values of the textanycat() and anytextcat() functions are the same as those in the Oracle database.
  • When this configuration item is specified, if there are null parameters in the textanycat() and anytextcat() functions, the returned value is also null. Different result formats in Oracle and Teradata are retained.

If this configuration item is not specified, the returned values of the textanycat() and anytextcat() functions are the same as those in the Oracle database.

SELECT textanycat('gauss', cast(NULL as BOOLEAN));
 textanycat
------------
 gauss
(1 row)

SELECT 'gauss' || cast(NULL as BOOLEAN); -- In this case, the || operator is converted to the textanycat function.
 ?column?
----------
 gauss
(1 row)

When setting this configuration item, retain the results that are different from those in Oracle and Teradata:

SELECT textanycat('gauss', cast(NULL as BOOLEAN));
 textanycat
------------

(1 row)

SELECT 'gauss' || cast(NULL as BOOLEAN); -- In this case, the || operator is converted to the textanycat function.
 ?column?
----------

(1 row)

ORA

TD

strict_text_concat_td

In Teradata compatible mode, whether the textcat(), textanycat() and anytextcat() functions are compatible with the return value if there are null parameters. This parameter and strict_concat_functions are mutually exclusive.

  • If this parameter is not specified, the return values of the textcat(), textanycat(), and anytextcat() functions in Teradata-compatible mode are the same as those in GaussDB(DWS).
  • When this parameter is specified, if the textcat(), textanycat(), and anytextcat() functions contain any null parameter values, the return value is null in the Teradata-compatible mode.

If this parameter is not specified, the returned values of the textcat(), textanycat(), and anytextcat() functions are the same as those in the GaussDB(DWS).

td_compatibility_db=# SELECT textcat('abc', NULL);
textcat
---------
abc
(1 row)
td_compatibility_db=# SELECT 'abc' || NULL; -- In this case, the operator || is converted to the textcat() function.
?column?
----------
abc
(1 row)

When this parameter is specified, NULL is returned if any of the textcat(), textanycat(), and anytextcat() functions returns a null value.

td_compatibility_db=# SELECT textcat('abc', NULL);
textcat
---------

(1 row)
td_compatibility_db=# SELECT 'abc' || NULL;
?column?
----------

(1 row)

TD

compat_display_ref_table

Sets the column display format in the view.

  • If this parameter is not specified, the prefix is used by default, in the tab.col format.
  • Specify this parameter to the same original definition. It is displayed only when the original definition contains a prefix.
SET behavior_compat_options='compat_display_ref_table';
CREATE OR REPLACE VIEW viewtest2 AS SELECT a.c1, c2, a.c3, 0 AS c4 FROM viewtest_tbl a;
SELECT pg_get_viewdef('viewtest2');
pg_get_viewdef
-----------------------------------------------------
SELECT a.c1, c2, a.c3, 0 AS c4 FROM viewtest_tbl a;
(1 row)

ORA

TD

para_support_set_func

Whether the input parameters of the COALESCE(), NVL(), GREATEST(), and LEAST() functions in a column-store table support multiple result set expressions.

  • If this item is not specified and the input parameter contains multiple result set expressions, an error is reported, indicating that the function is not supported.
1
2
SELECT COALESCE(regexp_split_to_table(c3,'#'), regexp_split_to_table(c3,'#')) FROM regexp_ext2_tb1 ORDER BY 1 LIMIT 5;
ERROR:  set-valued function called in context that cannot accept a set
  • When this configuration item is specified, the function input parameter can contain multiple result set expressions.
1
2
3
4
5
6
7
8
9
SELECT COALESCE(regexp_split_to_table(c3,'#'), regexp_split_to_table(c3,'#')) FROM regexp_ext2_tb1 ORDER BY 1 LIMIT 5;
 coalesce
----------
 a
 a
 a
 a
 a
(5 rows)

ORA

TD

disable_select_truncate_parallel

Controls the DDL lock level such as TRUNCATE in a partitioned table.

  • If this item is specified, the concurrent execution of TRUNCATE and DML operations (such as SELECT) on different partitions is forbidden, and the fast query shipping (FQS) of the SELECT operation on the partitioned table is allowed. You can set this parameter in the OLTP database, where there are many simple queries on partitioned tables, and there is no requirement for concurrent TRUNCATE and DML operations on different partitions.
  • If this item is not specified, SELECT and TRUNCATE operations can be concurrently performed on different partitions in a partitioned table, and the FQS of the partitioned table is disabled to avoid possible inconsistency.

ORA

TD

MySQL

bpchar_text_without_rtrim

In Teradata-compatible mode, controls the space to be retained on the right during the character conversion from bpchar to text. If the actual length is less than the length specified by bpchar, spaces are added to the value to be compatible with the Teradata style of the bpchar character string.

Currently, ignoring spaces at the end of a string for comparison is not supported. If the concatenated string contains spaces at the end, the comparison is space-sensitive.

The following is an example:

td_compatibility_db=# SELECT length('a'::char(10)::text);
length
--------
10
(1 row)

td_compatibility_db=# SELECT length('a'||'a'::char(10));
length
--------
11
(1 row)

TD

convert_empty_str_to_null_td

In Teradata-compatible mode, controls the to_date, to_timestamp, and to_number type conversion functions to return null when they encounter empty strings, and controls the format of the return value when the to_char function encounters an input parameter of the date type.

Example:

If this parameter is not specified:

td_compatibility_db=# SELECT to_number('');
 to_number 
-----------
         0
(1 row)

td_compatibility_db=# SELECT to_date('');
ERROR:  the format is not correct
DETAIL:  invalid date length "0", must between 8 and 10.
CONTEXT:  referenced column: to_date

td_compatibility_db=# SELECT to_timestamp('');
      to_timestamp
------------------------
 0001-01-01 00:00:00 BC
(1 row)

td_compatibility_db=# SELECT to_char(date '2020-11-16');
        to_char         
------------------------
 2020-11-16 00:00:00+08
(1 row)

If this parameter is specified, and parameters of to_number, to_date, and to_timestamp functions contain empty strings:

td_compatibility_db=# SELECT to_number('');
 to_number 
-----------

(1 row)

td_compatibility_db=# SELECT to_date('');
 to_date 
---------

(1 row)

td_compatibility_db=# SELECT to_timestamp('');
 to_timestamp
--------------

(1 row)

td_compatibility_db=# SELECT to_char(date '2020-11-16');
  to_char   
------------
 2020/11/16
(1 row)

TD

disable_case_specific

Determines whether to ignore case sensitivity during character type match. This parameter is valid only in Teradata-compatible mode.

  • If this item is not specified, characters are case sensitive during character type match.
  • If this item is specified, characters are case insensitive during character type match.

After being specified, this item will affect five character types (CHAR, TEXT, BPCHAR, VARCHAR, and NVARCHAR), 12 operators (<, >, =, >=, <=, !=, <>, !=, like, not like, in, and not in), and expressions case when and decode.

CAUTION:

After this item is enabled, the UPPER function is added before the character type, which affects the estimation logic. Therefore, an enhanced estimation model is required. (Suggested settings: cost_param=16, cost_model_version = 1, join_num_distinct=-20, and qual_num_distinct=200)

TD

enable_interval_to_text

Controls the implicit conversion from the interval type to the text type.

  • When this option is enabled, the implicit conversion from the interval type to the text type is supported.
    SELECT TO_DATE('20200923', 'yyyymmdd') - TO_DATE('20200920', 'yyyymmdd') = '3'::text;
    ?column?
    ----------
    f
    (1 row)
  • When this option is disabled, the implicit conversion from the interval type to the text type is not supported.
    SELECT TO_DATE('20200923', 'yyyymmdd') - TO_DATE('20200920', 'yyyymmdd') = '3'::text;
    ?column?
    ----------
    t
    (1 row)

ORA

TD

MySQL

case_insensitive

In MySQL-compatible mode, configure this parameter to specify the case-insensitive input parameters of the locate, strpos, and instr string functions.

Currently, this parameter is not configured by default. That is, the input parameter is case-sensitive.

The following shows an example:

  • If this parameter is not configured, the input parameter is case-sensitive.
    mysql_compatibility_db=# SELECT LOCATE('sub', 'Substr');
     locate
    --------
          0
    (1 row)
  • If this parameter is configured, the input parameter is case-insensitive.
    mysql_compatibility_db=# SELECT LOCATE('sub', 'Substr');
     locate
    --------
          1
    (1 row)

MySQL

inherit_not_null_strict_func

Controls the original strict attribute of a function. A function with one parameter can transfer the NOT NULL attribute. func(x) is used an example. If func() is the strict attribute and x contains the NOT NULL constraint, func(x) also contains the NOT NULL constraint.

The compatible configuration item is effective in some optimization scenarios, for example, NOT IN and COUNT(DISTINCT) optimization. However, the optimization results may be incorrect in specific scenarios.

Currently, this parameter is not configured by default to ensure that the result is correct. However, the performance may be rolled back. If an error occurs, you can set this parameter to roll back to the historical version.

ORA

TD

MySQL

disable_compat_minmax_expr_mysql

Specifies the method for processing the input parameter null in the greatest/least expression in MySQL-compatible mode.

You can configure this parameter to roll back to a historical version.

  • If this parameter is not configured and the input parameter is null, null is returned.
    mysql_compatibility_db=# SELECT greatest(1, 2, null), least(1, 2, null);
     greatest | least
    ----------+-------
              |
    (1 row)
  • If this parameter is configured, the maximum or minimum value of non-null parameters is returned.
    mysql_compatibility_db=# SELECT greatest(1, 2, null), least(1, 2, null);
     greatest | least
    ----------+-------
            2 |     1
    (1 row)

MySQL

disable_compat_substr_mysql

Specifies the behavior of the substr/substring function when the start position pos is ≤ 0 in MySQL-compatible mode.

You can configure this parameter to roll back to a historical version.

  • If this parameter is not configured, that is, an empty string is returned when pos = 0. When pos < 0, TRUNCATE starts from the last |pos| character on.
    mysql_compatibility_db=# SELECT substr('helloworld',0);
     substr
    --------
    
    (1 row)
    mysql_compatibility_db=# SELECT substring('helloworld',0),substring('helloworld',-2,4);
     substring | substring
    -----------+-----------
               | ld
    (1 row)
  • If this parameter is configured and pos is ≤ 0, characters are truncated from the left.
    mysql_compatibility_db=# SELECT substr('helloworld',0);
       substr
    ------------
     helloworld
    (1 row)
    mysql_compatibility_db=# SELECT substring('helloworld',0),substring('helloworld',-2,4);
     substring  | substring
    ------------+-----------
     helloworld | h
    (1 row)

MySQL

disable_compat_trim_mysql

Specifies the method for processing the input parameter in the trim/ltrim/rtrim function in MySQL-compatible mode.

You can configure this parameter to roll back to a historical version.

  • If this parameter is not configured, the entire substring is matched.
    mysql_compatibility_db=# SELECT trim('{}{name}{}','{}'),trim('xyznamezyx','xyz');
     btrim  |  btrim
    --------+---------
     {name} | namezyx
    (1 row)
  • If this parameter is configured, a single character in the character set is matched.
    mysql_compatibility_db=# SELECT trim('{}{name}{}','{}'),trim('xyznamezyx','xyz');
     btrim | btrim
    -------+-------
     name  | name
    (1 row)

MySQL

light_object_mtime

Specifies whether the mtime column in the pg_object system catalog records object operations.

  • If this parameter is configured, the GRANT, REVOKE, and TRUNCATE operations are not recorded by mtime, that is, the mtime column is not updated.
  • If this parameter is not configured (by default), the ALTER, COMMENT, GRANT, REVOKE, and TRUNCATE operations are recorded by mtime, that is, the mtime column is updated.

ORA

TD

MySQL

disable_including_all_mysql

In MySQL-compatible mode, this parameter controls whether the CREATE TABLE...LIKE syntax is INCLUDING_ALL.

By default, this parameter is not set. That is, in MySQL compatibility mode, CREATE TABLE... LIKE syntax is INCLUDING_ALL.

Set this parameter to roll back to a historical version.

  • If this parameter is not set, in MySQL-compatible mode, the CREATE TABLE... LIKE syntax is in INCLUDING_ALL.
    mysql_compatibility_db=# CREATE TABLE mysql_like(id int, name varchar(10), score int) distribute by hash(id) COMMENT 'mysql_like';
    CREATE TABLE
    mysql_compatibility_db=# CREATE index index_like on mysql_like(name);
    CREATE INDEX
    mysql_compatibility_db=# \d+ mysql_like;
                                 Table "public.mysql_like"
     Column |         Type          | Modifiers | Storage  | Stats target | Description
    --------+-----------------------+-----------+----------+--------------+-------------
     id     | integer               |           | plain    |              |
     name   | character varying(10) |           | extended |              |
     score  | integer               |           | plain    |              |
    Indexes:
        "index_like" btree (name) TABLESPACE pg_default
    Has OIDs: no
    Distribute By: HASH(id)
    Location Nodes: ALL DATANODES
    Options: orientation=row, compression=no
    
    mysql_compatibility_db=# CREATE table copy_like like mysql_like;
    CREATE TABLE
    mysql_compatibility_db=# \d+ copy_like;
                                  Table "public.copy_like"
     Column |         Type          | Modifiers | Storage  | Stats target | Description
    --------+-----------------------+-----------+----------+--------------+-------------
     id     | integer               |           | plain    |              |
     name   | character varying(10) |           | extended |              |
     score  | integer               |           | plain    |              |
    Indexes:
        "copy_like_name_idx" btree (name) TABLESPACE pg_default
    Has OIDs: no
    Distribute By: HASH(id)
    Location Nodes: ALL DATANODES
    Options: orientation=row, compression=no
  • If this parameter is set, in MySQL-compatible mode, the CREATE TABLE... LIKE syntax is empty.
    mysql_compatibility_db=# SET behavior_compat_options = 'disable_including_all_mysql';
    SET
    mysql_compatibility_db=# CREATE TABLE mysql_copy like mysql_like;
    NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using round-robin as the distribution mode by default.
    HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
    CREATE TABLE
    mysql_db=# \d+ mysql_copy;
                                 Table "public.mysql_copy"
     Column |         Type          | Modifiers | Storage  | Stats target | Description
    --------+-----------------------+-----------+----------+--------------+-------------
     id     | integer               |           | plain    |              |
     name   | character varying(10) |           | extended |              |
     score  | integer               |           | plain    |              |
    Has OIDs: no
    Distribute By: ROUND ROBIN
    Location Nodes: ALL DATANODES
    Options: orientation=row, compression=no

MySQL

cte_onetime_inline

Indicates whether to execute inline for non-stream plans.

  • When this parameter is set, the CTE that is not in a stream plan and is referenced only once executes inline.
  • If this parameter is not set, the CTE that is not in a stream plan and is referenced only once does not execute inline.

ORA

TD

MySQL

skip_first_after_mysql

Determines whether to ignore the FIRST/AFTER colname syntax in ALTER TABLE ADD/MODIFY/CHANGE COLUMN in MySQL compatibility mode.

  • If this parameter is set, the FIRST/AFTER colname syntax is ignored and executing this syntax does not cause errors
    mysql_compatibility_db=# SET behavior_compat_options = 'skip_first_after_mysql';
    mysql_compatibility_db=# ALTER TABLE t1 add column b text after a;
    ALTER TABLE
  • If this parameter is not set, the FIRST/AFTER colname syntax is not supported, and executing this syntax causes error.
    mysql_compatibility_db=# SET behavior_compat_options = '';
    mysql_compatibility_db=# ALTER TABLE t1 add column b text after a;
    ERROR:  FIRST/AFTER is not yet supported.

MySQL

enable_division_by_zero_mysql

Specifies whether to report an error when the divisor is 0 in MySQL compatibility mode. (This configuration item is supported only by clusters of 8.1.3.110 and later versions.)

  • If this parameter is set, NULL is returned if the divisor is 0 in a division or modulo operation.
    compatible_mysql_db=# SET behavior_compat_options = 'enable_division_by_zero_mysql';
    SET
    compatible_mysql_db=# SELECT 1/0 as test;
     test 
    ----------
             
    (1 row)
  • If this parameter is not set, an error is returned if the divisor is 0 in a division or modulo operation.
    compatible_mysql_db=# SELECT 1/0;
    ERROR:  division by zero

MySQL

merge_into_with_trigger

Controls whether the MERGE INTO operation can be performed on tables with triggers. (This parameter is supported only in 8.1.3.200 and later cluster versions.)

  • When this option is set, the MERGE INTO operation can be performed on tables with triggers. When the MERGE INTO operation is performed, the trigger on the table is not activated.
  • If this option is not set, an error is reported when the MERGE INTO operation is performed on a table with triggers.

ORA

TD

MySQL

add_column_default_v_func

Controls whether expression in alter table add column default expression supports volatile functions. (This parameter is supported only in 8.1.3.200 and later cluster versions.)

  • If this option is selected, expression in alter table add column default expression supports volatile functions.
  • If this option is not selected, expression in alter table add column default expression does not support volatile functions. If expression contains volatile functions, an error will be reported during statement execution.

ORA

TD

MySQL

disable_gc_fdw_filter_partial_pushdown

Controls whether filter criteria are pushed down when filter criteria are used to query data in a collaborative analysis foreign table (type: gc_fdw). (This parameter is supported only in 8.1.3.310 and later cluster versions.)

  • When this option is selected, if the filter criteria contain elements (such as non-immutable functions) that do not meet the pushdown conditions, all filter criteria are not pushed down to ensure the normal generation of the result set document. This behavior is compatible with the behavior in versions earlier than 8.1.3.310.
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    -- Create a table in the source cluster.
    CREATE TABLE t1(c1 INT, c2 INT, c3 INT) DISTRIBUTE BY HASH(c1);
    -- Create a foreign table with the same structure in the local cluster.
    CREATE SERVER server_remote FOREIGN DATA WRAPPER gc_fdw options(ADDRESS 'address', DBNAME 'dbname', USERNAME 'username', PASSWORD 'password');
    CREATE FOREIGN TABLE t1(c1 INT, c2 INT, c3 INT) SERVER server_remote;
    -- Enable the parameter and see the pushdown behavior.
    SET behavior_compat_options = 'disable_gc_fdw_filter_partial_pushdown';
    EXPLAIN (verbose on,costs off) SELECT * FROM t1 WHERE c1>3 AND c2 <100 AND now() - '20230101' < c3;
                                                                            QUERY PLAN                                                                        
    ----------------------------------------------------------------------------------------------------------------------------------------------------------
     Streaming (type: GATHER)
       Output: c1, c2, c3
       Node/s: All datanodes
       ->  Foreign Scan on ca_schema.t1
             Output: c1, c2, c3
             Filter: ((t1.c1 > 3) AND (t1.c2 < 100) AND ((now() - '2023-01-01 00:00:00-08'::timestamp with time zone) < (t1.c3)::interval))
             Remote SQL: SELECT c1, c2, c3 FROM ca_schema.t1
    (7 rows)
    
  • If this parameter is not set, the filter criteria that can be pushed down are executed in the source cluster, and the filter criteria that cannot be pushed down are executed in the local cluster. This improves the query efficiency of foreign tables.
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    -- Disable this parameter and see the pushdown behavior.
    SET behavior_compat_options = '';
    EXPLAIN (verbose on,costs off) SELECT * FROM t1 WHERE c1>3 AND c2 <100 AND now() - '20230101' < c3;
                                                    QUERY PLAN                                                
    ----------------------------------------------------------------------------------------------------------
     Streaming (type: GATHER)
       Output: c1, c2, c3
       Node/s: All datanodes
       ->  Foreign Scan on ca_schema.t1
             Output: c1, c2, c3
             Filter: ((now() - '2023-01-01 00:00:00-08'::timestamp with time zone) < (t1.c3)::interval)
             Remote SQL: SELECT c1, c2, c3 FROM ca_schema.t1 WHERE ((c1 > 3)) AND ((c2 < 100))
    (7 rows)
    

ORA

TD

MySQL

normalize_negative_zero

Controls whether the ceil() and round() functions will produce a negative zero when dealing with certain float values. This parameter is supported only by clusters of version 8.1.3.333 and later.

  • When this parameter is set, ceil() processes (-1,0) and round() processes [-0.5, 0). The return value is 0.
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    SET behavior_compat_options='normalize_negative_zero';
    SELECT ceil(cast(-0.1 as float));
     ceil
    ------
        0
    (1 row)
    SELECT round(cast(-0.1 as FLOAT));
     round
    -------
         0
    (1 row)
    
  • If this parameter is not set, -0 is returned when ceil() processes (-1,0) and round() processes [-0.5, 0).
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    SET behavior_compat_options = '';
    SELECT ceil(cast(-0.1 as FLOAT));
     ceil 
    ------
       -0 
    (1 row)
    SELECT round(cast(-0.1 as FLOAT));
     round
    -------
        -0
    (1 row)
    

ORA

TD

MySQL

disable_client_detection_commit

Specifies whether to verify the client connection before committing each transaction. If the connection is not present, an error will be reported, and the transaction will be rolled back to prevent duplicate data delivery due to disconnection. This parameter is supported only by clusters of version 8.1.3.333 and later.

  • If this parameter is not set, the system will verify the client connection before committing each transaction.
  • If this parameter is set, the system will not verify the client connection before committing each transaction.

ORA

TD

MySQL

enable_trunc_orc_string

Controls the foreign table query behavior when the foreign table field is in ORC format and the data type is varchar(n), but the field type in the ORC file is string and the length of the string exceeds n.

This parameter is available only in clusters of version 8.1.3.336, 8.3.0.100, 910.100, and later.

  • If this parameter is not set, an error message is returned, indicating that the field is too long.
  • If this parameter is set, the query is responded to, and the result is truncated by the length defined by varchar(n).

ORA

TD

MySQL

gds_fill_multi_missing_fields

Controls the behavior when the GDS foreign table fault tolerance parameter fill_missing_fields is set to true or on. When fill_missing_fields is set to true or on in a GDS foreign table, any missing columns at the end of a row in the data source file are automatically set to NULL. Before this, only the last column in a row of the data source file can be missing without an error being reported. This parameter is available only in clusters of version 8.1.3.336, 8.2.1.200, 9.1.0.100, and later.

  • If this option is specified, the GDS foreign table tolerates the missing of multiple last columns in a row of the source data file.
  • If this option is not specified, only the missing of the last column in a row of the data source file is tolerated in the GDS foreign table. This parameter compatible with historical behavior.

ORA

TD

MySQL

internal_compat_options

Parameter description: Specifies database compatibility behavior. Multiple items are separated by commas (,). This parameter is supported only by clusters of version 8.1.3.333 and later.

Type: SIGHUP

Value range: a string

Default value: In upgrade scenarios, the default value of this parameter is the same as that in the cluster before the upgrade. In a cluster installation scenario, the default value of this parameter is empty.

Table 2 Compatibility configuration items

Configuration Item

Behavior

light_proxy_permission_compat

Nested query permission configuration item in the light proxy scenario.

  • If this parameter is not set, you must have the query permission for nested queries in the light proxy scenario.
  • Enabling this parameter allows for nested queries in the light proxy scenario, regardless of permissions.

redact_compat_options

Parameter description: Specifies the compatibility option for calculation using masked data. This parameter is supported by version 8.1.3 or later clusters.

Type: USERSET

Value range: a string

  • none indicates that compatibility options are specified.
  • disable_comparison_operator_mask indicates that comparison operators that do not expose raw data can bypass the data masking check and generate the actual calculation result.

Default value: none

table_skewness_warning_threshold

Parameter description: Specifies the threshold for triggering a table skew alarm.

Type: SUSET

Value range: a floating point number ranging from 0 to 1

Default value: 1

table_skewness_warning_rows

Parameter description: Specifies the minimum number of rows for triggering a table skew alarm.

Type: SUSET

Value range: an integer ranging from 0 to INT_MAX

Default value: 100000

auto_process_residualfile

Parameter description: Specifies whether to enable the residual file recording function.

Type: SIGHUP

Value range: Boolean

  • on indicates that the residual file recording function is enabled.
  • off indicates that the residual file recording function is disabled.

Default value: off

enable_view_update

Parameter description: Enables the view update function or not.

Type: POSTMASTER

Value range: Boolean

  • on indicates that the view update function is enabled.
  • off indicates that the view update function is disabled.

Default value: off

view_independent

Parameter description: Decouples views from tables, functions, and synonyms or not. After the base table is restored, automatic association and re-creation are supported.

Type: SIGHUP

Value range: Boolean

  • on indicates that the view decoupling function is enabled. Tables, functions, synonyms, and other views on which views depend can be deleted separately (except temporary tables and temporary views). Associated views are reserved but unavailable.
  • off indicates that the view decoupling function is disabled. Tables, functions, synonyms, and other views on which views depend cannot be deleted separately. You can only delete them in the cascade mode.

Default value: off

bulkload_report_threshold

Parameter description: Sets the threshold for reporting import and export statistics. When the data volume exceeds this threshold, the PGXC_BULKLOAD_STATISTICS view can be used to query synchronized data volume, record count, execution time, and other information.

Type: SIGHUP

Value range: an integer ranging from 0 to INT_MAX

Default value: 50

assign_abort_xid

Parameter description: Determines the transaction to be aborted based on the specified XID in a query.

Type: USERSET

Value range: a character string with the specified XID

This parameter is used only for quick restoration if a user deletes data by mistake (DELETE operation). Do not use this parameter in other scenarios. Otherwise, visible transaction errors may occur.

default_distribution_mode

Parameter description: Specifies the default distribution mode of a table. This feature is supported only in 8.1.2 or later.

Type: USERSET

Value range: enumerated values
  • roundrobin: If the distribution mode is not specified during table creation, the default distribution mode is selected according to the following rules:
    1. If the primary key or unique constraint is included during table creation, hash distribution is selected. The distribution column is the column corresponding to the primary key or unique constraint.
    2. If the primary key or unique constraint is not included during table creation, round-robin distribution is selected.
  • hash: If the distribution mode is not specified during table creation, the default distribution mode is selected according to the following rules:
    1. If the primary key or unique constraint is included during table creation, hash distribution is selected. The distribution column is the column corresponding to the primary key or unique constraint.
    2. If the primary key or unique constraint is not included during table creation but there are columns whose data types can be used as distribution columns, hash distribution is selected. The distribution column is the first column whose data type can be used as a distribution column.
    3. If the primary key or unique constraint is not included during table creation and no column whose data type can be used as a distribution column exists, round-robin distribution is selected.

Default value: roundrobin

The default value of this parameter is roundrobin for a new GaussDB(DWS) 8.1.2 cluster and is hash for an upgrade to GaussDB(DWS) 8.1.2.