Platform and Client Compatibility
Many platforms use the database system. External compatibility of the database system provides great convenience for platforms.
a_format_date_timestamp
Parameter description: Specifies whether to return the date and time. In A-compatible mode, when a transaction is started, the functions current_date(), current_timestamp(), and localtimestamp() return the timestamp when the current SQL statement is started. In the multi-tenancy scenario, this parameter can be set at the PDB level.
Parameter type: Boolean.
Unit: none
Value range:
- on: Returns the timestamp when the current SQL statement is started.
- off: Returns the date or date and time when the transaction is started.
Default value: off. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value. This parameter can be enabled when the system time needs to be returned when a transaction is started.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.
convert_string_to_digit
Parameter description: Specifies the implicit conversion priority, which specifies whether to preferentially convert strings into numbers. This parameter can be set at the PDB level.
Parameter type: Boolean.
Unit: none
Value range:
- on indicates that strings are preferentially converted into numbers.
- off indicates that strings are not preferentially converted into numbers.
Default value: on. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: If this parameter is modified, the internal data type conversion rule will be modified, causing unexpected behavior. Exercise caution when performing this operation.
character_set_connection
Parameter description: Specifies the character set of constant strings. If this parameter is modified, collation_connection is changed to the default collation of the character set.
- This parameter takes effect when sql_compatibility is set to 'B', b_format_version is set to '5.7', and b_format_dev_version is set to 's2'.
- This parameter takes effect in M-compatible mode.
Parameter type: string.
Unit: none
Value range:
- A character set in the compatibility mode whose sql_compatibility is set to 'B'. For details about the character sets supported in B-compatible mode, see COLLATE collation in "SQL Reference" > "SQL Syntax" > "C" > "CREATE TABLE" in Developer Guide.
- In M-compatible mode, the value range is the supported character set. For details, see "SQL Reference > Character Sets and Collations" in M Compatibility Developer Guide.
Default value: same as the value of server_encoding.
Setting method: This is a USERSET parameter, which can only be set at database level and session level using method 3, and cannot be set using the GUC tools. For details about how to set this parameter, see Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: If the configured character set is inconsistent with the current environment, transcoding may be performed for multiple times, affecting performance.
collation_connection
Parameter description: Specifies the collation of a constant string. If this parameter is modified, character_set_connection is changed to the default character set of the collation.
- This parameter takes effect when sql_compatibility is set to 'B', b_format_version is set to '5.7', and b_format_dev_version is set to 's2'.
- This parameter takes effect in M-compatible mode.
Parameter type: string.
Unit: none
Value range:
- A character set in the compatibility mode whose sql_compatibility is set to 'B'. For details about the character sets supported in B-compatible mode, see COLLATE collation in "SQL Reference" > "SQL Syntax" > "C" > "CREATE TABLE" in Developer Guide.
- In M-compatible mode, the value range is the supported character set. For details, see "SQL Reference > Character Sets and Collations" in M Compatibility Developer Guide.
Default value: default collation of the current server_encoding. If it has no default collation, the value is default.
Setting method: This is a USERSET parameter, which can only be set at database level and session level using method 3, and cannot be set using the GUC tools. For details about how to set this parameter, see Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: If the configured collation is inconsistent with the current environment, transcoding may be performed for multiple times, affecting performance.
character_set_client
Parameter description: Specifies the character set of the client. The meaning of this parameter is the same as that of client_encoding.
Parameter type: string.
Unit: none
Value range: character set supported in M-compatible mode. For details, see "SQL Reference > Character Sets and Collations" in M Compatibility Developer Guide.
Default value: same as the character set of the client.
Setting method: This is a USERSET parameter, which can only be set using method 3, and cannot be set using the GUC tools. For details about how to set this parameter, see Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: If the configured character set is inconsistent with the current environment, garbled characters may be displayed.
character_set_database
Parameter description: Specifies the character set of a database or schema.
Parameter type: string.
Unit: none
Value range: character set supported in M-compatible mode. For details, see "SQL Reference > Character Sets and Collations" in M Compatibility Developer Guide.
Default value: same as the character set of the current database.
Setting method: This is a USERSET parameter, which can only be set at session level using method 3, and cannot be set using the GUC tools. For details about how to set this parameter, see Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.

This parameter takes effect in M-compatible mode.
collation_database
Parameter description: Displays the collation of a database or schema.
Parameter type: string.
Unit: none
Value range: collation supported in M-compatible mode. For details, see "SQL Reference > Character Sets and Collations" in M Compatibility Developer Guide.
Default value: default collation of the current database character set. If the default collation does not exist, the value is default.
Setting method: This is a USERSET parameter, which can only be set at session level using method 3, and cannot be set using the GUC tools. For details about how to set this parameter, see Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.

This parameter takes effect in M-compatible mode.
character_set_server
Parameter description: Specifies the character set for creating a database or schema.
Parameter type: string.
Unit: none
Value range: character set supported in M-compatible mode. For details, see "SQL Reference > Character Sets and Collations" in M Compatibility Developer Guide.
Default value: same as the character set of the current database.
Setting method: This is a USERSET parameter, which can only be set at database level and session level using method 3, and cannot be set using the GUC tools. For details about how to set this parameter, see Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.

This parameter takes effect in M-compatible mode.
collation_server
Parameter description: Specifies the collation for creating a database or schema.
Parameter type: string.
Unit: none
Value range: collation supported in M-compatible mode. For details, see "SQL Reference > Character Sets and Collations" in M Compatibility Developer Guide.
Default value: default collation of the current database character set. If the default collation does not exist, the value is default.
Setting method: This is a USERSET parameter, which can only be set at database level and session level using method 3, and cannot be set using the GUC tools. For details about how to set this parameter, see Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.

This parameter takes effect in M-compatible mode.
character_set_results
Parameter description: Specifies the character set of the returned result.
- This parameter takes effect when sql_compatibility is set to 'B', b_format_version is set to '5.7', and b_format_dev_version is set to 's2'.
- This parameter takes effect in M-compatible mode.
Parameter type: string.
Unit: none
Value range:
- A character set in the compatibility mode whose sql_compatibility is set to 'B'. For details about the character sets supported in B-compatible mode, see COLLATE collation in "SQL Reference" > "SQL Syntax" > "C" > "CREATE TABLE" in Developer Guide.
- In M-compatible mode, the value range is the supported character set. For details, see "SQL Reference > Character Sets and Collations" in M Compatibility Developer Guide.
- You can also set this parameter to null or ''. In this case, the character set of the database is output.
Default value: same as the value of client_encoding.
Setting method: This is a USERSET parameter, which can only be set at session level using method 3, and cannot be set using the GUC tools. For details about how to set this parameter, see Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: If the configured character set is inconsistent with the current environment, garbled characters may be displayed.
enable_multiple_charset
Parameter description: Specifies whether tables, columns, and schemas whose character sets are different from the character set of the current database can be created in M-compatible database.
Parameter type: Boolean.
Unit: none
Value range:
- on: You can create tables, columns, and schemas whose character sets are different from the character set of the current database.
- off: You cannot create tables, columns, and schemas whose character sets are different from the character set of the current database.
Default value: on.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Set this parameter based on service requirements.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.
lower_case_table_names
Parameter description: Specifies whether the user table names (including table aliases), user view names, schema names (internal database names), and values of the columns corresponding to the preceding objects in the information_schema system view are case-sensitive in an M-compatible database.
Parameter type: integer.
Unit: none
Value range:
- 0: User table names, user view names, and schema names (internal database names) are stored in system catalogs in the capitalization style specified in the CREATE TABLE, CREATE VIEW, and CREATE SCHEMA (CREATE DATABASE) statements. In the information_schema system view, the values of the columns corresponding to the preceding objects are case-sensitive.
- 1: User table names are stored in lowercase in system catalogs. Names are case-insensitive. During storage and query operations, all user table names are converted to lowercase. This behavior also applies to schema names (internal database names), table aliases, user view names, and values of the corresponding columns in the information_schema system view.
Default value: 0
Setting method: This parameter needs to be specified during database initialization. If it is not specified, the default behavior is case-sensitive. You can use the following methods to specify the case-sensitive behavior:
--gsinit-parameter='--lower_case_table_names=1'; -- Case-insensitive. --gsinit-parameter='--lower_case_table_names=0'; -- Case-sensitive.
Setting suggestion: Retain the default value. If the values of the columns corresponding to the preceding objects in the user table names (including table aliases), user views, schema names (internal database names), and information_schema system views queried in the M-compatible database need to be case-insensitive, set this parameter to 1.

- The following scenarios may cause data loss. Exercise caution when performing these operations.
- Do not perform import, export, logical decoding, or backup and restoration between two instances with different lower_case_table_names parameter values.
- Do not create a DR relationship between two instances with different lower_case_table_names parameter values.
- This parameter is specified during database installation. Do not modify this parameter after the database is started or during the upgrade. Otherwise, data may be lost, and the correct case sensitivity of column values in the information_schema system view cannot be ensured.
- For system functions in a non-M compatibility, if the input parameters involve user table names (including table aliases), user view names, and schema names (internal database names), the correct case sensitivity cannot be ensured after the lower_case_table_names parameter is set to 1.
nls_timestamp_format
Parameter description: Specifies the default timestamp format. This parameter can be set at the PDB level.
Parameter type: string.
Unit: none
Value range: a string.
Default value: "DD-Mon-YYYY HH:MI:SS.FF AM". In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.
nls_timestamp_tz_format
Parameter description: Specifies the default timestamp with time zone format. This parameter can be set at the PDB level.
Parameter type: string.
Unit: none
Value range: a string.
Default value: "DD-Mon-YYYY HH:MI:SS.FF AM". In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.

This parameter is valid only when a_format_version is set to 10c and a_format_dev_version is set to s1.
nls_nchar_characterset
Parameter description: Specifies the national character set. This parameter is used together with the nchr(cvalue int|bigint) system function. This parameter can be set at the PDB level.
Parameter type: string.
Unit: none
Value range: "AL16UTF16" and "UTF8" (case-insensitive).
Default value: "AL16UTF16". In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.

- The database does not support the national character set. This parameter is used only to be compatible with the A database and obtain the national character set for users.
- This GUC parameter applies only to the nchr(cvalue int|bigint) function.
group_concat_max_len
Parameter description: This parameter is used together with the group_concat() function to limit the length of the return value. If the length exceeds the limit, the return value is truncated and an error or alarm is generated. If sql_mode is set to a value contain STRICT_TRANS_TABLES, an error is reported in the DML statement. In other scenarios, an alarm is reported. This parameter can be set at the PDB level.
Parameter type: integer.
Unit: none
Value range: 0 to 9223372036854775807. Currently, the maximum length that takes effect is 1073741823. If the length exceeds this limit, the out of memory error is reported.
Default value: 1024. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: If this parameter is set to a small value, the result returned by group_concat() may be truncated.
lastval_supported
Parameter description: Specifies whether the lastval() function can be used.
Parameter type: Boolean.
Unit: none
Value range:
- on: The lastval() function can be used and the nextval() function cannot be pushed down.
- off: The lastval() function cannot be used and the nextval() function can be pushed down.
Default value: off
Setting method: This is a POSTMASTER parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.
max_function_args
Parameter description: Specifies the maximum number of parameters allowed for a function.
Parameter type: integer.
Unit: none
Value range: 8192
Default value: 8192
Setting method: This is a fixed INTERNAL parameter. It can be viewed but cannot be modified.
Setting suggestion: This parameter can only be viewed.
Risks and impacts of improper settings: none
max_subpro_nested_layers
Parameter description: Specifies the maximum nesting depth of nested subprograms. This parameter can be set at the PDB level.
Parameter type: integer.
Unit: none
Value range: 0 to 100

When this parameter is set to 0, nested subprograms are not allowed.
Default value: 3. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Set this parameter based on the maximum nesting depth.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.
transform_null_equals
Parameter description: Specifies whether expressions of the form expr=NULL (or NULL=expr) are treated as expr IS NULL. The correct SQL-standard-compliant behavior of expr=NULL is to always return NULL (unknown). Filtered forms in Microsoft Access generate queries that appear to use expr=NULL to test for null values. After you enable this option, you can use this API to access the database. This parameter can be set at the PDB level.
Parameter type: Boolean.
Unit: none
Value range:
- on: Expressions of the form expr=NULL (or NULL=expr) are treated as expr IS NULL They return true if expr evaluates to the NULL value, and false otherwise.
- off: expr=NULL always returns NULL (unknown).
Default value: off. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.

New users are always confused about the semantics of expressions involving NULL values. Therefore, off is used as the default value.
support_extended_features
Parameter description: Specifies whether extended database features are supported.
Parameter type: Boolean.
Unit: none
Value range:
- on indicates that extended database features are supported.
- off indicates that extended database features are not supported.
Default value: off
Setting method: This is a POSTMASTER parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.
sql_compatibility
Parameter description: Specifies the type of mainstream database with which the SQL syntax and statement behavior of the database is compatible.
Parameter type: enumerated type
Unit: none
Value range:
- A: Oracle-compatible database.
- B: MySQL-compatible database.
- C: Teradata-compatible database.
- PG: PG-compatible database.
- M: M-compatible database.
Default value: A
Setting method: This is a fixed INTERNAL parameter. It can be viewed but cannot be modified.
Setting suggestion: This parameter can only be viewed.
Risks and impacts of improper settings: none

- This parameter can be set only when you run the CREATE DATABASE command to create a database. For details, see "SQL Reference" > "SQL Syntax" > "C" > "CREATE DATABASE" in Developer Guide.
- In the database, this parameter must be set to a specific value. It can be set to A or B and cannot be changed. Otherwise, the setting is not consistent with the database behavior.
b_format_behavior_compat_options
Parameter description: Specifies a B-compatible database configuration item. This parameter can be set at the PDB level.
Parameter type: string.
Unit: none
Value range: Currently, only B-compatible configuration items listed in Table 1 are supported. Use commas (,) to separate multiple compatibility configuration items, for example, set b_format_behavior_compat_options="enable_set_variables,set_session_transaction".
Default value: "". In the PDB scenario, if this parameter is not set, the global setting is inherited.

If b_format_version is not set to "", b_format_behavior_compat_options will be set to all and cannot be modified.
Configuration Item |
Behavior |
---|---|
enable_set_variables |
Specifies whether to enable the enhancement of the SET syntax.
|
set_session_transaction |
Specifies whether to enable the SET SESSION TRANSACTION syntax.
|
enable_modify_column |
Specifies whether to enable the ALTER TABLE MODIFY syntax.
|
default_collation |
Specifies whether to enable forward compatibility of the default collation.
|
all |
Determines whether to enable all syntax. all cannot be specified together with other configuration items. In the table, specifying all configuration items except all that are separated by commas is equivalent to specifying all. |
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Determines whether some B-compatible features are available. If you want to modify this parameter, make sure you understand its meaning and modify it with caution to avoid risks caused by misoperations.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.
m_format_behavior_compat_options
Parameter description: Specifies the configuration items of an M-compatible database. In the multi-tenancy scenario, this parameter can be set at the PDB level.
Parameter type: string.
Unit: none
Value range: Currently, only Table 2 are supported. Compatibility configuration items are separated by commas (,).
Default value: "disable_zero_chars_conversion,cast_as_new_json,enable_load_data_remote_transmission,grant_database_nomapping,disable_illegal_function_syntax". In the PDB scenario, if this parameter is not set, the global setting is inherited.
Configuration Item |
Behavior |
---|---|
enable_escape_string |
Specifies whether escape character control is enabled.
|
enable_conflict_funcs |
Specifies whether to enable the conflict function.
|
select_column_name |
Specifies whether to display column names. Currently, this function is supported only in M-compatible mode and is not supported in B-compatible mode. m_format_behavior_compat_options is set to 'select_column_name' and the feature switch is at the user level and is disabled by default.
|
enable_precision_decimal |
Data type precision.
|
forbid_none_space_comment |
The single-line comment syntax "-- " is supported.
|
enable_load_data_remote_transmission |
Specifies whether to import data from a client through LOAD DATA. If this parameter is not set, files are imported from the environment where the server is located after the local parameter is specified for LOAD DATA. If this parameter is set, data is imported from the environment where the client is located after the local parameter is specified for LOAD DATA.
NOTE:
|
disable_zero_chars_conversion |
Converts \0 into spaces when the parameters are specified in text mode through PBE API.
|
cast_as_new_json |
::JSON can be converted to the JSON type of the M-compatible database for the JSON type.
|
grant_database_nomapping |
Configuration item of the GRANT | REVOKE ON DATABASE syntax behavior in the M-compatible database.
|
disable_illegal_function_syntax |
Controls invalid syntax in system functions.
-- disable_illegal_function_syntax is not set. m_db=# set m_format_behavior_compat_options=''; SET m_db=# select pi(*); pi ------------------- 3.141592653589793 (1 row) -- disable_illegal_function_syntax is set. m_db=# set m_format_behavior_compat_options='disable_illegal_function_syntax'; SET m_db=# select pi(*); ERROR: pi(*) specified, but pi is not an aggregate function LINE 1: select pi(*); ^ CONTEXT: referenced column: pi |
disable_int_cmp_num_index |
Specifies whether to convert integers and fixed-point constants to integers for comparison.
|
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.

If the precision parameter is disabled (m_format_behavior_compat_options is not set to enable_precision_decimal), the created index becomes invalid after the precision parameter is enabled. If the precision parameter is enabled, you need to create an index again.
system_function_version
Parameter description: Specifies the configuration item of the database system function behavior.
Parameter type: string.
Unit: none
Value range: Currently, only compatibility configuration items listed in Table 3 are supported. Use commas (,) to separate multiple compatibility configuration items, for example, "SET system_function_version='get_hash_value_fix,pbe_trunc_fix,empty_array_fix';".
Default value:
- During installation: get_hash_value_fix,pbe_trunc_fix,empty_array_fix
- During upgrade: ""
Configuration Item |
Behavior |
---|---|
get_hash_value_fix |
Specifies whether to enable the dbe_utility.get_hash_value forward compatibility.
gaussdb=# SET system_function_version = ''; SET gaussdb=# SELECT dbe_utility.get_hash_value(1, 1024, 1024); get_hash_value ---------------- 1444 (1 row) gaussdb=# SELECT dbe_utility.get_hash_value(2, 1024, 1024); get_hash_value ---------------- 1444 (1 row) gaussdb=# SET system_function_version = 'get_hash_value_fix'; SET gaussdb=# SELECT dbe_utility.get_hash_value(1, 1024, 1024); get_hash_value ---------------- 1785 (1 row) gaussdb=# SELECT dbe_utility.get_hash_value(2, 1024, 1024); get_hash_value ---------------- 1538 (1 row) |
pbe_trunc_fix |
Specifies whether to enable the forward compatibility of the lpad/rpad/chr/round/trunc/add_months/instrb function in the PBE scenario.
gaussdb=# SET a_format_version='10c'; SET gaussdb=# SET a_format_dev_version='s1'; SET gaussdb=# PREPARE pround1 AS SELECT round(123.456,$1); PREPARE gaussdb=# EXECUTE pround1('2.6'); round --------- 123.456 (1 row) gaussdb=# SET system_function_version = 'pbe_trunc_fix'; SET gaussdb=# PREPARE pround1 AS SELECT round(123.456,$1); PREPARE gaussdb=# EXECUTE pround1('2.6'); round --------- 123.46 (1 row)
NOTE:
This parameter is valid only in an A-compatible database. |
empty_array_fix |
Specifies whether to enable the forward compatibility of empty arrays.
gaussdb=# SET system_function_version = 'empty_array_fix'; SET gaussdb=# SELECT array_eq(array_positions(array[1, 1, 1], 3), '{}'::int[]); array_eq ---------- t (1 row) gaussdb=# SET system_function_version = ''; SET gaussdb=# SELECT array_eq(array_positions(array[1, 1, 1], 3), '{}'::int[]); array_eq ---------- f (1 row) |
regex_like_m_fix |
Specifies the forward compatibility switch that controls the behavior of the regex_like_m function. (The regexp_like function calls regex_like_m, which is also affected.)
gaussdb=# SET system_function_version = 'regex_like_m_fix'; SET gaussdb=# SELECT 'abc' FROM dual WHERE regex_like_m('111 2 222','^1+$|^2+$'); ?column? ---------- abc (1 row) gaussdb=# SET system_function_version = ''; SET gaussdb=# SELECT 'abc' FROM dual WHERE regex_like_m('111 2 222','^1+$|^2+$'); ?column? ---------- (0 row) |
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1. During the upgrade, the parameter is left empty by default. If you need to enable the setting, you are advised to modify the GUC configuration file to ensure that the setting is not lost when the database is restarted.
Setting suggestion: Specifies whether some system function compatibility features are available. If you want to modify this parameter, make sure you understand its meaning and modify it with caution.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.
behavior_compat_options
Parameter description: Specifies the database compatibility configuration item. This parameter can be set at the PDB level.
Parameter type: string.
Unit: none
Value range: Currently, only compatibility configuration items listed in Table 4 are supported. Use commas (,) to separate multiple compatibility configuration items, for example, set behavior_compat_options='end_month_calculate,display_leading_zero'.
Default value: "forbid_package_function_with_prefix,enable_bpcharlikebpchar_compare,enable_crosstype_integer_operator,forbid_skip_tableof_empty_str_elem,enable_ora_joinop_in_updatestmt". In the PDB scenario, if this parameter is not set, the global setting is inherited.
Configuration Item |
Behavior |
||||
---|---|---|---|---|---|
display_leading_zero |
Specifies how floating-point numbers are displayed. It controls the display of zeros before the decimal point of all character string types (such as char, character, nchar, varchar, character varying, varchar2, nvarchar2, text, and clob) and arbitrary-precision types (such as float4, float8, and numeric) in the numeric type, specifies whether the length of the number calculated by length contains zeros before the decimal point.
|
||||
end_month_calculate |
Specifies the calculation logic of the add_months function. This parameter does not take effect in M-compatible databases. Assume that the two parameters of the add_months function are param1 and param2, and that the month of param1 and param2 is result.
|
||||
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 database node 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 objects in a stored procedure for which no schema name is specified. If no schema name is specified for a stored procedure, the schema to which the stored procedure belongs is searched preferentially. If the stored procedure is not found, the following operations are performed:
|
||||
correct_to_number |
Specifies the compatibility of the to_number() result.
|
||||
unbind_divide_bound |
Specifies the range check on the result of integer division. This parameter does not take effect in M-compatible databases.
|
||||
convert_string_digit_to_numeric |
Specifies whether to convert numeric constants of the character string type to those of the numeric type before these two types are compared. This parameter does not take effect in M-compatible databases.
|
||||
return_null_string |
Specifies how to display the empty result (empty string '') of the lpad() and rpad() functions. This parameter does not take effect in M-compatible databases.
|
||||
compat_concat_variadic |
Specifies the compatibility of variadic results of the concat() and concat_ws() functions. The B database does not have the variadic type. Therefore, this option has no impact on the B database.
|
||||
merge_update_multi |
When MERGE INTO ... WHEN MATCHED THEN UPDATE (see "SQL Reference" > "SQL Syntax" > "M" > "MERGE INTO" in Developer Guide) and INSERT ... ON DUPLICATE KEY UPDATE (see "SQL Reference" > "SQL Syntax" > "I" > "INSERT" in Developer Guide) are used, it controls the UPDATE behavior if a piece of target data in the target table conflicts with multiple pieces of source data.
|
||||
plstmt_implicit_savepoint |
Determines whether the execution of an UPDATE statement in a stored procedure has an independent subtransaction. If this parameter is set, the implicit savepoint is enabled before executing each UPDATE statement in the stored procedure, and the subtransaction is rolled backed to the latest savepoint in the EXCEPTION block by default, ensuring that only the modification of failed statements is rolled back. This option is used to be compatible with the EXCEPTION behavior of the ORA database. |
||||
hide_tailing_zero |
Configuration item for numeric display.
For example: gaussdb=# set behavior_compat_options='hide_tailing_zero'; gaussdb=# select cast(123.123 as numeric(15,10)) as a, to_char(cast(123.123 as numeric(15,10)), '999D999999'); a | to_char ---------+---------- 123.123 | 123.123 (1 row) gaussdb=# set behavior_compat_options=''; gaussdb=# select cast(123.123 as numeric(15,10)) as a, to_char(cast(123.123 as numeric(15,10)), '999D999999'); a | to_char ----------------+------------- 123.1230000000 | 123.123000 (1 row) |
||||
truncate_numeric_tail_zero |
Configuration item for numeric display.
For example: gaussdb=# set behavior_compat_options='truncate_numeric_tail_zero'; gaussdb=# select cast(123.123 as numeric(15,10)) as a, to_char(cast(123.123 as numeric(15,10)), '999D999999'); a | to_char ---------+------------- 123.123 | 123.123000 (1 row) gaussdb=# set behavior_compat_options=''; gaussdb=# select cast(123.123 as numeric(15,10)) as a, to_char(cast(123.123 as numeric(15,10)), '999D999999'); a | to_char ----------------+------------- 123.1230000000 | 123.123000 (1 row) |
||||
char_coerce_compat |
Controls the behavior when char(n) types are converted to other variable-length string types. This parameter is valid only when the sql_compatibility parameter is set to A. After this parameter is enabled, spaces at the end are not omitted in implicit conversion, explicit conversion, or conversion by calling the text(bpchar) function.
gaussdb=# set behavior_compat_options=''; gaussdb=# create table tab_1(col1 varchar(3)); gaussdb=# create table tab_2(col2 char(3)); gaussdb=# insert into tab_2 values(' '); gaussdb=# insert into tab_1 select col2 from tab_2; gaussdb=# select * from tab_1 where col1 is null; col1 ------ (1 row) gaussdb=# select * from tab_1 where col1=' '; col1 ------ (0 rows) gaussdb=# delete from tab_1; gaussdb=# set behavior_compat_options = 'char_coerce_compat'; gaussdb=# insert into tab_1 select col2 from tab_2; gaussdb=# select * from tab_1 where col1 is null; col1 ------ (0 rows) gaussdb=# select * from tab_1 where col1=' '; col1 ------ (1 row) gaussdb=# drop table tab_1; gaussdb=# drop table tab_2; |
||||
rownum_type_compat |
Specifies the ROWNUM type. The default value is BIGINT. After this parameter is specified, the value is changed to NUMERIC. gaussdb=# set behavior_compat_options=''; gaussdb=# create table tb_test(c1 int,c2 varchar2,c3 varchar2); gaussdb=# insert into tb_test values(1,'a','b'); gaussdb=# create or replace view v_test as select rownum from tb_test; gaussdb=# \d+ v_test View "public.v_test" Column | Type | Modifiers | Storage | Description --------+--------+-----------+---------+------------- rownum | bigint | | plain | View definition: SELECT ROWNUM AS "rownum" FROM tb_test; gaussdb=# set behavior_compat_options = 'rownum_type_compat'; gaussdb=# create or replace view v_test1 as select rownum from tb_test; gaussdb=# \d+ v_test1 View "public.v_test1" Column | Type | Modifiers | Storage | Description --------+---------+-----------+---------+------------- rownum | numeric | | main | View definition: SELECT ROWNUM AS "rownum" FROM tb_test; gaussdb=# drop view v_test; gaussdb=# drop view v_test1; gaussdb=# drop table tb_test; |
||||
aformat_null_test |
Specifies the logic for checking whether rowtype is null. This parameter does not take effect in M-compatible databases. When this parameter is set, if one column in a row is not empty, true is returned for checking whether rowtype is not null. When this parameter is not set, if all columns in a row are not empty, true is returned for checking whether rowtype is not null. This parameter has no influence on checking whether rowtype is null. gaussdb=# set behavior_compat_options='aformat_null_test'; gaussdb=# select r, r is null as isnull, r is not null as isnotnull from (values (1,row(1,2)), (1,row(null,null)), (1,null), (null,row(1,2)), (null,row(null,null)), (null,null) ) r(a,b); r | isnull | isnotnull -------------+--------+----------- (1,"(1,2)") | f | t (1,"(,)") | f | t (1,) | f | t (,"(1,2)") | f | t (,"(,)") | f | t (,) | t | f (6 rows) gaussdb=# set behavior_compat_options=''; gaussdb=# select r, r is null as isnull, r is not null as isnotnull from (values (1,row(1,2)), (1,row(null,null)), (1,null), (null,row(1,2)), (null,row(null,null)), (null,null) ) r(a,b); r | isnull | isnotnull -------------+--------+----------- (1,"(1,2)") | f | t (1,"(,)") | f | t (1,) | f | f (,"(1,2)") | f | f (,"(,)") | f | f (,) | t | f (6 rows) |
||||
aformat_regexp_match |
Determines the matching behavior of regular expression functions.
When this parameter is set and sql_compatibility is set to A or B or in an M-compatible database, the options supported by the flags parameter of the regular expression are as follows:
Otherwise, the meanings of the options supported by the flags parameter of the regular expression are as follows:
|
||||
disable_rewrite_nesttable |
If this parameter is enabled, rewriting columns of the tableof type in the pg_type table will be disabled. That is, when you read the pg_type table, the actual stored value of the tableof type is displayed. |
||||
disable_emptystr2null |
In the A-compatible parameter binding scenario, if this parameter is enabled, the function of converting the values of parameters of the character type from an empty string to null by default, if any, is disabled. text, clob, blob, raw, bytea, varchar, nvarchar2, bpchar, char, name, byteawithoutorderwithqualcol, and byteawithoutordercol. This parameter is reserved for emergency. Do not set it unless necessary. |
||||
compat_cursor |
Determines the compatibility behavior of implicit cursor states. If this parameter is set and the A-compatible mode is used, the effective scope of implicit cursor states (SQL%FOUND, SQL%NOTFOUND, SQL%ISOPNE, and SQL%ROWCOUNT) is extended from only the currently executed function to all subfunctions called by this function. |
||||
allow_procedure_compile_check |
Controls the compilation check of the SELECT and OPEN CURSOR statements in a stored procedure. If this parameter is set, when the SELECT, OPEN CURSOR FOR, CURSOR%rowtype, or FOR rec IN statement is executed in a stored procedure, the stored procedure cannot be created if the queried table does not exist, and the compilation check of the trigger function is not supported. If the queried table exists, the stored procedure is successfully created. Note: When creating an encrypted function, you need to disable allow_procedure_compile_check. |
||||
skip_insert_gs_source |
If this parameter is enabled, no data is inserted into the dbe_pldeveloper.gs_source table when the PL/SQL objects are created. |
||||
select_into_return_null |
If this parameter is enabled in PG-compatible mode, NULL values can be assigned to the variables in the stored procedure statement SELECT select_expressions INTO [STRICT] target FROM... without specifying STRICT when the query result is empty. After this parameter is enabled, if the returned result set is empty, the NULL value is assigned to the variable. For example: gaussdb=# SET behavior_compat_options = 'select_into_return_null'; SET gaussdb=# CREATE TABLE t1 (a int, b int); CREATE TABLE gaussdb=# DECLARE gaussdb-# var int; gaussdb-# BEGIN gaussdb$# SELECT b INTO var FROM t1; gaussdb$# RAISE INFO '%', var; gaussdb$# end; gaussdb$# / INFO: <NULL> ANONYMOUS BLOCK EXECUTE |
||||
plsql_security_definer |
After this parameter is enabled, the definer permission is used by default when a stored procedure is created. |
||||
proc_outparam_override |
Determines the overloading of output parameters of a stored procedure. After this parameter is enabled, the stored procedure can be properly created and called even if only the output parameters of the stored procedure are different. The syntax of a stored procedure is also checked during compilation. Currently, this parameter can be used only when gsql and JDBC are used to connect to the database. If this parameter is enabled for other tools to connect to the database, stored procedures with the out parameter cannot be called. It supports the functions that contain the out output parameter and returns data of the record type. Besides, a value is assigned to the out parameter. |
||||
proc_implicit_for_loop_variable |
Specifies the behavior of the FOR_LOOP query statement in a stored procedure. When this parameter is set, if rec has been defined in the FOR rec IN query LOOP statement, the defined rec variable is not reused but a new variable is created. Otherwise, the defined rec variable is reused and no variable is created. |
||||
proc_uncheck_default_param |
When a function is called, the system does not check whether the default parameter is omitted.
|
||||
dynamic_sql_compat |
After this parameter is enabled:
NOTE:
If a stored procedure is called when a dynamic statement executes an anonymous block statement, only the IN parameters are corrected. If OUT parameters need to be checked, set the proc_outparam_override parameter. |
||||
dynamic_sql_check |
After this parameter is enabled, an error is reported during dynamic statement execution if the number of different template parameters in the dynamic statement template SQL is different from that of variables in the USING clause.
NOTE:
|
||||
enable_funcname_with_argsname |
If the parameter is enabled, the projection alias displays the complete function when SELECT is used to call a function.
|
||||
proc_outparam_transfer_length |
After this parameter is enabled, the length of the out output parameter can be transferred in the stored procedure and function, and an error is reported in the inner stored procedure or function. For example:
gaussdb=# SET behavior_compat_options='proc_outparam_override,proc_outparam_transfer_length'; SET gaussdb=# CREATE OR REPLACE PROCEDURE out_param_test1(m in int, v inout varchar2,v1 inout varchar2) is gaussdb$# begin gaussdb$# v := 'aaaddd'; gaussdb$# v1 := 'aaaddd'; gaussdb$# end; gaussdb$# / CREATE PROCEDURE gaussdb=# CREATE OR REPLACE PROCEDURE call_out_param_test1 is gaussdb$# v varchar2(5) := 'aabbb'; gaussdb$# v1 varchar2(6) := 'aabb'; gaussdb$# begin gaussdb$# out_param_test1(5,v,v1); gaussdb$# end; gaussdb$# / CREATE PROCEDURE gaussdb=# CALL call_out_param_test1(); ERROR: value too long for type character varying(5) CONTEXT: PL/SQL function out_param_test1(integer,character varying,character varying) line 3 at assignment PL/SQL function call_out_param_test1() line 4 at SQL statement gaussdb=# DROP PROCEDURE call_out_param_test1; gaussdb=# DROP PROCEDURE out_param_test1; |
||||
varray_compat |
After this parameter is enabled:
For details about behavior differences and examples, see "Stored Procedure" > "Arrays, Collections, and Records" > Arrays" in Developer Guide.
NOTE:
|
||||
tableof_elem_constraints |
After this parameter is enabled:
For details about behavior differences and examples, see "Stored Procedure" > "Arrays, Collections, and Records" > "Collections" in Developer Guide.
NOTE:
The following is an example of verifying the validity of elements: If this parameter is not enabled, the elements are not verified when the following stored procedure is called: gaussdb=# CREATE OR REPLACE procedure p1 is gaussdb$# type t1 is table of varchar(5); gaussdb$# v t1 := t1(); gaussdb$# begin gaussdb$# v.extend(); gaussdb$# v(1) := '123456'; gaussdb$# raise info '%', v; gaussdb$# end; gaussdb$# / CREATE PROCEDURE gaussdb=# CALL p1(); INFO: {123456} p1 ---- (1 row) gaussdb=# DROP procedure p1; If this parameter is enabled, the elements are verified and an error may be reported when the following stored procedure is called: gaussdb=# SET behavior_compat_options = 'tableof_elem_constraints'; SET gaussdb=# CREATE OR REPLACE procedure p1 is gaussdb$# type t1 is table of varchar(5); gaussdb$# v t1 := t1(); gaussdb$# begin gaussdb$# v.extend(); gaussdb$# v(1) := '123456'; gaussdb$# raise info '%', v; gaussdb$# end; gaussdb$# / CREATE PROCEDURE gaussdb=# CALL p1(); ERROR: value too long for type character varying(5) CONTEXT: PL/SQL function p1() line 5 at assignment The following is an example of verifying the index value: If this parameter is not enabled, no error is reported when the index value exceeds the defined length. gaussdb=# CREATE OR REPLACE procedure p1 is gaussdb$# type t1 is table of int index by varchar(5); gaussdb$# v t1; gaussdb$# begin gaussdb$# v('123456') := 1; gaussdb$# raise info '%', v; gaussdb$# end; gaussdb$# / CREATE PROCEDURE gaussdb=# CALL p1(); INFO: {indexbyType:varchar,"123456"=>1} p1 ---- (1 row) gaussdb=# DROP procedure p1; If this parameter is enabled, an error is reported when the index value exceeds the defined length. gaussdb=# SET behavior_compat_options = 'tableof_elem_constraints'; SET gaussdb=# CREATE OR REPLACE procedure p1 is gaussdb$# type t1 is table of int index by varchar(5); gaussdb$# v t1; gaussdb$# begin gaussdb$# v('123456') := 1; gaussdb$# raise info '%', v; gaussdb$# end; gaussdb$# / CREATE PROCEDURE gaussdb=# CALL p1(); ERROR: value too long for type character varying(5) CONTEXT: PL/SQL function p1() line 4 at assignment |
||||
allow_rownum_alias |
After this parameter is enabled, ROWNUM can be used as a column alias in SQL statements using the AS syntax. ROWNUM is used as a common identifier and cannot be used as a pseudocolumn. This parameter does not take effect in M-compatible databases.
NOTE:
You are advised not to change the status of this parameter during service execution. When the parameter is enabled, database objects (such as table names, column names, and database names) are created using ROWNUM as the name in the database. If the parameter is disabled, ambiguity occurs and the behavior is unpredictable. When the parameter is disabled, the behavior of using ROWNUM as a pseudocolumn in the database becomes invalid after the parameter is enabled and the behavior is unpredictable. |
||||
current_sysdate |
If this parameter is enabled, the current OS time is obtained when the sysdate command is executed. gaussdb=# set behavior_compat_options='current_sysdate'; SET gaussdb=# select sysdate; current_sysdate --------------------- 2023-06-20 20:15:27 (1 row) |
||||
allow_function_procedure_replace |
In the default scenario, for A and PG compatibility modes, when there is a stored procedure (or function) outside the package, you cannot create a function (or stored procedure) with the same name because they are of different object types, and an error is reported. After this parameter is enabled, you can use the CREATE OR REPLACE method to replace stored procedures and functions with the same names outside the package. gaussdb=# create or replace function proc_test return varchar2 as gaussdb$# begin gaussdb$# return '1'; gaussdb$# end; gaussdb$# / CREATE FUNCTION gaussdb=# create or replace procedure proc_test as gaussdb$# begin gaussdb$# null; gaussdb$# end; gaussdb$# / ERROR: cannot change routine kind DETAIL: "proc_test" is a function. gaussdb=# \df+ proc_test List of functions Schema | Name | Result data type | Argument data types | Type | Volatility | Owner | Language | Source code | Description | fencedmode | propackage | prokind --------+-----------+-------------------+---------------------+--------+------------+-----------+----------+-------------+-------------+------------+------------+--------- public | proc_test | character varying | | normal | volatile | wangxinyu | plpgsql | DECLARE +| | f | f | f | | | | | | | | begin +| | | | | | | | | | | | return '1';+| | | | | | | | | | | | end | | | | (1 row) -- Types can be replaced after the parameter is set. gaussdb=# set behavior_compat_options='allow_function_procedure_replace'; SET gaussdb=# create or replace procedure proc_test as gaussdb$# begin gaussdb$# null; gaussdb$# end; gaussdb$# / CREATE PROCEDURE gaussdb=# \df+ proc_test List of functions Schema | Name | Result data type | Argument data types | Type | Volatility | Owner | Language | Source code | Description | fencedmode | propackage | prokind --------+-----------+------------------+---------------------+--------+------------+-----------+----------+-------------+-------------+------------+------------+--------- public | proc_test | void | | normal | volatile | wangxinyu | plpgsql | DECLARE +| | f | f | p | | | | | | | | begin +| | | | | | | | | | | | null; +| | | | | | | | | | | | end | | | | (1 row) gaussdb=# drop procedure proc_test; |
||||
collection_exception_backcompat |
Exception value thrown when an error related to the collection type is reported in the PL/SQL. Currently, three exception values are controlled. The mapping is as follows: Parameter not enabled Parameter enabled collection_is_null program_limit_exceeded subscript_beyond_count program_limit_exceeded subscript_outside_limit program_limit_exceeded Example: gaussdb=# create or replace procedure p1 is gaussdb$# type t1 is table of int; gaussdb$# v t1; gaussdb$# v_int int; gaussdb$# begin gaussdb$# v_int := v.count(); gaussdb$# exception when collection_is_null then gaussdb$# raise info '%', sqlerrm; gaussdb$# end; gaussdb$# / CREATE PROCEDURE gaussdb=# call p1(); INFO: Reference to uninitialized collection p1 ---- (1 row) gaussdb=# create or replace procedure p1 is gaussdb$# type t1 is table of int; gaussdb$# v t1 := t1(1, 2, 3); gaussdb$# v_int int; gaussdb$# begin gaussdb$# v_int := v(4); gaussdb$# exception when subscript_beyond_count then gaussdb$# raise info '%', sqlerrm; gaussdb$# end; gaussdb$# / CREATE PROCEDURE gaussdb=# call p1(); INFO: Subscript beyond count p1 ---- (1 row) gaussdb=# create or replace procedure p1 is gaussdb$# type t1 is table of int; gaussdb$# v t1 := t1(1, 2, 3); gaussdb$# v_int int; gaussdb$# begin gaussdb$# v_int := v(-1); gaussdb$# exception when subscript_outside_limit then gaussdb$# raise info '%', sqlerrm; gaussdb$# end; gaussdb$# / CREATE PROCEDURE gaussdb=# call p1(); INFO: Subscript outside of limit p1 ---- (1 row) gaussdb=# set behavior_compat_options = 'collection_exception_backcompat'; SET gaussdb=# create or replace procedure p1 is gaussdb$# type t1 is table of int; gaussdb$# v t1; gaussdb$# v_int int; gaussdb$# begin gaussdb$# v_int := v.count(); gaussdb$# exception when program_limit_exceeded then gaussdb$# raise info '%', sqlerrm; gaussdb$# end; gaussdb$# / CREATE PROCEDURE gaussdb=# call p1(); INFO: Reference to uninitialized collection p1 ---- (1 row) gaussdb=# create or replace procedure p1 is gaussdb$# type t1 is table of int; gaussdb$# v t1 := t1(1, 2, 3); gaussdb$# v_int int; gaussdb$# begin gaussdb$# v_int := v(4); gaussdb$# exception when program_limit_exceeded then gaussdb$# raise info '%', sqlerrm; gaussdb$# end; gaussdb$# / CREATE PROCEDURE gaussdb=# call p1(); INFO: Subscript beyond count p1 ---- (1 row) gaussdb=# create or replace procedure p1 is gaussdb$# type t1 is table of int; gaussdb$# v t1 := t1(1, 2, 3); gaussdb$# v_int int; gaussdb$# begin gaussdb$# v_int := v(-1); gaussdb$# exception when program_limit_exceeded then gaussdb$# raise info '%', sqlerrm; gaussdb$# end; gaussdb$# / CREATE PROCEDURE gaussdb=# call p1(); INFO: Subscript outside of limit p1 ---- (1 row) gaussdb=# drop procedure p1; |
||||
enable_case_when_alias |
If this parameter is enabled, the aliases of the CASE WHEN and DECODE syntaxes are character strings starting with __unnamed_. Example: gaussdb=# set behavior_compat_options='enable_case_when_alias'; SET gaussdb=# create table test(c1 varchar2); CREATE TABLE gaussdb=# insert into test values('x'); INSERT 0 1 gaussdb=# select decode(c1,'x','0','default') from test; __unnamed_decode__ -------------------- 0 (1 row) gaussdb=# select (case c1 when 'x' then '0' else 'default' end) from test; __unnamed_case_when__ ----------------------- 0 (1 row) gaussdb=# drop table test; |
||||
plsql_rollback_keep_user |
Determines whether ROLLBACK and ROLLBACK TO SAVEPOINT in PL/SQL changes the current user. If this parameter is enabled, ROLLBACK in the PL/SQL does not change the current user. Example: gaussdb=# create user plsql_rollback1 PASSWORD '********'; gaussdb=# create user plsql_rollback2 PASSWORD '********'; gaussdb=# grant plsql_rollback1 to plsql_rollback2; gaussdb=# create or replace procedure plsql_rollback1.p1 () authid definer gaussdb-# as gaussdb$# va int; gaussdb$# begin gaussdb$# raise info 'current usr:%', current_user; gaussdb$# rollback; gaussdb$# raise info 'current usr:%', current_user; gaussdb$# end; gaussdb$# / CREATE PROCEDURE gaussdb=# set session AUTHORIZATION plsql_rollback2 PASSWORD '********'; SET gaussdb=> set behavior_compat_options = 'plsql_rollback_keep_user'; SET gaussdb=> call plsql_rollback1.p1 (); INFO: current usr:plsql_rollback1 INFO: current usr:plsql_rollback1 p1 ---- (1 row)
NOTE:
|
||||
enable_bpcharlikebpchar_compare |
Enables or disables the bpcharlikebpchar and bpcharnlikebpchar operators.
NOTE:
|
||||
cursor_asensitive |
Used for forward compatibility of cursor data-sensitive behavior. This item is not supported in the centralized mode. |
||||
show_full_error_lineno |
Displays the number of the line that contains the header information of a stored procedure. When compilation error information is printed during the creation of a stored procedure, the number of the line where the CREATE statement starts is displayed. When a statement with compilation problems is called, there are two cases: if the recompilation is non-invalid, the number of the line where the CREATE statement starts is displayed; if an error is reported indicating invalid recompilation, the line number is displayed based on the original logic. To display the error information, you must have permission to view gs_source which contains the original definition of the stored procedure; otherwise, the number of the line where the error is reported is displayed based on the original logic. If an error is reported when the stored procedure is executed, after this parameter is set, the number of the line where the CREATE statement starts is displayed. This parameter takes effect only in centralized mode. gaussdb=# set behavior_compat_options='show_full_error_lineno'; gaussdb=# set plsql_compile_check_options='plsql_expression_check'; gaussdb=# CREATE OR REPLACE function proclex1(type1 int) return int is var1 int; var2 int; begin var3 := 30; --- var3 is not defined. return type1; end; / WARNING: "var3" is not a known variable DETAIL: N/A CONTEXT: compilation of PL/pgSQL function "proclex1" near line 6 WARNING: Function created with compilation errors. |
||||
enable_crosstype_integer_operator |
Enables or disables the cross-type integer operator.
NOTE:
|
||||
forbid_package_function_with_prefix |
After this parameter is enabled, an error is reported if a function with a prefix is created in a package. For example: -- Enable a parameter. gaussdb=# SET behavior_compat_options='forbid_package_function_with_prefix'; SET -- Create a schema sch1. gaussdb=# CREATE SCHEMA sch1; -- An error is reported after a package is created. gaussdb=# CREATE PACKAGE pck1 IS PROCEDURE sch1.pck1(); END pck1; / ERROR: not support procedure name use *.* format in package at or near ";" CONTEXT: compilation of PL/pgSQL package near line 1 -- Delete the schema sch1. gaussdb=# DROP SCHEMA sch1; |
||||
time_constexpr_compact |
If this parameter is enabled, in a scenario where the time expression is executed, the type automatically returned is with timezone or without timezone depending on whether the constant carries the time zone. Currently, the timestamp and time types are supported. -- Execute a timestamp expression without a time zone. gaussdb=# SELECT timestamp '1999-03-15 8:00:00'; timestamp --------------------- 1999-03-15 08:00:00 (1 row) -- The type timestamp with time zone is returned after a timestamp expression with a time zone is executed. gaussdb=# SELECT timestamp '1999-03-15 8:00:00 -8:00:00'; timestamptz ------------------------ 1999-03-16 00:00:00+08 (1 row) |
||||
enable_use_ora_timestamptz |
After it is enabled, the date and time obtained by the TIMESTAMP AT TIME ZONE syntax are the same as those in A-compatible database. GaussDB can display only the session time zone, and the A-compatible database can display the entered time zone. If the time zones are different, the displayed results are different. After the date and time are converted to the same time zone, the same time point is displayed. -- Before enabling a parameter gaussdb=# SELECT timestamp '2024-03-20 01:30:00' at time zone 'Europe/Moscow' FROM dual; timezone ---------------------- 2024-03-20 06:30:00+08 (1 row) -- After enabling a parameter gaussdb=# SET behavior_compat_options='enable_use_ora_timestamptz'; SET gaussdb=# SELECT timestamp '2024-03-20 01:30:00' at time zone 'Europe/Moscow' FROM dual; timezone ---------------------- 2024-03-20 01:30:00+08 (1 row)
NOTE:
This parameter is valid only in an A-compatible database. |
||||
forbid_skip_tableof_empty_str_elem |
It is used to control the input behavior of the collection type without indexes for the character string in the format of '{1,,3}'.
|
||||
sys_function_without_brackets |
After this parameter is enabled, system functions without input parameters in A-compatible mode can be called without parentheses.
|
||||
enable_ora_joinop_in_updatestmt |
Specifies whether the subquery supports the operator (+) when the set clause in the UPDATE statement contains subqueries. After this parameter is enabled, the subquery returns a result with (+). gaussdb=# SET behavior_compat_options='enable_ora_joinop_in_updatestmt'; gaussdb=# DROP TABLE t1; gaussdb=# DROP TABLE t2; gaussdb=# DROP TABLE t3; gaussdb=# CREATE TABLE t1(a1 int, b int); CREATE TABLE gaussdb=# CREATE TABLE t2(c1 int, d int); CREATE TABLE gaussdb=# CREATE TABLE t3(a2 int, c2 int, e int); CREATE TABLE gaussdb=# INSERT INTO t1 VALUES(1, 11),(2, 12); INSERT 0 2 gaussdb=# INSERT INTO t2 VALUES(3, 23),(4, 24); INSERT 0 2 gaussdb=# INSERT INTO t3 VALUES(1, 3, 31),(5, 6, 32); INSERT 0 2 gaussdb=# CREATE TABLE result(col1 int, col2 int, col3 int, col4 int, col5 int,col6 int, col7 int); CREATE TABLE gaussdb=# INSERT INTO result SELECT * FROM t1, t2, t3 WHERE t1.a1=t3.a2 AND t2.c1=t3.c2; INSERT 0 1 gaussdb=# UPDATE result SET (col1, col2, col3, col4 ,col5,col6,col7) = ( SELECT a1,b,c1,d,a2,c2,e FROM t1, t2, t3 WHERE t1.a1=t3.a2(+) AND t3.c2=t2.c1(+)); ERROR: More than one row returned by a subquery used as an expression. CONTEXT: referenced column: col1 |
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Adjust the parameter value based on the database-compatible objects.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.
plsql_compile_check_options
Parameter description: Specifies the database compatibility configuration item. This parameter can be set at the PDB level.
Parameter type: string.
Unit: none
Value range: Currently, only compatibility configuration items listed in Table 5 are supported. Use commas (,) to separate multiple compatibility configuration items, for example, set plsql_compile_check_options='for_loop,outparam'.
Default value: ''. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.
Configuration Item |
Behavior |
---|---|
for_loop |
Controls the behavior of the FOR_LOOP query statement in a stored procedure. When this parameter is set, if rec has been defined in the FOR rec IN query LOOP statement, the defined rec variable is not reused. Instead, a new variable is created. Otherwise, the defined rec variable is reused and no variable is created. (It is the same as proc_implicit_for_loop_variable and will be incorporated later.) |
outparam |
Multiple overloaded functions with the same name but different output parameter lists may exist. If this option is enabled, the out output parameter is checked. If the out output parameter is a constant, an error is reported. |
plsql_expression_check |
Enables the plsql_expression_check parameter. It verifies full statements in functions, stored procedures, and packages. If an undefined object exists (for example, a table, variable, or function does not exist), an alarm is generated.
NOTE:
The following is an example of the verification function:
gaussdb=# CREATE OR REPLACE procedure proc1() AS var1 int; begin SELECT id into var1 from table1_not_exist limit 1;-- The table does not exist, and an alarm is generated. SELECT id into var1 from view1_not_exist limit 1;-- The view does not exist, and an alarm is generated. var1 := func_input1(func1_not_exist());-- The function does not exist, and an alarm is generated. var1 := pkg.var1_not_exist;-- The package variable does not exist, and an alarm is generated. end; /
The following command output indicates that the creation is successful and the alarm information is printed:
WARNING: compile failed when parse the query: select id from table1_not_exist limit 1, error info is relation "table1_not_exist" does not exist on datanode DETAIL: N/A CONTEXT: compilation of PL/SQL function "proc1" near line 3 WARNING: compile failed when parse the query: select id from view1_not_exist limit 1, error info is relation "view1_not_exist" does not exist on datanode DETAIL: N/A CONTEXT: compilation of PL/SQL function "proc1" near line 4 WARNING: compile failed when parse the query: SELECT func_input1(func1_not_exist()), error info is function func1_not_exist() does not exist DETAIL: N/A CONTEXT: compilation of PL/SQL function "proc1" near line 5 WARNING: compile failed when parse the query: SELECT pkg.var1_not_exist, error info is missing FROM-clause entry for table "pkg" DETAIL: N/A CONTEXT: compilation of PL/SQL function "proc1" near line 6 WARNING: Procedure created with compilation errors. CREATE PROCEDURE False alarms: Example 1: gaussdb=# CREATE TABLE emp(deptno smallint, ename char(100), salary int); gaussdb=# CREATE TABLE emp_back(deptno smallint, ename char(100), salary int); gaussdb=# CREATE OR REPLACE PROCEDURE proc_200() As BEGIN for data in delete from emp returning * loop INSERT INTO emp_back values(data.deptno,data.ename,data.salary);-- This alarm is falsely reported. END LOOP; END; / WARNING: compile failed when parse the query: INSERT INTO emp_back values(data.deptno,data.ename,data.salary), error info is record "data" is not assigned yet when get datum type info DETAIL: N/A CONTEXT: compilation of PL/pgSQL function "proc_200" near line 4 WARNING: Procedure created with compilation errors. CREATE PROCEDURE
Example 2:
gaussdb=# CREATE OR REPLACE procedure proc1() is BEGIN DROP TABLE if exists table1; CREATE TABLE table1(id int); INSERT INTO table1 values(1);-- The system incorrectly reports that table1 does not exist. END; / WARNING: compile failed when parse the query: insert into table1 values(1), error info is relation "table1" does not exist on datanode DETAIL: N/A CONTEXT: compilation of PL/SQL function "proc1" near line 2 WARNING: Procedure created with compilation errors. CREATE PROCEDURE -- The status of pg_object is invalid. gaussdb=# SELECT object_type,valid from pg_object obj join pg_proc proc on obj.object_oid = proc.oid and proc.proname = 'proc1'; object_type | valid -------------+------- P | f (1 row) gaussdb=# ALTER procedure proc1 compile; WARNING: compile failed when parse the query: insert into table1 values(1), error info is relation "table1" does not exist on datanode DETAIL: N/A CONTEXT: compilation of PL/SQL function "proc1" near line 5 WARNING: Procedure proc1 recompile with compilation errors. ALTER PROCEDURE gaussdb=# SELECT object_type,valid from pg_object obj join pg_proc proc on obj.object_oid = proc.oid and proc.proname = 'proc1'; object_type | valid -------------+------- P | f (1 row) gaussdb=# CALL proc1(); NOTICE: table "table1" does not exist, skipping CONTEXT: SQL statement "drop table if exists table1" PL/SQL function proc1() line 3 at SQL statement proc1 ------- (1 row) gaussdb=# SELECT object_type,valid from pg_object obj join pg_proc proc on obj.object_oid = proc.oid and proc.proname = 'proc1'; object_type | valid -------------+------- P | t (1 row) When a function, stored procedure, or package is created or recompiled, the verification function is executed. If this alarm is falsely reported, the valid column of pg_object changes to false. In the execution phase, no verification is performed. When the execution is successful, the valid column of pg_object changes to true. |
a_format_version
Parameter description: Specifies the compatibility configuration item of the database platform. This parameter can be set at the PDB level.
Parameter type: string.
Unit: none
Value range: Currently, only the item in Table 6 is supported.
Default value: "10c" In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.
a_format_dev_version
Parameter description: Specifies the database platform minor version compatibility configuration item. This parameter can be set at the PDB level.
Parameter type: string.
Unit: none
Value range: Currently, only the items in Table 7 are supported.
Default value: "s6" In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.
Configuration Item |
Behavior |
---|---|
s1 |
|
s2 |
|
s3 |
|
s4 |
|
s5 |
|
s6 |
|
b_format_version
Parameter description: Specifies the database platform compatibility behavior configuration item that controls the forward compatibility in B-compatible mode. This parameter can be set at the PDB level.

- b_format_version takes effect only when sql_compatibility is set to B.
- If this parameter is set to a non-empty string, b_format_behavior_compat_options is set to "ALL" and bytea_output parameter is set to "escape" simultaneously. If this parameter is set to an empty string, b_format_behavior_compat_options and bytea_output are set to the original values.
Parameter type: string.
Unit: none
Value range: an empty string "" or "5.7"
Default value: "". In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Set this parameter together with b_format_dev_version immediately after the installation and deployment. You do not need to change the value unless necessary.
Risks and impacts of improper settings: The behaviors of some SQL statements are affected, causing compatibility issues.
b_format_dev_version
Parameter description: Specifies the database platform minor version compatibility configuration item. This parameter can be set at the PDB level.

b_format_dev_version takes effect only when sql_compatibility is set to B and b_format_version is set to "5.7".
Parameter type: string.
Unit: none
Value range: an empty string "" or a compatibility configuration item in Table 8.
Default value: "". In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Set this parameter together with b_format_version immediately after the installation and deployment. You do not need to change the value unless necessary.
Risks and impacts of improper settings: The behaviors of some SQL statements are affected, causing compatibility issues.
m_format_dev_version
Parameter description: Specifies the database platform minor version compatibility configuration item.
Parameter type: string.
Unit: none
Value range: an empty string "" or a compatibility configuration item in Table 9.
Configuration Item |
Behavior |
---|---|
s1 |
The following syntax is influenced:
|
s2 |
The following operators are influenced:
The following views are affected:
The following syntax is influenced:
The following GUC parameter is affected:
The following functions are affected:
|
Default value: ""
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Set this parameter immediately after the installation and deployment. You do not need to change the value unless necessary.
Risks and impacts of improper settings: The behaviors of some SQL statements are affected, causing compatibility issues.
sql_mode
Parameter description: Specifies the SQL behavior control configuration item in B-compatible and M-compatible modes. This parameter can be set at the PDB level.

- sql_mode takes effect when sql_compatibility is set to B, b_format_version is set to '5.7', and b_format_dev_version is set to 's1', or when sql_mode takes effect only when sql_compatibility is set to M.
- When SHOW sql_mode and SELECT @@sql_mode are used for query, the values are displayed in uppercase based on the sequence as follows: REAL_AS_FLOAT, ANSI_QUOTES, ONLY_FULL_GROUP_BY, NO_UNSIGNED_SUBTRACTION, NO_AUTO_VALUE_ON_ZERO, NO_BACKSLASH_ESCAPES, STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ALLOW_INVALID_DATES, ERROR_FOR_DIVISION_BY_ZERO, TRADITIONAL, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION, and PAD_CHAR_TO_FULL_LENGTH.
Parameter type: string.
Unit: none
Value range:
- B-compatible mode: an empty string "" or a value in the range of "strict_trans_tables,only_full_group_by,no_zero_in_date,no_zero_date,error_for_division_by_zero,no_auto_create_user,no_engine_substitution,pad_char_to_full_length,no_auto_value_on_zero".
- M-compatible mode: parameters supported in B-compatible mode, no_unsigned_subtraction, ansi_quotes, allow_invalid_dates, real_as_float, strict_all_tables, and traditional.
The meaning of each option value is described in Table 10.
Configuration Item |
Behavior |
B-compatible Parameter Configuration |
M-compatible Parameter Configuration |
---|---|---|---|
strict_trans_tables |
If this parameter is set, its format and range are strictly verified. If an invalid value is entered or the value exceeds the range, an error is reported during parsing. If this parameter is not set, the format and range of the input parameter are loosely verified. If an invalid value is entered or the value exceeds the range, a warning is reported during parsing and the value 0 of the corresponding data is returned. |
It can be set and canceled independently. |
It can be set and canceled independently. |
only_full_group_by |
Projection columns that contain non-GROUP BY keys and that are not constants and aggregate functions are not allowed. |
It can be set and canceled independently. |
It can be set and canceled independently. |
no_zero_in_date |
The year, month, and day of the DATE cannot be 0. |
It can be set and canceled independently, but it does not take effect. |
It can be set and canceled independently. |
no_zero_date |
The value of DATE cannot be 0 (0000-00-00). |
It can be set and canceled independently, but it does not take effect. |
It can be set and canceled independently. |
error_for_division_by_zero |
The value cannot be divided by 0. |
It can be set and canceled independently, but it does not take effect. |
It can be set and canceled independently. |
no_auto_create_user |
This item has no actual function. It is used only for compatibility that an error is not reported when the SET SQL_MODE statement contains this option. |
It can be set and canceled independently. |
It can be set and canceled independently. |
no_engine_substitution |
This item has no actual function. It is used only for compatibility that an error is not reported when the SET SQL_MODE statement contains this option. |
It can be set and canceled independently. |
It can be set and canceled independently. |
pad_char_to_full_length |
Be used for formatted output of CHAR columns in a table. If there are CHAR columns, a string with spaces at the end is output. Otherwise, a string without spaces at the end is output. |
It can be set and canceled independently. |
It can be set and canceled independently. |
no_auto_value_on_zero |
If this option is included, the value 0 inserted into the AUTO_INCREMENT column does not trigger auto-increment. |
It can be set and canceled independently. |
It can be set and canceled independently. |
no_unsigned_subtraction |
Unsigned numbers cannot be subtracted. If unsigned integers are subtracted and the result is negative, an error is returned. |
It can be set and canceled independently, but it does not take effect. |
It can be set and canceled independently. |
ansi_quotes |
ANSI_QUOTES mode is enabled. In this mode, double quotation marks are treated as identifier quotes rather than string quotes, and you must quote the entire name of a table or column with double quotation marks instead of backquotes. |
It can be set and canceled independently, but it does not take effect. |
It can be set and canceled independently. |
allow_invalid_dates |
If this option is enabled, MySQL allows invalid dates to be inserted, for example, '0000-00-00'. |
It can be set and canceled independently, but it does not take effect. |
It can be set and canceled independently. |
real_as_float |
By default, the REAL type is regarded as the DOUBLE type. If this option is enabled, the REAL type is regarded as the FLOAT type. |
It can be set and canceled independently, but it does not take effect. |
It can be set and canceled independently. |
strict_all_tables |
The effect is the same as that of strict_trans_tables. |
It can be set and canceled independently. |
It can be set and canceled independently. |
traditional |
The effect is the same as that of setting strict_trans_tables, strict_all_tables, no_zero_in_date, no_zero_date, error_for_division_by_zero, no_auto_create_user, and no_engine_substitution together. |
It can be set and canceled independently.
NOTE:
Whether this parameter takes effect depends on whether the B-compatible mode is supported. |
It can be set and canceled independently. |
Default value: "strict_trans_tables,only_full_group_by,no_zero_in_date,no_zero_date,error_for_division_by_zero,no_auto_create_user,no_engine_substitution". In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.
foreign_key_checks
Parameter description: Specifies whether to enable integrity check for FOREIGN KEY constraints. In the multi-tenancy scenario, this parameter can be set at the PDB level.

This parameter takes effect only in M-compatible mode.
Parameter type: Boolean.
Unit: none
Value range:
- on: The integrity check of FOREIGN KEY constraints is enabled.
- off: The integrity check of FOREIGN KEY constraints is disabled.
Default value: on. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Set this parameter to off only during data import and ensure that the original data meets the integrity of FOREIGN KEY constraints to improve import efficiency.
Risks and impacts of improper settings: If this parameter is set to off, DML operations on referenced and referenced tables do not trigger integrity check of foreign key. After a DML operation is performed, the table may contain data that conflicts with foreign key integrity. In addition, when this parameter is enabled again, the system does not check whether the existing data complies with the FOREIGN KEY constraint integrity.

- DDL operations:
- When a table is created, the system checks whether the table name matches an existing foreign key (the referenced table does not exist) regardless of whether foreign_key_checks is enabled. If it matches an existing foreign key, the system checks whether the foreign key definition is correct, and converts the foreign key that does not exist in the referenced table to the foreign key that exists in the referenced table.
- When you add a foreign key, if the referenced table referenced by the foreign key does not exist, an error message is displayed if foreign_key_checks is set to on, and a foreign key that does not exist in the referenced table is created if foreign_key_checks is set to off.
- If CASCADE is specified when a referenced table is deleted, foreign keys on the referenced table are deleted in cascading mode regardless of whether foreign_key_checks is enabled. If CASCADE is not specified and foreign_key_checks is set to on, an error message is displayed. If foreign_key_checks is set to off, foreign keys are converted to foreign keys that do not exist in the referenced table.
- If CASCADE is specified when TRUNCATE is used to reference a table, data in the reference table is deleted regardless of whether foreign_key_checks is enabled. If CASCADE is not specified and foreign_key_checks is set to on, an error message is displayed. If foreign_key_checks is set to off, no error message is displayed and data in the reference table is not cleared.
- DML operations: For details, see Table 11.
Operation |
foreign_key_checks |
Referenced Table |
Reference Table (Referenced Table Exists) |
Reference Table (Referenced Table Does Not Exist) |
---|---|---|---|---|
INSERT |
on |
No check is required and the insertion is successful. |
The integrity is checked. If the integrity conflicts, the insertion fails. |
A non-null value cannot be inserted into the foreign key column. |
off |
No check is required and the insertion is successful. |
The check is skipped and the insertion is successful. |
The check is skipped and the insertion is successful. |
|
DELETE |
on |
The integrity is checked. If the integrity conflicts, an error is reported based on the ACTION operation or the cascading modification is performed. |
No check is required and the deletion is successful. |
No check is required and the deletion is successful. |
off |
The check is skipped and the deletion is successful. |
No check is required and the deletion is successful. |
No check is required and the deletion is successful. |
|
UPDATE |
on |
The integrity is checked. If the integrity conflicts, an error is reported based on the ACTION operation or the cascading modification is performed. |
The integrity is checked. If the integrity conflicts, the update fails. |
A non-null value cannot be updated to the foreign key column. |
off |
The check is skipped and the update is successful. |
The check is skipped and the update is successful. |
The check is skipped and the update is successful. |
|
REPLACE INTO |
on |
The integrity is checked. If the integrity conflicts, an error is reported based on the ACTION operation or the cascading modification is performed. |
The integrity is checked. If the integrity conflicts, the operation fails. |
A non-null value cannot be added or updated to the foreign key column. |
off |
The check is skipped and the operation is successful. |
The check is skipped and the operation is successful. |
The check is skipped and the operation is successful. |
auto_increment_increment
Parameter description: Specifies the auto-increment step of an auto-increment column. The auto-increment value is calculated by the following formula: auto_increment_offset + N × auto_increment_increment. N is a positive integer. In the multi-tenancy scenario, this parameter can be set at the PDB level.
Parameter type: integer.
Unit: none
Value range: 1 to 65535
Default value: 1. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Set this parameter as required.
Risks and impacts of improper settings: The self-increment value may not meet the expectation. If the parameter value is smaller than that of auto_increment_offset, an error occurs when the values in the auto-increment column automatically increase.
auto_increment_offset
Parameter description: Specifies the initial value of an auto-increment column. The auto-increment value is calculated by the following formula: auto_increment_offset + N × auto_increment_increment. N is a positive integer. In the multi-tenancy scenario, this parameter can be set at the PDB level.
Parameter type: integer.
Unit: none
Value range: 1 to 65535
Default value: 1. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Set this parameter as required.
Risks and impacts of improper settings: The self-increment value may not meet the expectation. If the parameter value is greater than that of auto_increment_increment, an error occurs when the values in the auto-increment column automatically increase.
auto_increment_cache
Parameter description: Specifies the number of reserved auto-increment cache values when auto-increment is triggered by batch insertion or import of auto-increment columns. When auto-increment values are reserved, the auto-increment counter value is updated to the maximum auto-increment cache value. Before the cache values are used up, the auto-increment counter value remains unchanged, and the triggered auto-increment uses the cache values. In the multi-tenancy scenario, this parameter can be set at the PDB level.

- The reserved cache values are valid only in the statement. If the reserved auto-increment cache values are used up and subsequent INSERT statements trigger auto-increment based on the auto-increment counter, the values in the auto-increment column in the table are discontinuous.
- When auto-increment is triggered by parallel import or insertion of auto-increment columns, the cache value reserved for each parallel thread is used only in the thread. If the cache value is not used up, the values of auto-increment columns in the table are discontinuous.
- When you add an auto-increment column to a table with data or modify a column to an auto-increment column, the existing data triggers auto-increment. The reserved auto-increment cache value is also affected by this parameter.
- This parameter does not affect the auto-increment column in the local temporary table.
- When sql_compatibility is set to 'B', this parameter takes effect only when b_format_version is set to '5.7' and b_format_dev_version is set to 's2'.
Parameter type: integer.
Unit: none
Value range: 0 to 2147483647
- If this parameter is set to 0, the auto-increment cache values are automatically reserved.
- When auto-increment is triggered for the first time, if the number of rows to be inserted into the auto-increment column is known, the number is the reserved value.
For example, the number of rows to be inserted cannot be obtained as the value of auto-increment that is triggered by INSERT INTO table SELECT ... or COPY FROM. When the ALTER TABLE statement is executed to rewrite table data, if auto-increment is triggered, reltuples in the statistics is used as the number of rows to be reserved. INSERT INTO table VALUES(...),(...),... is distributed to different DNs. Therefore, in some execution plans, DNs cannot obtain the number of rows to be inserted.
- If the number of rows is unknown, 2^n values are reserved each time. For example, one value is reserved in the first auto-increment, two values are reserved in the second auto-increment, four values are reserved in the third auto-increment, and eight values are reserved for in fourth auto-increment. However, if the number of reserved values exceeds 65535, 65535 values are reserved.
- When auto-increment is triggered for the first time, if the number of rows to be inserted into the auto-increment column is known, the number is the reserved value.
- If this parameter is not set to 0, the number of reserved cache values is the value of this parameter.
- When auto-increment is triggered for the first time, if the number of rows to be inserted into the auto-increment column is known, the number is the reserved value.
- If the number of rows is unknown, the value of auto_increment_cache is the number of auto-increment values reserved each time.
Default value: 0. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: The default value is recommended. However, if the auto-increment values are discontinuous in this case, you can adjust the parameter value based on the amount of data to be inserted in batches.
Risks and impacts of improper settings: A larger parameter value indicates better batch insertion performance and more likely to generate discontinuous self-increment values.
disable_keyword_options
Parameter description: Specifies database compatibility behavior. Multiple items are separated by commas (,). An identifier with this parameter set will not be used as a keyword. This parameter can be set at the PDB level.
Parameter type: string.
Unit: none
Value range: The keywords vary depending on compatibility modes of databases. For details, see Table 12.
Keyword |
A Compatibility |
B, C, and PG Compatibilities |
M Compatibility |
---|---|---|---|
access |
Not supported |
Not supported |
Supported |
active |
Not supported |
Not supported |
Supported |
adddate |
Supported |
Supported |
Not supported |
admin |
Not supported |
Not supported |
Supported |
advanced |
Supported |
Supported |
Not supported |
array |
Not supported |
Not supported |
Supported |
authentication |
Not supported |
Not supported |
Supported |
authid |
Not supported |
Not supported |
Supported |
auto |
Supported |
Supported |
Not supported |
auto_increment |
Supported |
Supported |
Not supported |
automatic |
Supported |
Supported |
Not supported |
bucketcnt |
Not supported |
Not supported |
Supported |
buckets |
Not supported |
Not supported |
Supported |
bulk |
Not supported |
Not supported |
Supported |
called |
Not supported |
Not supported |
Supported |
cancelable |
Not supported |
Not supported |
Supported |
challenge_response |
Not supported |
Not supported |
Supported |
change |
Supported |
Supported |
Not supported |
charset |
Supported |
Supported |
Not supported |
clone |
Not supported |
Not supported |
Supported |
columns |
Supported |
Supported |
Not supported |
colview |
Supported |
Supported |
Not supported |
compile |
Supported |
Supported |
Not supported |
completion |
Supported |
Supported |
Not supported |
component |
Not supported |
Not supported |
Supported |
conflict |
Not supported |
Supported |
Not supported |
containing |
Supported |
Supported |
Not supported |
content |
Not supported |
Not supported |
Supported |
convert |
Supported |
Supported |
Not supported |
crossbucket |
Supported |
Supported |
Not supported |
csn |
Supported |
Supported |
Not supported |
cume_dist |
Not supported |
Not supported |
Supported |
datafiles |
Supported |
Supported |
Not supported |
date_add |
Supported |
Supported |
Not supported |
date_sub |
Supported |
Supported |
Not supported |
datetime |
Supported |
Supported |
Not supported |
day_hour |
Supported |
Supported |
Not supported |
day_microsecond |
Supported |
Supported |
Not supported |
day_minute |
Supported |
Supported |
Not supported |
day_second |
Supported |
Supported |
Not supported |
days |
Supported |
Supported |
Not supported |
db4aishot |
Supported |
Supported |
Not supported |
dbtimezone |
Supported |
Supported |
Not supported |
definition |
Not supported |
Not supported |
Supported |
delete_all |
Supported |
Supported |
Not supported |
dense_rank |
Supported |
Supported |
Supported |
description |
Not supported |
Not supported |
Supported |
disable_all |
Supported |
Supported |
Not supported |
discard_path |
Supported |
Supported |
Not supported |
distributed |
Supported |
Supported |
Not supported |
document |
Not supported |
Not supported |
Supported |
dumpfile |
Supported |
Supported |
Not supported |
empty |
Not supported |
Not supported |
Supported |
enable_all |
Supported |
Supported |
Not supported |
encoding |
Not supported |
Not supported |
Supported |
ends |
Supported |
Supported |
Not supported |
enforced |
Not supported |
Not supported |
Supported |
engine |
Supported |
Supported |
Not supported |
engine_attribute |
Not supported |
Not supported |
Supported |
entityescaping |
Supported |
Supported |
Not supported |
escaped |
Supported |
Supported |
Not supported |
evalname |
Supported |
Supported |
Not supported |
event |
Supported |
Supported |
Not supported |
events |
Supported |
Supported |
Not supported |
except |
Not supported |
Not supported |
Supported |
exclude |
Not supported |
Not supported |
Supported |
exclusive |
Not supported |
Not supported |
Supported |
expdp |
Supported |
Supported |
Not supported |
extend |
Supported |
Supported |
Not supported |
factor |
Not supported |
Not supported |
Supported |
failed_login_attempts |
Not supported |
Not supported |
Supported |
fenced |
Not supported |
Not supported |
Supported |
finish |
Supported |
Supported |
Supported |
first_value |
Not supported |
Not supported |
Supported |
following |
Not supported |
Not supported |
Supported |
force |
Not supported |
Not supported |
Supported |
generate |
Not supported |
Not supported |
Supported |
geomcollection |
Not supported |
Not supported |
Supported |
get_format |
Supported |
Supported |
Not supported |
get_master_public_key |
Not supported |
Not supported |
Supported |
get_source_public_key |
Not supported |
Not supported |
Supported |
grouping |
Not supported |
Not supported |
Supported |
groups |
Not supported |
Not supported |
Supported |
gsidead |
Supported |
Supported |
Not supported |
gsigetxid |
Supported |
Supported |
Not supported |
gsimerge |
Supported |
Supported |
Not supported |
gsiready |
Supported |
Supported |
Not supported |
gsiswitch |
Supported |
Supported |
Not supported |
gsiusable |
Supported |
Supported |
Not supported |
gsivalid |
Supported |
Supported |
Not supported |
gsiwaitall |
Supported |
Supported |
Not supported |
gtid_only |
Not supported |
Not supported |
Supported |
high |
Supported |
Supported |
Not supported |
histogram |
Not supported |
Not supported |
Supported |
history |
Not supported |
Not supported |
Supported |
hour_microsecond |
Supported |
Supported |
Not supported |
hour_minute |
Supported |
Supported |
Not supported |
hour_second |
Supported |
Supported |
Not supported |
ifnull |
Supported |
Supported |
Not supported |
ignore |
Supported |
Supported |
Not supported |
ilm |
Supported |
Supported |
Not supported |
ilm_pidx_list |
Supported |
Supported |
Not supported |
imcvtable |
Supported |
Supported |
Not supported |
impdp |
Supported |
Supported |
Not supported |
inactive |
Not supported |
Not supported |
Supported |
initial |
Not supported |
Not supported |
Supported |
initiate |
Not supported |
Not supported |
Supported |
intersect |
Not supported |
Not supported |
Supported |
interval |
Supported |
Supported |
Not supported |
invisible |
Supported |
Supported |
Supported |
json_object |
Supported |
Supported |
Not supported |
json_table |
Not supported |
Not supported |
Supported |
json_value |
Not supported |
Not supported |
Supported |
keep |
Supported |
Supported |
Not supported |
keyring |
Not supported |
Not supported |
Supported |
lag |
Not supported |
Not supported |
Supported |
last_day |
Supported |
Supported |
Not supported |
last_value |
Not supported |
Not supported |
Supported |
lateral |
Not supported |
Not supported |
Supported |
lc_collate |
Not supported |
Not supported |
Supported |
lead |
Not supported |
Not supported |
Supported |
leakproof |
Not supported |
Not supported |
Supported |
least |
Not supported |
Not supported |
Supported |
lines |
Supported |
Supported |
Not supported |
link |
Supported |
Supported |
Not supported |
lnnvl |
Supported |
Supported |
Not supported |
load_bad |
Not supported |
Not supported |
Supported |
load_discard |
Supported |
Supported |
Supported |
load_integer_len |
Supported |
Supported |
Not supported |
load_session_id |
Supported |
Supported |
Not supported |
load_smallint_len |
Supported |
Supported |
Not supported |
load_unfixed_len |
Supported |
Supported |
Not supported |
load_unfixed_start_pos |
Supported |
Supported |
Not supported |
location |
Not supported |
Not supported |
Supported |
locked |
Supported |
Supported |
Supported |
low |
Supported |
Supported |
Not supported |
manual |
Supported |
Supported |
Not supported |
mark |
Supported |
Supported |
Not supported |
master_compression_algorithms |
Not supported |
Not supported |
Supported |
master_public_key_path |
Not supported |
Not supported |
Supported |
master_tls_ciphersuites |
Not supported |
Not supported |
Supported |
master_zstd_compression_level |
Not supported |
Not supported |
Supported |
match |
Not supported |
Not supported |
Supported |
mediumint |
Supported |
Supported |
Not supported |
member |
Not supported |
Not supported |
Supported |
microsecond |
Supported |
Supported |
Not supported |
minute_microsecond |
Supported |
Supported |
Not supported |
minute_second |
Supported |
Supported |
Not supported |
minvalue |
Not supported |
Not supported |
Supported |
modification |
Supported |
Supported |
Not supported |
months |
Supported |
Supported |
Not supported |
move |
Not supported |
Not supported |
Supported |
nested |
Not supported |
Not supported |
Supported |
nocache |
Supported |
Supported |
Not supported |
nocolview |
Supported |
Supported |
Not supported |
nocycle |
Not supported |
Not supported |
Supported |
node |
Not supported |
Not supported |
Supported |
noentityescaping |
Supported |
Supported |
Not supported |
noextend |
Supported |
Supported |
Not supported |
nomaxvalue |
Not supported |
Not supported |
Supported |
nominvalue |
Not supported |
Not supported |
Supported |
noscale |
Supported |
Supported |
Not supported |
now |
Supported |
Supported |
Not supported |
nowait |
Not supported |
Not supported |
Supported |
nth_value |
Not supported |
Not supported |
Supported |
ntile |
Not supported |
Not supported |
Supported |
nulls |
Not supported |
Not supported |
Supported |
nvl |
Not supported |
Not supported |
Supported |
nvl2 |
Supported |
Supported |
Supported |
of |
Not supported |
Not supported |
Supported |
off |
Not supported |
Not supported |
Supported |
offline |
Supported |
Supported |
Not supported |
oids |
Not supported |
Not supported |
Supported |
oj |
Not supported |
Not supported |
Supported |
old |
Not supported |
Not supported |
Supported |
online |
Supported |
Supported |
Not supported |
open |
Supported |
Supported |
Not supported |
operator |
Not supported |
Not supported |
Supported |
optional |
Not supported |
Not supported |
Supported |
ordinality |
Supported |
Supported |
Supported |
organization |
Not supported |
Not supported |
Supported |
others |
Not supported |
Not supported |
Supported |
outfile |
Supported |
Supported |
Not supported |
over |
Not supported |
Not supported |
Supported |
owned |
Not supported |
Not supported |
Supported |
partitioning |
Supported |
Supported |
Not supported |
password_lock_time |
Not supported |
Not supported |
Supported |
path |
Not supported |
Not supported |
Supported |
percent_rank |
Not supported |
Not supported |
Supported |
performance |
Supported |
Supported |
Not supported |
persist |
Not supported |
Not supported |
Supported |
persist_only |
Not supported |
Not supported |
Supported |
pivot |
Supported |
Supported |
Not supported |
pluggable |
Supported |
Supported |
Not supported |
populate |
Supported |
Supported |
Not supported |
position |
Not supported |
Not supported |
Supported |
preceding |
Not supported |
Not supported |
Supported |
prepared |
Not supported |
Not supported |
Supported |
priority |
Supported |
Supported |
Not supported |
privilege_checks_user |
Not supported |
Not supported |
Supported |
process |
Not supported |
Not supported |
Supported |
public |
Supported |
Supported |
Not supported |
quarter |
Supported |
Supported |
Not supported |
random |
Not supported |
Not supported |
Supported |
rank |
Not supported |
Not supported |
Supported |
recover |
Supported |
Supported |
Not supported |
recursive |
Not supported |
Not supported |
Supported |
recyclebin |
Not supported |
Not supported |
Supported |
reference |
Not supported |
Not supported |
Supported |
regexp |
Supported |
Supported |
Not supported |
regexp_like |
Supported |
Supported |
Not supported |
registration |
Not supported |
Not supported |
Supported |
reindex |
Not supported |
Not supported |
Supported |
reject |
Not supported |
Not supported |
Supported |
relative |
Not supported |
Not supported |
Supported |
replica |
Not supported |
Not supported |
Supported |
replicas |
Not supported |
Not supported |
Supported |
require_row_format |
Not supported |
Not supported |
Supported |
resource |
Not supported |
Not supported |
Supported |
respect |
Supported |
Supported |
Supported |
restart |
Not supported |
Not supported |
Supported |
retain |
Not supported |
Not supported |
Supported |
returning |
Not supported |
Not supported |
Supported |
reuse |
Not supported |
Not supported |
Supported |
rlike |
Supported |
Supported |
Not supported |
role |
Not supported |
Not supported |
Supported |
row_number |
Not supported |
Not supported |
Supported |
scale |
Supported |
Supported |
Not supported |
schedule |
Supported |
Supported |
Not supported |
scroll |
Not supported |
Not supported |
Supported |
second_microsecond |
Supported |
Supported |
Not supported |
secondary |
Not supported |
Not supported |
Supported |
secondary_engine |
Not supported |
Not supported |
Supported |
secondary_engine_attribute |
Not supported |
Not supported |
Supported |
secondary_load |
Not supported |
Not supported |
Supported |
secondary_unload |
Not supported |
Not supported |
Supported |
separator |
Supported |
Supported |
Not supported |
sequence |
Not supported |
Not supported |
Supported |
sessiontimezone |
Supported |
Supported |
Not supported |
setof |
Not supported |
Not supported |
Supported |
shippable |
Not supported |
Not supported |
Supported |
shrink |
Supported |
Supported |
Not supported |
signed |
Supported |
Supported |
Not supported |
size |
Not supported |
Not supported |
Supported |
skip |
Not supported |
Not supported |
Supported |
slave |
Supported |
Supported |
Not supported |
slice |
Not supported |
Not supported |
Supported |
slicegroup |
Supported |
Supported |
Supported |
smalldatetime |
Not supported |
Not supported |
Supported |
smalldatetime_format |
Not supported |
Not supported |
Supported |
source_auto_position |
Not supported |
Not supported |
Supported |
source_bind |
Not supported |
Not supported |
Supported |
source_compression_algorithms |
Not supported |
Not supported |
Supported |
source_connect_retry |
Not supported |
Not supported |
Supported |
source_delay |
Not supported |
Not supported |
Supported |
source_heartbeat_period |
Not supported |
Not supported |
Supported |
source_host |
Not supported |
Not supported |
Supported |
source_log_file |
Not supported |
Not supported |
Supported |
source_log_pos |
Not supported |
Not supported |
Supported |
source_password |
Not supported |
Not supported |
Supported |
source_port |
Not supported |
Not supported |
Supported |
source_public_key_path |
Not supported |
Not supported |
Supported |
source_retry_count |
Not supported |
Not supported |
Supported |
source_ssl |
Not supported |
Not supported |
Supported |
source_ssl_ca |
Not supported |
Not supported |
Supported |
source_ssl_capath |
Not supported |
Not supported |
Supported |
source_ssl_cert |
Not supported |
Not supported |
Supported |
source_ssl_cipher |
Not supported |
Not supported |
Supported |
source_ssl_crl |
Not supported |
Not supported |
Supported |
source_ssl_crlpath |
Not supported |
Not supported |
Supported |
source_ssl_key |
Not supported |
Not supported |
Supported |
source_ssl_verify_server_cert |
Not supported |
Not supported |
Supported |
source_tls_ciphersuites |
Not supported |
Not supported |
Supported |
source_tls_version |
Not supported |
Not supported |
Supported |
source_user |
Not supported |
Not supported |
Supported |
source_zstd_compression_level |
Not supported |
Not supported |
Supported |
specification |
Supported |
Supported |
Not supported |
split |
Not supported |
Not supported |
Supported |
srid |
Not supported |
Not supported |
Supported |
stable |
Not supported |
Not supported |
Supported |
starting |
Supported |
Supported |
Not supported |
starts |
Supported |
Supported |
Not supported |
stdin |
Not supported |
Not supported |
Supported |
stdout |
Not supported |
Not supported |
Supported |
stream |
Not supported |
Not supported |
Supported |
strict |
Not supported |
Not supported |
Supported |
subdate |
Supported |
Supported |
Not supported |
subpartitioning |
Supported |
Supported |
Not supported |
subpartitions |
Supported |
Supported |
Not supported |
substr |
Supported |
Supported |
Not supported |
substring |
Not supported |
Not supported |
Supported |
sysdate |
Not supported |
Not supported |
Supported |
system |
Not supported |
Not supported |
Supported |
thread_priority |
Not supported |
Not supported |
Supported |
ties |
Not supported |
Not supported |
Supported |
timestampadd |
Supported |
Supported |
Not supported |
tls |
Not supported |
Not supported |
Supported |
trim |
Not supported |
Not supported |
Supported |
unbounded |
Not supported |
Not supported |
Supported |
unpivot |
Supported |
Supported |
Not supported |
unregister |
Not supported |
Not supported |
Supported |
unsigned |
Supported |
Supported |
Not supported |
unusable |
Not supported |
Not supported |
Supported |
url |
Not supported |
Not supported |
Supported |
vacuum |
Not supported |
Not supported |
Supported |
valid |
Not supported |
Not supported |
Supported |
varchar2 |
Not supported |
Not supported |
Supported |
vcpu |
Not supported |
Not supported |
Supported |
verbose |
Not supported |
Not supported |
Supported |
verify |
Supported |
Supported |
Not supported |
version |
Not supported |
Not supported |
Supported |
visible |
Supported |
Supported |
Supported |
volatile |
Not supported |
Not supported |
Supported |
week |
Supported |
Supported |
Not supported |
wellformed |
Supported |
Supported |
Not supported |
window |
Not supported |
Not supported |
Supported |
within |
Not supported |
Not supported |
Supported |
xmlattributes |
Not supported |
Not supported |
Supported |
xmlconcat |
Not supported |
Not supported |
Supported |
xmlelement |
Not supported |
Not supported |
Supported |
xmlforest |
Not supported |
Not supported |
Supported |
xmlnamespaces |
Supported |
Supported |
Not supported |
xmlpi |
Not supported |
Not supported |
Supported |
xmlroot |
Not supported |
Not supported |
Supported |
xmltable |
Supported |
Supported |
Not supported |
xmltype |
Supported |
Supported |
Supported |
year_month |
Supported |
Supported |
Not supported |
years |
Supported |
Supported |
Not supported |
zerofill |
Supported |
Supported |
Not supported |
zone |
Not supported |
Not supported |
Supported |
Default value:
- In non-M-compatible database: "datetime,regexp,rlike,zerofill"
- In M-compatible database: ""
- In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: If this parameter is enabled, some functions used as keywords will become invalid. Exercise caution when setting this parameter. If the keyword to be shielded does not exist in the to-be-shielded keyword array of the current compatibility mode database but exists in other compatibility mode databases, WARNING is generated. If the keyword to be shielded does not exist in other compatibility mode databases, ERROR is generated.

In B-compatible mode, whether datetime, regexp, rlike, and zerofill are reserved keywords is determined by the system and cannot be modified or deleted by users. Specifically, in the initial state, the system adds datetime, regexp, rlike, and zerofill to the disable_keyword_options parameter. In this case, datetime, regexp, rlike, and zerofill are not reserved keywords. After b_format_version is set to '5.7' and b_format_dev_version is set to 's1' (or a version later than s1), the system deletes datetime, regexp, rlike, and zerofill from disable_keyword_options. In this case, datetime, regexp, rlike, and zerofill can be used as reserved keywords.
disable_plsql_keyword_options
Parameter description: Specifies the database compatibility behavior, that is, whether to use an identifier as a non-keyword. Values of this parameter are separated by commas (,). In the multi-tenancy scenario, this parameter can be set at the PDB level.
Parameter type: string.
Unit: none
Value range: PIPE, PIPELINED, RANGE, REPLACE, and SUBTYPE.
Default value: "". In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.

- If this parameter is enabled, some functions used as keywords will become invalid. Exercise caution when setting this parameter.
- To cancel the function of shielding the PL/SQL keyword, leave this parameter empty.
plpgsql.variable_conflict
Parameter description: Specifies the priority of using stored procedure variables and table columns with the same name.
Parameter type: string.
Unit: none
Value range:
- error indicates that a compilation error is reported when the name of a stored procedure variable is the same as that of a table column.
- use_variable indicates that if the name of a stored procedure variable is the same as that of a table column, the variable is used preferentially.
- use_column indicates that if the name of a stored procedure variable is the same as that of a table column, the column name is used preferentially.
Default value: error
Setting method: This is a USERSET parameter. Set it using method 3 described in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.
td_compatible_truncation
Parameter description: Specifies whether to enable features compatible with a Teradata database. You can set this parameter to on when connecting to a database compatible with the Teradata database, so that when you perform the INSERT operation, overlong strings are truncated based on the allowed maximum length before being inserted into char- and varchar-type columns in the target table. This ensures all data is inserted into the target table without errors reported. This parameter can be set at the PDB level.

- The string truncation function cannot be used if the INSERT statement includes a foreign table.
- If inserting multi-byte character data (such as Chinese characters) to database with the character set byte encoding (such as SQL_ASCII or LATIN1), and the character data crosses the truncation position, the string is truncated based on its bytes instead of characters. Unexpected result will occur in tail after the truncation. If you want correct truncation result, you are advised to adopt encoding set such as UTF-8, which has no character data crossing the truncation position.
Parameter type: Boolean.
Unit: none
Value range:
- on indicates that overlong strings are automatically truncated.
- off indicates that overlong strings are not automatically truncated.
Default value: off. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.
uppercase_attribute_name
Parameter description: Specifies whether to return column names in uppercase to the client. This parameter is used only in A-compatible mode. This parameter can be set at the PDB level.
Parameter type: Boolean.
Unit: none
Value range:
- on: Column names are returned to the client in uppercase.
- off: Column names are not returned to the client in uppercase.
Default value: off. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.

Do not run gs_guc reload, alter database set, or alter user set or modify gaussdb.conf to modify this parameter globally.
enable_copy_error_log
Parameter description: Enables or disables gs_loader to use the error table pgxc_copy_error_log. This parameter can be set at the PDB level.
Parameter type: Boolean.
Unit: none
Value range:
- on: indicates that the error table is enabled.
- off: indicates that the error table is disabled.
Default value: off. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.
loader_support_nul_character
Parameter description: Specifies the database platform minor version compatibility configuration item. This parameter can be set at the PDB level.
Parameter type: string.
Unit: none
Value range: "", "s1", and "s2". For details, see Table 13.
Default value: "". In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.

- This GUC parameter needs to be set when gs_loader is used to enable fault tolerance for NUL characters (0x00).
- For details, see "Data Import and Export Tools > gs_loader for Importing Data" in Tool Reference.
Configuration Item |
Behavior |
---|---|
s1 |
Specifies how gs_loader processes NUL characters in data files during data import. If a data file contains the NUL character, the data is truncated based on the position of the NUL character. The data before the NUL character is imported to the table, and the data after the NUL character is truncated and discarded. This is used for forward compatibility of the function. |
s2 |
Specifies how gs_loader processes NUL characters in data files during data import. If a data file contains the NUL character, the NUL character is converted to the space character " " (0x20), and then processed, determined, and imported. |
Empty |
The default parameter settings do not affect any functions. |
a_format_copy_version
Parameter description: Specifies the database platform minor version compatibility configuration item. The value of this parameter is an enumerated string. When using gs_loader to import new features, you need to set the corresponding values. This parameter can be set at the PDB level.
Parameter type: string.
Unit: none
Value range: Currently, only the item in Table 14 is supported.
Default value: ". In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.

Set character string for compatibility configuration. For details, see "Data Import and Export Tools > gs_loader for Importing Data" in Tool Reference. You can set the a_format_copy_version parameter by using guc_param.
Configuration Item |
Behavior |
---|---|
s1 |
|
enable_volatile_match_index
Parameter description: Specifies whether volatile functions can match indexes. This parameter is valid only when DBCOMPATIBILITY is set to A. There are semantic risks when the volatile function matches indexes. The stable and immutable functions can match indexes by default and comply with semantics. This parameter can be set at the PDB level.
Parameter type: Boolean.
Unit: none
Value range:
- on: enabled.
- off: disabled.
Default value: off. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a SUSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Set this parameter based on the variability of the function.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.
enable_union_all_order
Parameter description: Specifies whether UNION ALL supports subquery order preserving when the main query is not sorted. This parameter can be set at the PDB level.
Parameter type: Boolean.
Unit: none
Value range:
- on: enabled.
- off: disabled.
Default value: on. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.
enable_immutable_optimization
Parameter description: Specifies whether immutable functions can be optimized. This parameter is valid only when DBCOMPATIBILITY is set to A. If a function with the immutable definition violates the immutable semantics, the call result, value, and impact on the caller are not defined. This parameter can be set at the PDB level.
Parameter type: Boolean.
Unit: none
Value range:
- on: enabled.
- off: disabled.
Default value: on. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.
enable_object_special_character
Parameter description: Determines whether the value of the schema parameter in the control file can contain any special characters in ["$'\] when the CREATE EXTENSION statement is executed and "@extschema@" is used in the script file.
Parameter type: Boolean.
Unit: none
Value range:
- on indicates that the value can contain any special characters in ["$'\].
- off indicates that the value cannot contain any special characters in ["$'\].
Default value: off
Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.
Setting suggestion: The extended function is for internal use only. You are advised not to use it.
Risks and impacts of improper settings: Changing the value of this parameter may cause unexpected results in the database. Retain the default value.
enable_implicit_cast_str2num
Parameter description: Specifies whether a string can be converted to a NUMERIC type when a comparison or arithmetic operator is performed between an integer data type and a string data type.

This parameter is valid only when a_format_version is set to '10c' and a_format_dev_version is set to 's1'.
Parameter type: Boolean.
Unit: none
Value range:
- on: enabled.
Comparison operators (>, >=, <, <=, =, and <>): The string data type is implicitly converted to the NUMERIC type. If the enable_crosstype_integer_operator option of the behavior_compat_options parameter is disabled, the integer data type is implicitly converted to the NUMERIC type.
Arithmetic operators (+, -, *, /,%, and ^): Both integer and string data types are implicitly converted to the NUMERIC type.
- off: disabled. The string data type will be implicitly converted to the integer data type.
Default value: on
Setting method: This is a SUSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.
a_format_disable_func
Parameter description: Disables a specified system function. The value of this parameter consists of multiple system function OIDs separated by commas (,). The system function for which this parameter is set cannot be called. When a system function cannot meet user requirements and a user-defined function with the same name is required, you can use this function. This parameter can be set at the PDB level.
Parameter type: string.
Unit: none
Value range: a string consisting of multiple system function OIDs separated by commas (,).
Default value: "". In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a SUSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.
enable_convert_illegal_char
Parameter description: Specifies whether invalid characters in the command output are not verified and are displayed as placeholders. In the multi-tenancy scenario, this parameter can be set at the PDB level.
Parameter type: Boolean.
Unit: none
Value range:
- on: The parameter is enabled. Special characters are replaced by the symbols specified by the convert_illegal_char_mode parameter during query.
- off: The parameter is disabled. If the query result contains characters that do not comply with the current character set encoding rule, an error is reported after verification.
Default value: off. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value. Enable this parameter only when the data contains special characters and errors should not be reported for special characters.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.

- When the database character set is utf8, zhs16gbk, gb18030, gb18030-2022, or latin1, enable_convert_illegal_char=on takes effect. If the character set of the database client is different from that of the database server, invalid characters are displayed as placeholders.
- Special character range: The special characters mentioned in this document include only fully abnormal encoding and hybrid encoding. The \u0000 character is not supported. If the character code contains the \u0000 character, the character is truncated at \u0000, which affects data integrity.
- If the character sets of the database client and server are different, special characters beyond the character set of the server can be written to the database only by using the dbe_raw.cast_to_varchar2() function or the import and export tool.
- When the GUC parameter is enabled, the behaviors of the special characters in the functions and advanced package functions listed in the following table are as follows:
- When the character sets of the client and server are the same, no error is reported during the query of special characters. The behavior is the same as that before the GUC parameter is enabled.
- If the character sets of the client and server are different, special characters are displayed as placeholders, which use question marks (?) by default.
- You are advised not to use functions to process character strings that contain special characters. The functions listed in the following table do not report errors when processing character strings that contain special characters, and the correctness of the results cannot be ensured.
No. |
Function/Advanced Package Function |
---|---|
1 |
bit_length(string) |
2 |
btrim(string text [, characters text]) |
3 |
char_length(string) character_length(string) |
4 |
chr(cvalue int|bigint) chr(integer) |
5 |
concat(str1,str2) |
6 |
concat_ws(sep text, str"any" [, str"any" [, ...] ]) |
7 |
decode(string text, format text) |
8 |
dump(expr[, return_fmt [, start_position [, length ] ] ]) |
9 |
encode(data bytea, format text) |
10 |
find_in_set(text, set) |
11 |
format(formatstr text [, str"any" [, ...] ]) |
12 |
left(str text, n int) |
13 |
length(string) |
14 |
lengthb(text/bpchar) |
15 |
ltrim(string [, characters]) |
16 |
md5(string) |
17 |
notlike(x bytea name text, y bytea text) |
18 |
octet_length(string) |
19 |
overlay(string placing string FROM int [for int]) |
20 |
quote_ident(string text) |
21 |
quote_literal(string text) |
22 |
quote_nullable(string text) |
23 |
rawcat(raw,raw) |
24 |
regexp_count(string text, pattern text [, position int [, flags text]]) |
25 |
regexp_instr(string text, pattern text [, position int [, occurrence int [, return_opt int [, flags text]]]]) |
26 |
regexp_like(source_string, pattern [, match_parameter]) regexp_like(text,text,text) |
27 |
regexp_matches(string text, pattern text [, flags text]) |
28 |
regexp_replace(string, pattern, replacement [,flags ]) |
29 |
regexp_split_to_array(string text, pattern text [, flags text ]) |
30 |
regexp_split_to_table(string text, pattern text [, flags text]) |
31 |
regexp_substr(source_char, pattern) regexp_substr(string text, pattern text [, position int [, occurrence int [, flags text]]]) |
32 |
repeat(string text, number int ) |
33 |
repexp_replace(string text, pattern text [, replacement text [, position int [, occurrence int [, flags text]]]]) |
34 |
replace(string text, from text, to text) |
35 |
replace(string, substring) |
36 |
reverse(str) |
37 |
right(str text, n int) |
38 |
rtrim(string [, characters]) |
39 |
sha(string) |
40 |
sha1(string) |
41 |
sha2(string, hash_length) |
42 |
split_part(string text, delimiter text, field int) |
43 |
substring(string [from int] [for int]) substring(string from pattern for escape) substring(string from pattern) |
44 |
substring_inner(string [from int] [for int]) |
45 |
tconvert(key text, value text) |
46 |
to_single_byte(char) |
47 |
translate(string text, from text, to text) |
48 |
trim([leading |trailing |both] [characters] from string) |
49 |
unistr(string) |
50 |
vsize(expr) |
51 |
PKG_UTIL.RAW_CAST_FROM_VARCHAR2 |
52 |
PKG_UTIL.LOB_CONVERTTOCLOB |
53 |
PKG_UTIL.LOB_RAWTOTEXT |
54 |
PKG_UTIL.LOB_TEXTTORAW |
55 |
PKG_UTIL.RAW_CAST_TO_VARCHAR2 |
56 |
DBE_OUTPUT.PUT |
57 |
DBE_OUTPUT.PUT_LINE |
fix_func_selection
Parameter description: Specifies whether to optimize the function matching policy. In the multi-tenancy scenario, this parameter can be set at the PDB level.
The catlist sequence issue occurs in this case: If a user-defined function conflicts with a system function, the function selected by the database depends on the registration sequence of the system function in the database system.
Parameter type: string.
Unit: none
Value range: "" and "catlist".
- "": No optimization is performed. The value is the same as that in versions earlier than 505.1.0.
- "catlist": The catlist sequence is optimized. System functions are always preferentially selected and executed.
Default value:
- "catlist": default value of the newly installed database
- "": default value of the database in versions earlier than 505.1.0 after the database is upgraded.
- In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.
enable_force_create_obj
Parameter description: Specifies whether to enable the one-off import function. After this parameter is enabled, if an undefined object exists when functions or packages are created or rebuilt, a virtual object is created for compilation. During function body compilation, try catch is used to capture exceptions so that the creation or rebuilding process can run successfully. In the multi-tenancy scenario, this parameter can be set at the PDB level.
Parameter type: Boolean.
Unit: none
Value range:
- on: The one-off import function is enabled.
- off: The one-off import function is disabled.
Default value: off. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Set this parameter based on the actual scenario. You are advised to enable the corresponding function in normal cases.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.
ddl_invalid_mode
Parameter description: Determines the behavior of objects (functions or packages) that depend on a function or package when the DROP operation is performed on the function or package. In the multi-tenancy scenario, this parameter can be set at the PDB level.

If there is an invalid object during cascading failure dependency, the cascading failure operation is interrupted.
Parameter type: enumerated type
Unit: none
Value range:
- delete: When DROP is performed on a function or package, the objects (functions or packages) that depend on the function or package are cascadingly deleted.
- invalid: When the DROP operation is performed on a function or package, the objects (functions or packages) that depend on the function or package are cascadingly invalidated.
Default value: delete. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Set this parameter based on the actual scenario.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.
max_allowed_packet
Parameter description: Specifies the upper limit of the return value of a string function in M-compatible database. If the actual result exceeds the upper limit, an alarm is generated and NULL is returned.
Parameter type: integer.
Unit: byte
Value range: 1024 to 1073741824. The value must be a multiple of 1024. Otherwise, the value is rounded down to the nearest multiple.
Default value: 4194304
Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.
div_precision_increment
Parameter description: Specifies the database configuration item in M-compatible mode. This parameter specifies the precision improvement value of the division result. That is, the number of decimal places in the division result is equal to the number of decimal places in the divisor plus the value of this parameter. In the multi-tenancy scenario, this parameter can be set at the PDB level.
Parameter type: integer.
Unit: none
Value range: 0 to 30
Default value: 4. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.
m_err_sql_dialect
Parameter description: Specifies the error code display mode. It is a configuration item of the M-compatible database.
Parameter type: string.
Unit: none
Value range:
- GaussDB: Error information uses GaussDB kernel error codes.
- MySQL: Error information uses M-compatible database error codes.
Default value: "GaussDB".
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.
enable_m_format_hook
Parameter description: Specifies the database configuration item in M-compatible mode. This parameter specifies whether the hook in M-compatible mode takes effect. In the multi-tenancy scenario, this parameter can be set at the PDB level.
Parameter type: Boolean.
Unit: none
Value range:
- on: The M-compatible database allows the corresponding parser and executor hooks to be mounted.
- off: The M-compatible database does not mount the corresponding parser and executor hooks.
Default value: on. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: This parameter is used only for external tools that are not fully adapted and cannot be used by users.
Risks and impacts of improper settings: none
net_write_timeout
Parameter description: Specifies the database configuration item in M-compatible mode. This parameter specifies the number of seconds to wait for a block write connection before aborting a write. In the multi-tenancy scenario, this parameter can be set at the PDB level.
Parameter type: integer.
Unit: second
Value range: 1 to 31536000
Default value: 60. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1. The value cannot contain a unit.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.

This parameter is used only to adapt to the MySQL JDBC driver and does not take effect currently.
init_connect
Parameter description: Specifies the database configuration item in M-compatible mode. This parameter indicates the character string executed by the server for each connected client.
Parameter type: string.
Unit: none
Value range: none
Default value: ""
Setting method: Currently, this parameter can only be viewed.
Setting suggestion: This parameter can only be viewed.
Risks and impacts of improper settings: none

This parameter is used only to adapt to the MySQL JDBC driver. You can only run SELECT @@init_connect to query the value and set m_format_dev_version to 's2'.
interactive_timeout
Parameter description: Specifies the database configuration item in M-compatible mode. This parameter indicates the number of seconds the server waits for its activity before closing an interactive connection. The return value is the same as that of session_timeout.
Parameter type: integer.
Unit: second
Value range: 0 to 86400
Default value: 600
Setting method: Currently, this parameter can only be viewed.
Setting suggestion: This parameter can only be viewed.
Risks and impacts of improper settings: none

This parameter is used only to adapt to the MySQL JDBC driver and can be queried only by running SELECT @@interactive_timeout.
wait_timeout
Parameter description: Specifies the database configuration item in M-compatible mode. This parameter indicates the number of seconds the server waits for its activity before closing a non-interactive connection. The return value is the same as that of session_timeout.
Parameter type: integer.
Unit: second
Value range: 0 to 86400
Default value: 600
Setting method: Currently, this parameter can only be viewed.
Setting suggestion: This parameter can only be viewed.
Risks and impacts of improper settings: none

This parameter is used only to adapt to the MySQL JDBC driver and can be queried only by running SELECT @@wait_timeout.
license
Parameter description: Specifies the database configuration item in M-compatible mode. This parameter indicates the license type.
Parameter type: string.
Unit: none
Value range: none
Default value: ""
Setting method: Currently, this parameter can only be viewed.
Setting suggestion: This parameter can only be viewed.
Risks and impacts of improper settings: none

This parameter is used only to adapt to the MySQL JDBC driver and can be queried only by running SELECT @@license.
net_buffer_length
Parameter description: Specifies the database configuration item in M-compatible mode. This parameter specifies the initial size of the connection buffer and result set buffer associated with the client thread.
Parameter type: integer.
Unit: byte
Value range: 1024 to 1048576
Default value: 8192
Setting method: Currently, this parameter can only be viewed.
Setting suggestion: This parameter can only be viewed.
Risks and impacts of improper settings: none

This parameter is used only to adapt to the MySQL JDBC driver and can be queried only by running SELECT @@net_buffer_length.
performance_schema
Parameter description: Specifies the database configuration item in M-compatible mode. This parameter specifies whether to support the query of performance information.
Parameter type: Boolean.
Unit: none
Value range:
- on: Performance information can be queried.
- off: Performance information cannot be queried.
Default value: off
Setting method: Currently, this parameter can only be viewed.
Setting suggestion: This parameter can only be viewed.
Risks and impacts of improper settings: none

This parameter is used only to adapt to the MySQL JDBC driver and can be queried only by running SELECT @@performance_schema.
query_cache_type
Parameter description: Specifies the database configuration item in M-compatible mode. This parameter specifies the query cache type.
Parameter type: string.
Unit: none
Value range:
- on: The cache can be queried.
- off: The cache cannot be queried.
Default value: off
Setting method: Currently, this parameter can only be viewed.
Setting suggestion: This parameter can only be viewed.
Risks and impacts of improper settings: none

This parameter is used only to adapt to the MySQL JDBC driver and can be queried only by running SELECT @@query_cache_type.
query_cache_size
Parameter description: Specifies the database configuration item in M-compatible mode. This parameter specifies the amount of memory allocated for caching query results.
Parameter type: integer.
Unit: byte
Value range: 0 to 18446744073709551615
Default value: 0
Setting method: Currently, this parameter can only be viewed.
Setting suggestion: This parameter can only be viewed.
Risks and impacts of improper settings: none

This parameter is used only to adapt to the MySQL JDBC driver and can be queried only by running SELECT @@query_cache_size.
system_time_zone
Parameter description: Specifies the database configuration item in M-compatible mode. This parameter specifies the system time zone of the server, which is the same as log_timezone.
Parameter type: string.
Unit: none
Value range: same as that of log_timezone.
Default value: Set this parameter based on the OS time zone.
Setting method: Currently, this parameter can only be viewed.
Setting suggestion: This parameter can only be viewed.
Risks and impacts of improper settings: none

This parameter is used only to adapt to the MySQL JDBC driver and can be queried only by running SELECT @@system_time_zone.
time_zone
Parameter description: Specifies the database configuration item in M-compatible mode. This parameter specifies the time zone of the current session, which is the same as TimeZone.
Parameter type: string.
Unit: none
Value range: HH:MM time format, such as '+08:00', and region format, such as 'Asia/Shanghai'.
The time format is [+][H]H[:MM] or –[H]H[:MM]. The time starts with a plus sign (+) or minus sign (–). The plus sign (+) can be omitted. The value ranges from '–12:59' to '+13:00'.
Default value: "PRC"
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.

This parameter is used only to adapt to the MySQL JDBC driver and can be queried only by running SELECT @@time_zone. The time_zone parameter can be set only when m_format_dev_version is set to 's2'.
gs_format_behavior_compat_options
Parameter description: gs_format_behavior_compat_options is used to select configuration items of GaussDB internal system functions. In the multi-tenancy scenario, this parameter can be set at the PDB level.
Parameter type: string.
Unit: none
Value range: Currently, only compatibility configuration items listed in Table 16 are supported. Compatibility configuration items are separated by commas (,). In the PDB scenario, if this parameter is not set, the global setting is inherited.
Default value: "karatsuba"
Configuration Item |
Behavior |
---|---|
sqrt_karatsuba |
|
allow_textconcat_null |
|
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Determines whether some compatibility features are available. If you want to modify this parameter, make sure you understand its meaning and modify it with caution to avoid risks caused by misoperations.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.
support_zero_character
Parameter description: Specifies whether the database cancels the verification of the zero character and correctly processes it without truncating the character when the data contains the character whose Unicode code is \u0000 (referred to as the zero character).
Parameter type: Boolean.
Unit: none
Value range:
- on: enabled. The database cancels the verification of the 0 character and can correctly insert and process the 0 character so that the data is not truncated during processing.
- off: disabled. The database retains the verification of the 0 character. When some functions process the 0 character, an error is reported or data is truncated.
Default value: off
Setting method: This is a POSTMASTER parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value. Enable this parameter only when the data contains the 0 character and the 0 character need to be correctly processed.
Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.

- This parameter takes effect only when the A-compatible database is used and support_zero_character is set to on for all nodes.
- When support_zero_character is set to on and the data type is CHAR(n), CHARACTER(n), NCHAR(n), VARCHAR(n), CHARACTER VARYING(n), VARCHAR2(n), NVARCHAR2(n), TEXT, or CLOB, the database can correctly insert and process 0 characters. If the string data contains 0 characters and is explicitly converted to JSON/JSONB, XML, XMLTYPE, numeric type, currency type, time type, geometric type, network address type, bit string type, text search type, HLL type, range type, object identifier type, ledger database type, aclitem type, or vector type, the string will be truncated at the 0 character. If the string is explicitly converted to the UUID type, an error is reported at the 0 character.
- The database supports only the following modes of writing the 0 character:
- When the database is connected through JDBC, the 0 character can be written to the database in text mode.
- Use the dbe_raw.cast_to_varchar2 function to directly write the 0 character code to the database.
- Use the copy/gs_loader tool to write the 0 character code to the database.
- When the GUC parameter is enabled and different character sets are converted, the 0 character is directly returned and no error is reported.
- The gsql client version must match the database version. When the GUC parameter is enabled, if the data received by gsql contains the 0 character, gsql converts the 0 character to a space and then prints the data. If gsql of an earlier version is used to connect to the database, data containing 0 is still truncated.
- When the GUC parameter is enabled, the 0 character is displayed as follows in the functions listed in Table 17 and advanced package functions: The functions can properly process the 0 character and do not truncate the 0 character.
- The input parameters of some advanced package APIs are schema names or object names, which cannot contain 0 characters theoretically. Therefore, 0 character verification is added to functions in Table 18. When the input parameters contain 0 characters, an error is reported. (Originally, data is truncated after 0 characters.)
- When support_zero_character is set to on, if the processed data does not contain 0 characters, the function performance does not deteriorate significantly. If the processed data contains 0 characters and the same number of valid characters are compared, the performance deterioration degree of the initcap, upper, lower, nls_upper and nls_lower functions is closely related to the number of 0 characters in the data. That is, more 0 characters indicate more serious performance deterioration.
- When support_zero_character is set to on, if the logical decoding tool reads 0 characters in the data, 0 characters are converted to a space and then printed. (Originally, the data is truncated after 0 characters are read.)
No. |
Function/Advanced Package Function |
---|---|
1 |
btrim(string text [, characters text]) |
2 |
char_length(string) or character_length(string) |
3 |
chr(cvalue int|bigint) |
4 |
chr(integer) |
5 |
coalesce(expr1, expr2, ..., exprn) |
6 |
concat(str1,str2) |
7 |
concat_ws(sep text, str"any" [, str"any" [, ...] ]) |
8 |
convert(string bytea, src_encoding name, dest_encoding name) |
9 |
convert_from(string bytea, src_encoding name) |
10 |
convert_to(string text, dest_encoding name) |
11 |
decode(base_expr, compare1, value1, Compare2,value2, ... default) |
12 |
dump(expr[, return_fmt [, start_position [, length ] ] ]) |
13 |
encode(data bytea, format text) |
14 |
format(formatstr text [, str"any" [, ...] ]) |
15 |
greatest(expr1 [, ...]) |
16 |
group_concat([DISTINCT | ALL] expression [,expression ...] [ORDER BY { expression [ [ ASC | DESC | USING operator ] | nlssort_expression_clause ] [ NULLS { FIRST | LAST } ] } [,...]] [SEPARATOR str_val]) |
17 |
initcap(string) |
18 |
instr(string,substring[,position,occurrence]) instr(text,text,int,int) |
19 |
instrb(text,text,int,int) |
20 |
least(expr1 [, ...]) |
21 |
left(str text, n int) |
22 |
length(string) length(string bytea, encoding name ) |
23 |
lengthb(text/bpchar) |
24 |
listagg(expression [, delimiter]) WITHIN GROUP(ORDER BY order-list) |
25 |
lower(string) |
26 |
lpad(string text, length int [, fill text]) |
27 |
ltrim(string [, characters]) |
28 |
nls_lower(string [, nlsparam]) |
29 |
nls_upper(string [, nlsparam]) |
30 |
nullif(expr1, expr2) |
31 |
nvl( expr1 , expr2 ) |
32 |
nvl2( expr1, expr2, expr3 ) |
33 |
overlay(string placing string FROM int [for int]) |
34 |
position(substring in string) |
35 |
quote_nullable(string text) |
36 |
regexp_count(string text, pattern text [, position int [, flags text]]) |
37 |
regexp_instr(string text, pattern text [, position int [, occurrence int [, return_opt int [, flags text]]]]) |
38 |
regexp_like(text,text,text) regexp_like(source_string, pattern [, match_parameter]) |
39 |
regexp_matches(string text, pattern text [, flags text]) |
40 |
regexp_replace(string, pattern, replacement [,flags ]) |
41 |
regexp_split_to_array(string text, pattern text [, flags text ]) |
42 |
regexp_split_to_table(string text, pattern text [, flags text]) |
43 |
regexp_substr(source_char, pattern) regexp_substr(string text, pattern text [, position int [, occurrence int [, flags text]]]) |
44 |
repeat(string text, number int ) |
45 |
replace(string text, from text, to text) replace(string, substring) |
46 |
reverse(str) |
47 |
right(str text, n int) |
48 |
rpad(string text, length int [, fill text]) |
49 |
rtrim(string [, characters]) |
50 |
similar_escape(pat text, esc text) |
51 |
split_part(string text, delimiter text, field int) |
52 |
string [NOT] LIKE pattern [ESCAPE escape-character] |
53 |
string || string, string || non-string, or non-string || string |
54 |
string_agg(expression, delimiter) |
55 |
strpos(string, substring) |
56 |
substr(string,from) substr(bytea,from,count) substr(string,from,count) |
57 |
substrb(text,int) substrb(text,int,int) |
58 |
substring(string [from int] [for int]) substring(string from pattern for escape) substring(string from pattern) substring_inner(string [from int] [for int]) |
59 |
translate(string text, from text, to text) |
60 |
trim([leading |trailing |both] [characters] from string) |
61 |
unistr(string) |
62 |
upper(string) |
63 |
PKG_UTIL.LOB_GET_LENGTH |
64 |
PKG_UTIL.LOB_READ |
65 |
PKG_UTIL.LOB_WRITE |
66 |
PKG_UTIL.LOB_APPEND |
67 |
PKG_UTIL.LOB_COMPARE |
68 |
PKG_UTIL.LOB_MATCH |
69 |
PKG_UTIL.LOB_READ_HUGE |
70 |
PKG_UTIL.LOB_WRITEAPPEND_HUGE |
71 |
PKG_UTIL.LOB_APPEND_HUGE |
72 |
PKG_UTIL.LOB_COPY_HUGE |
73 |
PKG_UTIL.LOB_CONVERTTOBLOB_HUGE |
74 |
PKG_UTIL.IO_PRINT |
75 |
PKG_UTIL.RAW_CAST_FROM_VARCHAR2 |
76 |
PKG_UTIL.FILE_READ |
77 |
PKG_UTIL.FILE_READLINE |
78 |
PKG_UTIL.APP_READ_CLIENT_INFO |
79 |
PKG_UTIL.APP_SET_CLIENT_INFO |
80 |
PKG_UTIL.LOB_CONVERTTOBLOB |
81 |
PKG_UTIL.LOB_CONVERTTOCLOB |
82 |
PKG_UTIL.MATCH_EDIT_DISTANCE_SIMILARITY |
83 |
PKG_UTIL.RAW_CAST_TO_VARCHAR2 |
84 |
PKG_UTIL.APP_SET_MODULE |
85 |
PKG_UTIL.APP_READ_MODULE |
86 |
PKG_UTIL.APP_SET_ACTION |
87 |
PKG_UTIL.APP_READ_ACTION |
88 |
DBE_OUTPUT.PRINT_LINE |
89 |
DBE_OUTPUT.PRINT |
90 |
DBE_OUTPUT.GET_LINE |
91 |
DBE_OUTPUT.GET_LINES |
92 |
DBE_OUTPUT.PUT |
93 |
DBE_OUTPUT.PUT_LINE |
94 |
DBE_UTILITY.CANONICALIZE |
95 |
DBE_UTILITY.COMMA_TO_TABLE |
96 |
DBE_UTILITY.NAME_TOKENIZE |
97 |
DBE_UTILITY.TABLE_TO_COMMA |
98 |
DBE_UTILITY.CANONICALIZE_RET |
99 |
DBE_UTILITY.COMMA_TO_TABLE_FUNC |
100 |
DBE_UTILITY.NAME_SEPARATE |
101 |
DBE_UTILITY.NAME_TOKENIZE_FUNC |
102 |
DBE_UTILITY.NAME_TOKENIZE_LOWER |
103 |
DBE_UTILITY.NAME_TOKENIZE_LOWER_FUNC |
104 |
DBE_UTILITY.TABLE_TO_COMMA_FUNC |
No. |
Advanced Package Function |
---|---|
1 |
PKG_UTIL.SESSION_SET_CONTEXT |
2 |
PKG_UTIL.UTILITY_COMPILE_SCHEMA |
3 |
PKG_UTIL.GS_COMPILE_SCHEMA |
4 |
DBE_UTILITY.NAME_RESOLVE |
5 |
DBE_UTILITY.COMPILE_SCHEMA |
6 |
DBE_UTILITY.SEARCH_CLASS_WITH_NSPOID_ONAME_TYPE |
7 |
DBE_UTILITY.SEARCH_OBJECTS |
8 |
DBE_UTILITY.SEARCH_OBJECTS_SYNONYM_FILL_SCHEMA |
9 |
DBE_UTILITY.SEARCH_PROCEDURE_WITH_NSPOID_ONAME |
10 |
DBE_UTILITY.SEARCH_SYNONYM_WITH_NSPOID_ONAME |
enable_case_intervaltonumeric
Parameter description: Specifies whether INTERVAL can be converted to NUMERIC in CASE statements. This parameter takes effect only in A-compatible mode.
Parameter type: Boolean.
Unit: none
Value range:
- on: INTERVAL can be converted to NUMERIC in the CASE statement.
- off: INTERVAL cannot be converted to NUMERIC in the CASE statement.
Default value: off
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: If this parameter is set to on and then modified to off, an error may be reported for the existing CASE statement.
enable_ecpg_cursor_duplicate_operation
Parameter description: Specifies whether to allow ECPG to control a cursor can be opened or closed repeatedly in A-compatible mode. This parameter is valid only when ECPG is connected to A-compatible databases. In the multi-tenancy scenario, this parameter can be set at the PDB level.
Parameter type: Boolean.
Unit: none
Value range:
- on: A cursor can be opened or closed repeatedly when ECPG connects to an A-compatible database.
- off: A cursor cannot be opened or closed repeatedly when ECPG connects to an A-compatible database.
Default value: on. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: If this parameter is set to off, an error will be reported when a cursor is opened or closed repeatedly.

If the database of this version is directly installed, set this parameter to on. If an earlier version is upgraded to the current version, set this parameter is off.
format_pg_to_timestamp
Parameter description: Controls the behavior of the to_timestamp(string [, fmt]) function in PG-compatible mode. After this parameter is enabled, unsupported formats are automatically skipped. The current version does not support formats such as FF, FF7, FF8, FF9, RR, RRRR, SYYYY, x and X. Dates earlier than 294277-01-01 00:00:00 and negative years (converted to BC) are supported. FF1 to FF6 round off the milliseconds that exceed the precision. When the same items in the time and date conflict (for example, the days specified by DDD and DD are different), a time in the correct format is returned and no error is reported. For details about the conflict behavior, see Table 19. In the multi-tenancy scenario, this parameter can be set at the PDB level.
Parameter type: Boolean.
Unit: none
Value range:
- on: The preceding functions are enabled.
- off: The preceding functions are disabled.
Default value: on. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: If this parameter is enabled, a format that is not supported by GaussDB will become invalid.
Type |
Description |
---|---|
SSSSS conflicts with time. |
Retain minutes and hours of time and the seconds part of SSSSS. |
W and J conflict with time and date. |
Priority: Overwrite J, W, and mmdd in sequence. W: indicates Wednesday by default. Default behavior: Overwrite mmdd with W. |
SSSSS conflicts with SS. |
Retain SS and discard SSSSS. |
DDD conflicts with a date. |
Discard DDD. |
synonym_priv_strict_check
Parameter description: Specifies whether to check access to objects associated with a synonym when you search for an object using the synonym.
Parameter type: Boolean.
Unit: none
Value range:
- on: The access permission on the objects associated with the synonyms is checked when searching for objects by synonyms.
- off: The access permission on the objects associated with the synonyms is not checked when searching for objects by synonyms.
Default value: on.
Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: If the parameter is set to off, the access permission on the objects associated with the synonyms is not checked when searching for objects by synonyms, which may cause unauthorized operations.

If a database of the current version is directly installed, set this parameter to on. If a version is upgraded to the current version, set this parameter is off.
forbid_public_funcname_same_with_sysfunc
Parameter description: Specifies whether to forbid PUBLIC functions to have the same name as system functions in pg_catalog when creating functions and RENAME functions.
Parameter type: Boolean.
Unit: none
Value range:
- on: When creating a function or rename a function, the name of the PUBLIC function cannot be the same as that of a system function in pg_catalog.
- off: When creating a function or rename a function, the name of the PUBLIC function can be the same as that of a system function in pg_catalog.
Default value: on.
Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: If this parameter is set to off, the PUBLIC function can have the same name as a system function in pg_catalog.

If the database of the 505.2.0 version is directly installed, set this parameter to on. If a version earlier than 505.2.0 is upgraded to the current version, set this parameter is off.
enable_cursor_with_hold
Parameter description: Specifies whether a cursor has the WITH HOLD function in A-compatible mode. If it is enabled, a cursor is created without the HOLD keyword, and the SELECT clause does not contain the FOR UPDATE/SHARE clause. The default value is WITH HOLD.
Parameter type: Boolean.
Unit: none
Value range:
- on: If a cursor is created without the HOLD keyword, the default value is WITH HOLD. After this parameter is enabled, CURSOR cursor_name FOR query is equivalent to CURSOR cursor_name WITH HOLD FOR query.
- off: If a cursor is created without the HOLD keyword, the default value is WITHOUT HOLD.
Default value: off.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1. This parameter can be set only in a session.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: If the value is on, all cursors are not released, which may cause memory bloat in sessions.
modify_function_property
Parameter description: Specifies the attributes of related system functions in non-M-compatible mode. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Parameter type: character
Unit: none
Value range: Currently, only the configuration items listed in Table 20 are supported. Compatibility configuration items are separated by commas (,).
Default value: "3257,5562,4164,4073" for a newly installed database. If the GUC parameter does not exist in the basic version to be upgraded, the default value is "". If the GUC parameter exists in the basic version to be upgraded, the original setting is retained.
Setting method: This is a POSTMASTER parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: The attributes of system functions are affected.
OID Corresponding to the System Function |
Description |
---|---|
3257 |
Modifies the variability of the pg_catalog.current_timestamp(numeric) function. When the configuration item exists in the GUC parameter, the variability of the function is 'stable'. When the configuration item does not exist, the variability of the function is 'immutable'. |
4073 |
Modifies the variability of the pg_catalog.text_timestamp(text) function. When the configuration item exists in the GUC parameter, the variability of the function is 'stable'. When the configuration item does not exist, the variability of the function is 'immutable'. |
4164 |
Modifies the variability of the pg_catalog.text_date(text) function. When the configuration item exists in the GUC parameter, the variability of the function is 'stable'. When the configuration item does not exist, the variability of the function is 'immutable'. |
5562 |
Modifies the variability of the pg_catalog.DBTimezone() function. When the configuration item exists in the GUC parameter, the variability of the function is 'stable'. When the configuration item does not exist, the variability of the function is 'immutable'. |
forbid_package_spec_circular_dependency
Parameter description: Specifies the status of the ring object on which the package packet header depends. To use this parameter, enable the enable_force_create_obj parameter.
Parameter type: Boolean.
Unit: none
Value range:
- on: The object status is invalid when the package header dependency is looped.
- off: The object status is valid when the package header dependency is looped.
Default value: on.
Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: If this parameter is set to off, the object status can become valid when the package packet header dependency is looped.

If a database of the current version is directly installed, set this parameter to on. If a version is upgraded to the current version, set this parameter is off.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot