Miscellaneous Parameters

server_version

Parameter description: Specifies the server version number in the string format.

Type: INTERNAL (Fixed parameter. You are not advised to configure this parameter because incorrect values may cause compatibility issues.)

Value range: a string

Default value: 9.2.4

server_version_num

Parameter description: Specifies the server version number in the integer format.

Type: INTERNAL (Fixed parameter. You are not advised to configure this parameter because incorrect values may cause compatibility issues.)

Value range: an integer

Default value: 90204

sql_dialect

Parameter description: Binds a dialect plug-in to the current database. This parameter is available only for clusters of version 9.1.1.100 or later.

Type: USERSET

Value range: mysql

Default Value: Empty

You need to install the sql_dialect plugin and run the ALTER DATABASE database_name SET sql_dialect = 'mysql'; statement to set this parameter.

enable_cluster_resize

Parameter description: Indicates whether the current session is for scaling or redistributing data. It should only be used for these specific sessions and not set for other service sessions.

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.

enable_auto_insert_into_new_group

Parameter description: Specifies whether to create a new table to the target node group when the INSERT OVERWRITE operation is performed on a table during scale-out.

Type: USERSET

Value range: Boolean

• on: The new table is created to the target node group.
• off: The new table is created to the source node group.

Default value: on

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

redact_compat_options

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

Type: USERSET

Value range: enumerated values

• 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.
• relaxed_redact_check indicates that the data masking column type verification is weakly controlled, and the table field column type that depends on data masking can be modified. This option is supported only by clusters of version 9.1.0.222 or later under 9.1.0.xxx and clusters of version 9.1.1.200 or later.

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

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: on

For a newly installed cluster of version 9.1.1.100 or later, the default value of this parameter is on. In upgrade scenarios, the default value of this parameter is forward compatible, and the original value is retained.

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

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

Default value: roundrobin

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

object_mtime_record_mode

Parameter description: Sets the update action of the mtime column in the PG_OBJECT system catalog.

Type: SIGHUP

Value range: a string

• default: ALTER, COMMENT, GRANT/REVOKE, and TRUNCATE operations update the mtime column by default.
• disable: The mtime column is not updated.
• disable_acl: GRANT or REVOKE operation does not update the mtime column.
• disable_truncate: TRUNCATE operations do not update the mtime column.
• disable_partition: Partition ALTER operations do not update the mtime column.

Default value: default

max_volatile_tables

Parameter description: Specifies the maximum number of volatile tables created for each session, including volatile tables and their auxiliary tables. This parameter is supported by clusters of version 8.2.0 or later.

Type: USERSET

Value range: an integer ranging from 0 to INT_MAX

Default value: 300

query_cache_refresh_time

Parameter description: Specifies the cache refresh interval for queries for which the enable_accelerate_select parameter takes effect. This parameter is supported only by clusters of version 8.3.0 or later.

Type: USERSET

Value range: a floating point number ranging from 0 to 10000.0, in seconds

Default value: 60.0

vector_engine_strategy

Parameter description: Specifies the vectorization enhancement policy. This parameter is supported only by clusters of version 8.3.0 or later.

Type: USERSET

Value range: enumerated values

• force specifies that the vectorization-enhanced plan is forcibly rolled back to the row storage plan when there are scenarios that do not support vectorization.
• improve specifies that vectorization enhancement is enabled even when there are scenarios that do not support vectorization.

Default value: improve

default_temptable_type

Parameter description: Specifies the type of temporary table created when CREATE TABLE is used to create a temporary table without specifying the table type before TEMP or TEMPORARY. This parameter is supported only by clusters of version 9.1.0 or later.

Type: USERSET

Value range: enumerated values

• local: creates a local temporary table when the type is not specified.
• volatile: creates a volatile temporary table when the type is not specified.

Default value: local

pgxc_node_readonly

Parameter description: Specifies whether a CN or DN is an elastic or classic DN. This parameter is supported only by clusters of version 9.1.0 or later.

Type: SUSET

Value range: Boolean

• on indicates that the CN or DN is an elastic node.
• off indicates that the CN or DN is a classic node.

Default value: off

hudi_sync_max_commits

Parameter description: Specifies the maximum number of commits for a single synchronization task in Hudi. This parameter is supported only by clusters of version 9.1.0.100 or later.

Type: SIGHUP

Value range: an integer ranging from –1 to INT_MAX

• –1 indicates no limit.
• 0 indicates no limit.
• Any other value indicates the maximum number of commits.

Default value: –1

foreign_table_default_rw_options

Parameter description: Specifies the default permissions when creating a foreign table without specifying them. This parameter is supported only by clusters of version 9.0.3 or later.

Type: USERSET

Value range: a string

• READ_ONLY indicates the read-only permission.
• WRITE_ONLY indicates the write-only permission.
• READ_WRITE indicates the read-write permission.

Default value: READ_ONLY

analyze_options

Parameter description: controls the features and functions related to the ANALYZE command. This parameter is available only in clusters of version 9.1.1.100 or later.

Type: USERSET

Value range: enumerated values

• none: No features or functions of ANALYZE are enabled.
• analyze_on_vw: ANALYZE can be executed on elastic VWs.
• runtime_v3_table: Runtime Analyze that can be triggered by queries from column-store 3.0 tables

Default value: analyze_on_vw

allow_system_table_vacuum_full

Parameter description: Specifies whether the VACUUM FULL command can be executed on system tables. This parameter is available only in clusters of version 9.1.1.100 or later.

Type: SUSET

Value range: Boolean

• on indicates that the VACUUM FULL command can be executed on system tables.
• off indicates that the VACUUM FULL command cannot be executed on system tables. If this parameter is set to off, an error is reported when the VACUUM FULL command is executed on a specified system catalog. If the VACUUM FULL command is executed on an entire database, all system tables are skipped.

Default value: off

The VACUUM FULL command can be executed on system tables only when the database is offline. Otherwise, the tables will be locked, and exceptions and errors may occur.

enable_recycle_default_obs_tbs

Parameter description: Specifies whether to scan and clear residual files in default_obs_tbs (OBS tablespace dedicated for cold and hot tables).

Using DWS cold and hot tables can create leftover files in the default_obs_tbs during system errors. These files do not clear automatically and take up significant disk space over time. This leads to incorrect disk usage readings, disrupting proper database maintenance and operation.

If this parameter is enabled, leftover files in default_obs_tbs can be automatically cleared.

This parameter is available only in clusters of version 9.1.1.100 or later.

Type: SUSET

Value range: Boolean

• on indicates that default_obs_tbs can be scanned and cleared.
• off indicates that default_obs_tbs cannot be scanned and cleared.

Default value: off

enable_pg_db_file

Parameter description: specifies whether to record file information in the PG_DB_FILE system catalog when a V3 table writes a CU file, and whether to obtain file information by traversing the PG_DB_FILE system catalog for functions such as column-store vacuum, table size calculation, and space control of V3 tables. This parameter is only supported by clusters of version 9.1.1.100 or later.

Type: SIGHUP

Value range: Boolean

• on indicates that file information is recorded in the PG_DB_FILE system catalog when a V3 table writes a CU file, and file information is obtained by traversing the PG_DB_FILE system catalog for functions such as column-store vacuum, table size calculation, and space control of V3 tables.
• off indicates that file information is not recorded in the PG_DB_FILE system catalog when a V3 table writes a CU file, and the method of obtaining file information for functions such as column-store vacuum, table size calculation, and space control of V3 tables remains unchanged.

Default value: on

• For new clusters of version 9.1.1.100 or later, the default value is on. For clusters of version 9.1.1.100 or later upgraded from earlier versions, the default value is off.
• If you have set enable_pg_db_file to on for a cluster, do not set it to off. Otherwise, information in the PG_DB_FILE system catalog is incorrect when it is opened in the future.
• If enable_pg_db_file is enabled for an upgraded cluster, the file information before the upgrade will be rebuilt in the PG_DB_FILE system catalog using autovacuum. Therefore, you must enable column-store autovacuum (autovacuum_max_workers_col and colvacuum_threshold_scale_factor are both greater than 0). After the file information is rebuilt, vacuum on the catalog can avoid the OBS list operation.

installation_group_alias

Parameter description: Specifies the alias of the installation node group. After a physical cluster is expanded, ensure that the CREATE TABLE...to group command can correctly point to the current node group. This parameter is available only in clusters of version 9.1.1.100 or later.

Type: USERSET

Value range: a string

Default value: empty

enable_external_column_index_access

Parameter description: Specifies the matching mode of the external table columns in the external schema and actual file columns. Only ORC and Parquet formats are supported. This parameter is supported only by clusters of version 9.1.1.200 or later.

Type: USERSET

Value range: Boolean

• on: External table fields and file fields are matched in sequence.
• off: External table fields and file fields are matched by name. Fields that cannot be matched are filled with null.

Default value: on

max_partition_per_table

Parameter description: Specifies the maximum number of partitions in a partitioned table. This parameter is supported only by clusters of version 9.1.1.200 or later.

Type: SIGHUP

Value range: an integer ranging from 1 to 32767

Default value: 4096

cache_partition_options

Parameter description: Specifies whether the system caches the latest partition result when data is written to a partitioned table in batches. This parameter is supported only by clusters of version 9.1.1.200 or later.

Type: SIGHUP

Value range: enumerated values

• range: indicates that this function supports range partitioned tables.
• list: indicates that this function supports list partitioned tables.
• none: indicates that this function is disabled.

Default value: rang,list

enable_obs_limiter

Parameter description: Specifies whether to enable OBS asynchronous I/O flow control. After it is enabled, the flow control settings take effect. This parameter is supported only by clusters of version 9.1.1.200 or later.

Type: SIGHUP

Value range: Boolean

• on: Flow control is enabled and the corresponding flow control settings are matched and take effect.
• off: Flow control is disabled. It won not work even if the settings are present.

Default value: on

enable_kae_accelerate

Parameter description: Specifies whether to enable KAE acceleration. This parameter is supported only by clusters of version 9.1.1.200 or later.

KAE acceleration is supported only on servers using Kunpeng 920 CPUs. If this parameter is set to on in other types of servers, KAE acceleration does not take effect.

Type: POSTMASTER

Value range: Boolean

• on: KAE acceleration is enabled and the zstd and lz4 compression libraries can be used.
• off: KAE acceleration is disabled and the default compression library is used.

Default value: off

hnsw_options

Parameter description: Specifies the query options related to pgvector HNSW indexes. This parameter is supported only by clusters of version 9.1.1.200 or later.

Type: USERSET

Value range: a string

• ef_search: controls the query parameter (number of query candidates). Value range: 1 to 1000.
• max_scan_tuples: specifies the maximum number of nodes to be scanned. Value range: 1 to INT_MAX.
• mem_scan_multiplier: specifies the dynamic memory expansion rate. Value range: 1 to 1000.

Default value: 40,20000,1

The value of this parameter is a string containing three parameter values, which are separated by commas (,). The single parameter cannot be set independently.

ivfflat_options

Parameter description: Specifies the query options related to pgvector IVFFlat indexes. This parameter is supported only by clusters of version 9.1.1.200 or later.

Type: USERSET

Value range: a string

• probes: number of scanned probes. Value range: 1 to 32768.
• max_probes: maximum number of scanned probes. Value range: 1 to 32768.

Default value: 1,32768

The value of this parameter is a string containing two parameter values, which are separated by commas (,). The single parameter cannot be set independently.

ivfflat_iterative_scan

Parameter description: Specifies whether to enable iterative scan for pgvector IVFFlat indexes. If this parameter is enabled, more indexes are automatically scanned to obtain sufficient data. This parameter is supported only by clusters of version 9.1.1.200 or later.

Type: USERSET

Value range: enumerated values

• relaxed_order: The function is enabled, but the results are not sorted based on the actual vector distance.
• off: The function is disabled.

Default value: off

hnsw_iterative_scan

Parameter description: Specifies whether to enable iterative scan for pgvector HNSW indexes. If this parameter is enabled, more indexes are automatically scanned to obtain sufficient data. This parameter is supported only by clusters of version 9.1.1.200 or later.

Type: USERSET

Value range: enumerated values

• relaxed_order: The function is enabled, but the results are not sorted based on the actual vector distance.
• strict_order: The function is enabled, and the results are sorted based on the actual vector distance.
• off: The function is disabled.

Default value: off