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 a scale-out redistribution session and redistribution SQL statements can be executed.
off indicates that the current session is a non-scale-out redistribution session and redistribution SQL statements cannot be executed.
Default value: off
Note
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.
Note
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.
Configuration Item | Behavior | Applicable Compatibility Mode |
|---|---|---|
display_leading_zero | Specifies how floating point numbers are displayed.
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: 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.
SELECT add_months('2018-02-28',3) FROM dual;
add_months
---------------------
2018-05-28 00:00:00
(1 row)
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:
| 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.
SELECT (-2147483648)::int / (-1)::int;
ERROR: integer out of range
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.
SELECT length(lpad('123',0,'*')) FROM dual;
length
--------
(1 row)
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.
Caution 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.
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. 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 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.
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.
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
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.
| 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.
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 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.
| 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:
| 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.
| 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.
| 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.
| MySQL |
light_object_mtime | Specifies whether the mtime column in the pg_object system catalog records object operations.
| 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.
| MySQL |
cte_onetime_inline | Indicates whether to execute inline for non-stream plans.
| 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.
| 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.)
| 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.)
| 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.)
| 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.)
| ORA TD MySQL |
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.
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
Caution
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:
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.
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:
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.
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.
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
Note
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.