Help Center > > Developer Guide> Setting GUC Parameters> Miscellaneous Parameters

Miscellaneous Parameters

Updated at: Jul 14, 2021 GMT+08:00

server_version

Parameter description: Specifies the server version number.

Type: INTERNAL (fixed parameter, which can be viewed but not modified)

Value range: a string

Default value: 9.2.4

server_version_num

Parameter description: Specifies the server version number.

Type: INTERNAL (fixed parameter, which can be viewed but not modified)

Value range: an integer

Default value: 90204

block_size

Parameter description: Specifies the block size of the current database.

Type: INTERNAL (fixed parameter, which can be viewed but not modified)

Value range: 1024, 2048, 4096, 8192, 16384, and 32768

Default value: 8192

segment_size

Parameter description: Specifies the segment file size of the current database.

Type: INTERNAL (fixed parameter, which can be viewed but not modified)

Default value: 1 GB

max_index_keys

Parameter description: Specifies the maximum number of index keys supported by the current database.

Type: INTERNAL (fixed parameter, which can be viewed but not modified)

Default value: 32

gin_fuzzy_search_limit

Parameter description: The primary goal of developing GIN indexes was to create support for highly scalable full-text search in GaussDB(DWS), and there are often situations when a full-text search returns a large set of results. Moreover, this situation often occurs when the query contains frequently used words, so that the large result set is not even useful. Because reading many tuples from the disk and sorting them will consume large numbers of resources, this is unacceptable for production. To facilitate controlled execution of such queries, GIN has a configurable soft upper limit on the number of rows returned: configuration parameter gin_fuzzy_search_limit. The default value 0 indicates that there is no limit on the returned set. If a non-zero value is set, the returned set is a subset chosen from the whole result set at random.

Type: USERSET

Value range: 0 to the maximum int value

Default value: 0

integer_datetimes

Parameter description: Specifies whether the date and time are in the 64-bit integer format.

Type: INTERNAL (fixed parameter, which can be viewed but not modified)

Value range: Boolean

  • on indicates the 64-bit integer format is used.
  • off indicates the 64-bit integer format is not used.

Default value: on

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

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

enable_cbm_tracking

Parameter description: Specifies whether to enable cbm tracking. To perform full or incremental backup for a cluster by using Roach, set this parameter to on. Otherwise, the backup will fail.

Type: SIGHUP

Value range: Boolean

  • on: The cbm tracking is enabled.
  • off: The cbm tracking is disabled.

Default value: off

lc_collate

Parameter description: Specifies the locale in which sorting of textual data is done.

Type: INTERNAL (fixed parameter, which can be viewed but not modified)

Default value: Depends on the configuration during cluster deployment.

lc_ctype

Parameter description: Specifies the locale that determines character classifications. For example, it specifies what a letter and its upper-case equivalent are.

Type: INTERNAL (fixed parameter, which can be viewed but not modified)

Default value: Depends on the configuration during cluster deployment.

max_identifier_length

Parameter description: Specifies the maximum identifier length.

Type: INTERNAL (fixed parameter, which can be viewed but not modified)

Value range: an integer

Default value: 63

server_encoding

Parameter description: Specifies the database encoding (character set).

Type: INTERNAL (fixed parameter, which can be viewed but not modified)

Default value: Determined when the database is created.

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, DELTAMERGE, 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

You can set other values as the default in the configuration file.

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 on by default. If you change this value and create a row- or column-store table, you will fail to add or delete CNs, replace nodes, or upgrade or scale out the cluster, and data reliability will be affected. Therefore, you are advised not to change this value. If any row- or column-store tables exist in the database, contact technical support to change this value to false before you add or delete CNs, replace nodes, or upgrade or scale-out the cluster.

In the GaussDB(DWS) cluster, this parameter 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-based 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

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_extra_dicts

Parameter description: Specifies whether to add extra dictionary files to Zhparser.

Type: POSTMASTER

Value range: a string

Default value: empty

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

max_resource_package

Parameter description: Specifies the maximum number of threads that can be concurrently run in each DN in the computing Node Group.

Type: POSTMASTER

Value range: 0 to 2147483647

Default value: 0

transparent_encrypted_string

Parameter description: A sample string for transparent encryption. It is the ciphertext of the fixed string TRANS_ENCRYPT_SAMPLE_STRING encrypted using the database encryption key. It is used to check whether the DEK obtained during the second startup is correct. If it is incorrect, CNs and DNs will not be started.

Type: POSTMASTER

Value range: a string. An empty string indicates that the current cluster is a non-encrypted cluster.

Default value: empty

Do not set this parameter manually. Otherwise, the cluster may become faulty.

transparent_encrypt_kms_region

Parameter description: The deployment zone of the current cluster. The value cannot contain characters that are not defined in RFC 3986. The maximum length is 2047 bytes.

Type: POSTMASTER

Value range: a string

Default value: empty

behavior_compat_options

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

Type: USERSET

Value range: a string

Default value: null

  • 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

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.

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 month 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)

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.

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.

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.

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.

unbind_divide_bound

Controls the range check on the result of integer division.

If this item is specified, you do not need to check the range of the division result. For example, the result of INT_MIN/(-1) can be INT_MAX+1. If this item is not specified, an out-of-bounds error is reported because the result is greater than INT_MAX.

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).

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.

In Teradata-compatible mode, this parameter has no impact on the display of an empty result.

In Oracle-compatible mode, an empty string will be returned as NULL if this parameter is specified.

  • 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)

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.

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 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.

check_function_conflicts

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

  • If this parameter is not specified, the IMMUTABLE/STABLE/VOLIATLE attributes of a custom function is 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

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; 
/ 

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.

  • 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)

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_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)

disable_select_truncate_parallel

When this configuration item is set, the concurrent execution of TRUNCATE and DML operations on different partitions is forbidden, and the 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.

bpchar_text_without_rtrim

In Teradata-compatible mode, when this parameter is set, the space on the right is reserved 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. 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)

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

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.

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.

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

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?







Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel