Miscellaneous Parameters¶
enable_cluster_resize¶
Parameter description: If an SQL statement involves tables belonging to different groups, you can enable this parameter to push the execution plan of the statement to improve performance.
Type: SUSET
Value range: Boolean
on indicates the execution plan of the statement can be pushed.
off indicates the execution plan of the statement cannot be pushed.
Default value: off
Note
This parameter is used for internal O&M. Do not set it to on unless absolutely necessary.
cstore_insert_mode¶
Parameter description: Specifies the storage location of data to be imported to an HDFS table. This parameter is needed for operations that involve data import, such as INSERT, UPDATE, COPY, and VACUUM FULL.
Type: USERSET
Value range: enumerated values
AUTO: The major part of imported data is stored in HDFS, and the tail is stored in the delta table.
DELTA: All the imported data is stored in the delta table.
MAIN: All the imported data is stored in HDFS.
Default value: auto
Note
You can set other values as the default in the configuration file.
remote_read_mode¶
Parameter description: When enable_crc_check is set to on and the data read by the primary DN fails the verification, remote_read_mode is used to specify whether to enable remote read and whether to use secure authentication for connection upon the data verification failure. The setting takes effect only after the cluster is restarted.
Type: POSTMASTER
Value range: off, non_authentication, authentication
off: indicates that the remote read function is disabled.
non_authentication: indicates that the standby DN is connected and data is obtained when non-authentication is used.
authentication: indicates that the standby DN is connected and data is obtained through authentication. Before restarting the cluster, ensure that a certificate exists in the $GAUSSHOME/share/sslcert/grpc/ directory. Otherwise, the cluster cannot be started.
Default value: non_authentication
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. This parameter is a postmaster parameter. You can set it using gs_guc, and you need to restart gaussdb to make the setting take effect.
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.
| 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 | Performs an update if multiple rows are matched for MERGE INTO. If this item is specified, no error is reported if multiple rows are matched. Otherwise, an error is reported (same as Oracle). | 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 preferred to be 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_data_compatible_db=# SELECT textcat('abc', NULL);
textcat
---------
abc
(1 row)
td_data_compatible_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_data_compatible_db=# SELECT textcat('abc', NULL);
textcat
---------
(1 row)
td_data_compatible_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_basic_db=# select length('a'::char(10)::text);
length
--------
10
(1 row)
td_compatibility_basic_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 |
light_object_mtime | Specifies whether the mtime column in the pg_object system catalog records object operations.
| ORA TD MySQL |
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
max_cache_partition_num¶
Parameter description: Specifies the number of memory-saving partitions in column-store mode during redistribution after scale-out. If the number of partitions exceeds the upper limit, the earliest cached partition is directly written to the column-store file.
Type: SIGHUP
Value range: an integer ranging from 0 to 32767.
0 indicates that the memory-saving mode is disabled in column storage.
Values from 1 to 32767 indicate the maximum number of partitions that can be cached in a partitioned table.
Default value: 0
Note
This parameter is used for redistribution during scale-out. A proper value can reduce the memory consumption during redistribution of a partitioned column-store table. However, tables with unbalanced data distribution in some partitions may generate a large number of small CUs after the redistribution. If there are a large number of small CUs, execute the VACUUM FULL statement to merge them.
enable_prevent_job_task_startup¶
Parameter description: Specifies whether to prevent the thread startup of scheduled jobs. This is an internal parameter. You are not advised to change the value of this parameter.
Type: SIGHUP
Value range: Boolean
on: Threads of scheduled jobs will not be started.
off: Threads of scheduled jobs will be started.
Default value: off
Note
Set this parameter only on CNs.
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.