Updated on 2025-06-27 GMT+08:00

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.

Table 1 B-compatible configuration items

Configuration Item

Behavior

enable_set_variables

Specifies whether to enable the enhancement of the SET syntax.

  • If this parameter is not set, user-defined variables and the SET [GLOBAL | SESSION] syntax are not supported.
  • If this parameter is set, the preceding syntax is supported in the B-compatible mode, for example, set @v1 = 1;.

set_session_transaction

Specifies whether to enable the SET SESSION TRANSACTION syntax.

  • If this parameter is not set, SET SESSION TRANSACTION is equivalent to SET LOCAL TRANSACTION.
  • If this parameter is set, the preceding syntax can be used in the B-compatible mode to modify the transaction features in the current session.

enable_modify_column

Specifies whether to enable the ALTER TABLE MODIFY syntax.

  • If this parameter is not set, running ALTER TABLE table_name MODIFY column_name data_type; can change only the data type of the column.
  • If this parameter is set, running ALTER TABLE table_name MODIFY column_name data_type; can change the entire column definition.

default_collation

Specifies whether to enable forward compatibility of the default collation.

  • If this item is not specified, the character set or collation of a column of the character type is not explicitly specified, and the collation of a table is empty, the column uses the default collation.
  • If this item is specified, the collation of a column of the character type inherits the collation of a table (if it is not empty). If the collation of a table is empty, the default collation corresponding to the database is used.

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.

Table 2 M-compatible configuration items

Configuration Item

Behavior

enable_escape_string

Specifies whether escape character control is enabled.

  • If this item is not set, escape character behaviors are controlled by the GUC parameters standard_conforming_strings, escape_string_warning, and backslash_quote related to the GaussDB escape character.
  • If this item is set, all MySQL escape characters except '\0' are supported by default. In addition, the output behavior of '\b', '\r', and '\Z' on the gsql client changes to be the same as that on the MySQL client.

enable_conflict_funcs

Specifies whether to enable the conflict function.

  • If this configuration item is not set, the ceil, format, instr, position, row_number, rank, dense_rank, regexp_instr, regexp_like, regexp_replace, and regexp_substr functions retain the behavior of native system functions of GaussDB.
  • If this configuration item is set and the value of m_format_dev_version is 's2' or a later version, the ceil, format, instr, position, row_number, rank, and dense_rank functions are implemented by the M-compatible database. When the regexp_instr, regexp_like, regexp_replace, and regexp_substr functions are used, an error message is displayed, indicating that the M-compatible database is not supported.

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.

  • If this configuration item is not set:
    • SELECT System function: The output is the system function name.
    • SELECT Expression: The output is ?column?.
    • SELECT Boolean value: The output is a Boolean value.
  • If this configuration item is set, the column name is displayed as all functions or expressions.
    • Only the centralized mode is supported. The commercial client is used for the test.
    • If the displayed column name string contains escape characters, the escape characters are displayed only after m_format_behavior_compat_options is set to 'enable_escape_string'. Otherwise, the escape characters are displayed. For example, SELECT"abc\tdef"; is displayed as abc\tdef when the preceding settings are disabled in M-compatible mode.
      m_db=# set m_format_behavior_compat_options='select_column_name,enable_escape_string';
      SET
      m_db=# select "abc\tdef";
       abc     def 
      -------------
       abc     def
      (1 row)
      
      m_db=# set m_format_behavior_compat_options='select_column_name';
      SET
      m_db=# select "abc\tdef";
       abc\tdef 
      ----------
       abc\tdef
      (1 row)
    • If a column name contains more than 63 characters, the extra characters will be truncated.
    • If the last part of an expression is a comment, the last comment and the space connected to the comment are not displayed.
      m_db=# SELECT 123        /* 456 */;
       123 
      -----
       123
      (1 row)
    • If the expression is a Boolean value, the command output is TRUE or FALSE regardless of the input case.
      m_db=# SELECT true;
       TRUE 
      ------
       t
      (1 row)
    • If the expression is null, the command output is NULL regardless of the input case.
      m_db=# SELECT null;
       NULL 
      ------
      
      (1 row)
    • If the expression contains a hyphen (-), all inputs are output as column names.
      m_db=# SELECT (+-+1);
       (+-+1) 
      --------
           -1
      (1 row)
      
      m_db=# SELECT -true;
       -true 
      -------
          -1
      (1 row)
      
      m_db=# SELECT -null;
       -null 
      -------
      
      (1 row)

enable_precision_decimal

Data type precision.

  • If this configuration item is not set:
    • The CREATE TABLE SELECT statement supports only SELECT table columns. It does not support expressions, functions, constants, or union scenarios (because result type derivation is involved).
  • If this configuration item is not set:
    • The CREATE TABLE SELECT statement does not restrict the preceding scenarios.

forbid_none_space_comment

The single-line comment syntax "-- " is supported.

  • If this configuration item is not set, all content after "--" (no matter whether a space is added after "--") is regarded as a single-line comment.
  • If this configuration item is set, only the content after "-- " (a space following "--") is considered as a single-line comment.

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:
  • If m_format_behavior_compat_options contains enable_load_data_remote_transmission, LOAD DATA cannot be executed together with other SQL statements.

disable_zero_chars_conversion

Converts \0 into spaces when the parameters are specified in text mode through PBE API.

  • If this configuration item is not set, \0 in the parameter is replaced with spaces.
  • If this configuration item is set, \0 in the parameter is not replaced with spaces.

cast_as_new_json

::JSON can be converted to the JSON type of the M-compatible database for the JSON type.

  • If this configuration item is not set, operations such as ::JSON, CREATE TABLE <tablename> AS <SELECT containing JSON type>, and other scenarios involving JSON type conversion through ::JSON will actually convert data to the JSON type in the non-M-compatible database (that is, original GaussDB).
  • If this configuration item is set, operations such as ::JSON, CREATE TABLE <tablename> AS <SELECT containing JSON type>, and other scenarios involving JSON type conversion through ::JSON will actually convert data to the JSON type in the M-compatible database.

grant_database_nomapping

Configuration item of the GRANT | REVOKE ON DATABASE syntax behavior in the M-compatible database.

  • If this configuration item is not set, the GRANT | REVOKE ON DATABASE syntax is mapped to the GRANT | REVOKE ON SCHEMA behavior to grant or revoke the specified user or role's permission to access schema-level objects.
  • If this configuration item is set, the GRANT | REVOKE ON DATABASE syntax retains the behavior of the GRANT | REVOKE ON DATABASE syntax in the GaussDB of the default compatibility, and grants or revokes the access permission of a specified user or role on database objects.
    -- Create a user and a database.
    \c postgres
    DROP USER IF EXISTS testUser cascade; 
    DROP DATABASE IF EXISTS m_db;
    CREATE USER testUser WITH PASSWORD 'Gauss_234';
    CREATE DATABASE m_db DBCOMPATIBILITY='M';
    \c m_db
    
    -- The option is not enabled and the schema is mapped to the grant schema. A message is displayed, indicating that the schema cannot be found.
    m_db=# SET m_format_behavior_compat_options='';
    SET
    m_db=# GRANT CREATE ON DATABASE m_db TO testUser;
    ERROR:  schema "m_db" does not exist
    m_db=# SET SESSION AUTHORIZATION testUser PASSWORD 'Gauss_234';
    SET
    m_db=> CREATE SCHEMA s1;
    ERROR:  Permission denied for database m_db.
    DETAIL:  N/A.
    m_db=> \c -
    
    -- Enable the option. The create schema permission is granted.
    m_db=# SET m_format_behavior_compat_options='grant_database_nomapping';
    SET
    m_db=# GRANT CREATE ON DATABASE m_db TO testUser;
    GRANT
    m_db=# SET SESSION AUTHORIZATION testUser PASSWORD 'Gauss_234';
    SET
    m_db=> CREATE SCHEMA s1;
    CREATE SCHEMA
    m_db=> \c -
    
    -- Clean up.
    \c postgres
    DROP USER IF EXISTS testUser cascade; 
    DROP DATABASE IF EXISTS m_db;

disable_illegal_function_syntax

Controls invalid syntax in system functions.

  • If this item is not set, the system function syntax is available in the following scenarios:
    • System functions without input parameters can be called with the parameter (*).
    • Ordinary system functions can be used together with WITHIN GROUP.
    • Input parameters of ordinary system functions can be used together with ORDER BY.
    • Ordinary system functions can be used together with the OVER clause.
    • Aggregate functions can be used together with WITHIN GROUP.
  • If this item is set, the preceding scenarios will be disabled.
-- 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.

  • If this item is not set, integers and fixed-point constants are converted to integers for comparison. (Assume that an integer column index exists. An index can be executed when an integer column is compared with a fixed-point constant.)
    -- When the disable_int_cmp_num_index option is not enabled, the integer and fixed-point constants are converted to integers for comparison, and indexes can be executed.
    m_db=# SET m_format_behavior_compat_options='';
    SET
    m_db=# DROP TABLE IF EXISTS t1;
    DROP TABLE
    m_db=# CREATE TABLE t1(a BIGINT, b INT);
    CREATE TABLE
    m_db=# CREATE INDEX idx_t1_a ON t1(a);
    CREATE INDEX
    m_db=# INSERT INTO t1 VALUES(generate_series(0, 500), generate_series(0, 500));
    INSERT 0 501
    m_db=# EXPLAIN SELECT * FROM t1 WHERE a < 5.1;
                                    QUERY PLAN
    --------------------------------------------------------------------------
     Bitmap Heap Scan on t1  (cost=13.27..31.37 rows=648 width=12)
       Recheck Cond: (a <= 5::bigint)
       ->  Bitmap Index Scan on idx_t1_a  (cost=0.00..13.11 rows=648 width=0)
             Index Cond: (a <= 5::bigint)
    (4 rows)
    
    m_db=# DROP TABLE IF EXISTS t1;
    DROP TABLE
  • If this item is set, integers and fixed-point constants are converted to fixed-point numbers for comparison. (Assume that an integer column index exists. When an integer column is compared with a fixed-point constant, the integer column index cannot be executed because the integer is converted to a fixed-point number.)
    -- When the disable_int_cmp_num_index option is enabled, the comparison between fixed-point numbers cannot be performed using indexes.
    m_db=# SET m_format_behavior_compat_options='disable_int_cmp_num_index';
    SET
    m_db=# DROP TABLE IF EXISTS t1;
    DROP TABLE
    m_db=# CREATE TABLE t1(a BIGINT, b INT);
    CREATE TABLE
    m_db=# CREATE INDEX idx_t1_a ON t1(a);
    CREATE INDEX
    m_db=# INSERT INTO t1 VALUES(generate_series(0, 500), generate_series(0, 500));
    
    INSERT 0 501
    m_db=# EXPLAIN SELECT * FROM t1 WHERE a < 5.1;
                          QUERY PLAN
    ------------------------------------------------------
     Seq Scan on t1  (cost=0.00..39.17 rows=648 width=12)
       Filter: (cast_to_numeric(a) < 5.1)
    (2 rows)
    
    m_db=# DROP TABLE IF EXISTS t1;
    DROP TABLE

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: ""
Table 3 Compatibility configuration items

Configuration Item

Behavior

get_hash_value_fix

Specifies whether to enable the dbe_utility.get_hash_value forward compatibility.

  • If this configuration item is not set, the last four bytes of the character string entered by the user are ignored. As a result, the hash values of the character strings less than four bytes are the same.
  • If this configuration item is set, the problem is resolved to ensure that the hash values are correct.
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.

  • If this configuration item is not set, the decimal points of the input parameters of the preceding functions are rounded off to integers in the PBE scenario.
  • If this configuration item is set, the decimal points of the input parameters of the preceding functions are truncated to integers in the PBE scenario to ensure that the result is the same as the result in A-compatible mode.
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.

  • If this configuration item is not set, the empty array constructed by the array_positions function is different from the empty array constructed by '{}'::int[].
  • If this configuration item is set, the empty array constructed by the array_positions function is the same as the empty array constructed by '{}'::int[].
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.)

  • If this configuration item is not set, the matching result is incorrect when multiple rows are matched and the regular expression rule contains |.
  • If this configuration item is set, the matching result is correct when multiple rows are matched and the regular expression rule contains |.
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.

Table 4 Configuration Item

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.

  • If this item is not specified, for a non-zero decimal number between –1 and +1, the 0 before the decimal point is not displayed. For example:
    1
    2
    3
    4
    5
    gaussdb=# select 0.1231243 as a, 0.1231243::numeric as b,0.1231243::integer(10,3) as c, length(0.1242343) as d;
        a     |    b     |  c   | d
    ----------+----------+------+---
     .1231243 | .1231243 | .123 | 8
    (1 row)
    
  • If this item is specified, for a non-zero decimal number between –1 and +1, the 0 before the decimal point is displayed. For example:
    1
    2
    3
    4
    5
    gaussdb=# select 0.1231243 as a, 0.1231243::numeric as b,0.1231243::integer(10,3) as c, length(0.1242343) as d;
         a     |     b     |   c   | d
    -----------+-----------+-------+---
     0.1231243 | 0.1231243 | 0.123 | 9
    (1 row)
    

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.

  • If this item is not specified, and the Day of param1 indicates the last day of a month shorter than result, the Day in the calculation result will equal that in param1. For example:
    1
    2
    3
    4
    5
    gaussdb=# select add_months('2018-02-28',3) from sys_dummy;
    add_months
    ---------------------
    2018-05-28 00:00:00
    (1 row)
    
  • If this item is specified, and the Day of param1 indicates the last day of a month shorter than result, the Day in the calculation result will equal that in result. For example:
    1
    2
    3
    4
    5
    gaussdb=# select add_months('2018-02-28',3) from sys_dummy;
    add_months
    ---------------------
    2018-05-31 00:00:00
    (1 row)
    

compat_analyze_sample

Specifies the sampling behavior of the ANALYZE operation.

If this item is specified, the sample collected by the ANALYZE operation will be limited to around 30,000 records, controlling 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:

  • If this item is not specified, the system reports an error and exits.
  • If this item is specified, the search continues based on the settings of search_path. If the issue persists, the system reports an error and exits.

correct_to_number

Specifies the compatibility of the to_number() result.

  • If this item is not specified, the result of the to_number() function is the same as that in the A database by default.
    1
    2
    3
    gaussdb=# select '' AS to_number_14, to_number('34,50','999,99');
    ERROR:  invalid data.
    CONTEXT:  referenced column: to_number
    
  • If this item is specified, the result of the to_number() function is the same as that of pg11.
    1
    2
    3
    4
    5
    gaussdb=# select '' AS to_number_14, to_number('34,50','999,99');
     to_number_14 | to_number
    --------------+-----------
                  |      3450
    (1 row)
    

unbind_divide_bound

Specifies the range check on the result of integer division.

This parameter does not take effect in M-compatible databases.

  • If this item is not specified, the range of the division result is verified. For example, an out-of-bounds error is reported because the output result of INT_MIN/(–1) is greater than INT_MAX.
    1
    2
    gaussdb=# select (-2147483648)::int4 / (-1)::int4;
    ERROR:  integer out of range
    
  • If this item is specified, the range of the division result does not need to be verified. For example, the output result of INT_MIN/(–1) is INT_MAX+1.
    1
    2
    3
    4
    5
    gaussdb=# select (-2147483648)::int4 / (-1)::int4;
      ?column?  
    ------------
     2147483648
    (1 row)
    

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.

  • If this item is not specified, the numeric constants of the character string type are not converted to those of the numeric type.
  • If this item is specified, the numeric constants of the character string type are converted to those of the numeric type.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
gaussdb=# create table test1 (c1 int, c2 varchar);
gaussdb=# insert into test1 values (2, '1.1');
gaussdb=# set behavior_compat_options='';
gaussdb=# select * from test1 where c2 > 1;
ERROR:  invalid input syntax for type bigint: "1.1"

gaussdb=# set behavior_compat_options='convert_string_digit_to_numeric';
gaussdb=# select * from test1 where c2 > 1;
 c1 | c2  
----+-----
  2 | 1.1 
(1 row)

gaussdb=# drop table test1;

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.

  • If this item is not specified, the empty string is displayed as NULL.
    1
    2
    3
    4
    5
    gaussdb=# select length(lpad('123',0,'*')) from sys_dummy;
    length
    --------
    
    (1 row)
    
  • If this item is specified, the empty string is displayed as single quotation marks ('').
    1
    2
    3
    4
    5
    gaussdb=# select length(lpad('123',0,'*')) from sys_dummy;
    length
    --------
    0
    (1 row)
    

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.

  • If this item is not specified and the concat function parameter is of the variadic type, the results of the A- and C-compatible databases are the same by default.
    1
    2
    3
    4
    5
    gaussdb=# select concat(variadic NULL::int[]) is null;
     ?column? 
    ----------
     t
    (1 row)
    
  • If this item is specified and the concat function parameter is of the variadic type, different result formats of the A- and C-compatible databases are retained.
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    -- In the A-compatible database:
    gaussdb=# select concat(variadic NULL::int[]) is null;
     ?column? 
    ----------
     t
    (1 row)
    -- In the C-compatible database:
    gaussdb=# select concat(variadic NULL::int[]) is null;
     ?column? 
    ----------
     f
    (1 row)
    

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.

  • If this item is specified and the preceding scenario exists, the system performs multiple UPDATE operations on the conflicting row.
  • If this item is not specified (the default value is retained), an error is reported, indicating that the MERGE or INSERT operation fails.

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.

  • If this item is specified, the trailing zeros after the decimal point are hidden in all scenarios where numeric values are output, even if the precision format is specified.
  • If this item is not specified, numeric data is displayed in the specified precision.

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.

  • If this item is specified, the trailing zeros after the decimal point are hidden in all numeric output scenarios except explicit precisions, such as to_char(numeric, format).
  • If this item is not specified, numeric data is displayed in the default precision.

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.

  • If this item is specified, spaces at the end are not omitted in conversion. In addition, if the length of the char(n) type exceeds that of other variable-length string types, an error is reported.
  • If this item is not specified, spaces at the end are omitted when the char(n) type is converted to another variable-length string type.
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:
  1. By default, the character '\n' cannot be matched.
  2. If flags contains the n option, '.' can match the character '\n'.
  3. The regexp_replace(source, pattern replacement) function replaces all matching substrings.
  4. The regexp_replace(source, pattern, replacement, flags) function returns null when the value of flags is '' or null.

Otherwise, the meanings of the options supported by the flags parameter of the regular expression are as follows:

  1. By default, the character '\n' can be matched.
  2. The n option in flags indicates that the multi-line matching mode is used.
  3. The regexp_replace(source, pattern replacement) function replaces only the first matched substring.
  4. If the value of flags is '' or null, the return value of the regexp_replace(source, pattern, replacement, flags) function is the character string after replacement.

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.

  • If this item is not specified and a function with default parameters is called, input parameters are added to the function from left to right. If inputs of non-default parameters are missing, an error is reported. For example:
    gaussdb=# create or replace function test(f1 int, f2 int default 20, f3 int, f4 int default 40, f5 int default 50) return int
    gaussdb-# as
    gaussdb$# begin
    gaussdb$# raise info 'f1:%',f1;
    gaussdb$# raise info 'f2:%',f2;
    gaussdb$# raise info 'f3:%',f3;
    gaussdb$# raise info 'f4:%',f4;
    gaussdb$# raise info 'f5:%',f5;
    gaussdb$# return 1;
    gaussdb$# end;
    gaussdb$# /
    CREATE FUNCTION
    gaussdb=# select test(1,2);
    ERROR:  function test(integer, integer) does not exist
    LINE 1: select test(1,2);
                   ^
    HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
    CONTEXT:  referenced column: test
    
    gaussdb=# drop function test;
  • If this item is specified and a function with default parameters is called, input parameters are added to the function from left to right. The number of defaulted inputs depends on the number of default parameters. If an input of a non-default parameter is missing, the previous default value is used to fill this parameter. For example:
    gaussdb=# create or replace function test(f1 int, f2 int default 20, f3 int, f4 int default 40, f5 int default 50) return int
    gaussdb-# as
    gaussdb$# begin
    gaussdb$# raise info 'f1:%',f1;
    gaussdb$# raise info 'f2:%',f2;
    gaussdb$# raise info 'f3:%',f3;
    gaussdb$# raise info 'f4:%',f4;
    gaussdb$# raise info 'f5:%',f5;
    gaussdb$# return 1;
    gaussdb$# end;
    gaussdb$# /
    CREATE FUNCTION
    gaussdb=# select test(1,2);
    INFO:  f1:1
    CONTEXT:  referenced column: test
    INFO:  f2:2
    CONTEXT:  referenced column: test
    INFO:  f3:20
    CONTEXT:  referenced column: test
    INFO:  f4:40
    CONTEXT:  referenced column: test
    INFO:  f5:50
    CONTEXT:  referenced column: test
     test 
    ------
        1
    (1 row)
    
    gaussdb=# drop function test;

    As shown above, f3 is filled with an incorrect default value.

    NOTE:

    In this scenario, a non-default parameter is filled with the previous default value.

dynamic_sql_compat

After this parameter is enabled:

  • Duplicate parameters in the SQL statement template are not regarded as the same parameter, but match variables in the USING clause in sequence.
  • If a stored procedure is called during dynamic statement execution, the IN and OUT attributes in a stored procedure and the USING clause are not checked.
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:
  • If the dynamic_sql_compat option is enabled, the dynamic_sql_check option does not take effect.
  • If a stored procedure is called when a dynamic statement executes an anonymous block statement, only the IN parameters are checked. If OUT parameters need to be checked, set the proc_outparam_override parameter.
  • If a stored procedure is called when a dynamic statement executes an anonymous block statement and the parameter is enabled, the IN and OUT attributes in a stored procedure and the USING clause are not checked.

enable_funcname_with_argsname

If the parameter is enabled, the projection alias displays the complete function when SELECT is used to call a function.

  • If this item is not specified, the projection alias displays only the function name when SELECT is used to call a function. For example:
    gaussdb=# select power(2,3);
     power 
    -------
         8
    (1 row)
    
    gaussdb=# select count(*) from db_ind_columns;
     count 
    -------
       611
    (1 row)
    
    gaussdb=# select count(index_name) from db_ind_columns;
     count 
    -------
       611
    (1 row)
    
    gaussdb=# select left('abcde', 2);
     left 
    ------
     ab
    (1 row)
    
    gaussdb=# select pg_client_encoding();
     pg_client_encoding 
    --------------------
     UTF8
    (1 row)
  • If this item is specified, the projection alias displays the complete function when SELECT is used to call a function. For example:
    gaussdb=# set behavior_compat_options = 'enable_funcname_with_argsname';
    SET
    gaussdb=# select power(2,3);
     power(2,3) 
    ------------
              8
    (1 row)
    
    gaussdb=# select count(*) from db_ind_columns;
     count(*) 
    ----------
          611
    (1 row)
    
    gaussdb=# select count(index_name) from db_ind_columns;
     count(index_name) 
    -------------------
                   611
    (1 row)
    
    gaussdb=# SELECT left('abcde', 2);
     left('abcde',2) 
    -----------------
     ab
    (1 row)
    
    gaussdb=# SELECT pg_client_encoding();
     pg_client_encoding() 
    ----------------------
     UTF8
    (1 row)
    NOTE:
    • Currently, only func_name(args_list), func_name(), and func_name(*) can be used to display complete functions and the arguments can only be character strings, numeric values, column names, and functions. A function name can contain a schema or package name. The parameter cannot contain other clauses (such as the ORDER BY clause) or be an expression. The parameter can contain only the DISTINCT keyword. If the parameter contains other keywords, complete functions cannot be displayed.
    • The displays of complete projection aliases are not supported for some special functions.
      • In non-M-compatible databases, the following functions are included: collation for, current_date, current_time, current_timestamp, dbtimezone, localtime, localtimestamp, sysdate, sessiontimezone, rownum, current_role, current_user, session_user, user, current_catalog, current_schema, cast, extract, timestampdiff, overlay, position, substring, treat, trim, nullif, nvl, nvl2, coalesce, greatest, least, lnnvl, regexp_like, and xml.
      • In M-compatible databases, the following functions are included: count, convert, get_format, date, time, timetsamp, date_add, date_sub, adddate, subdate, timestampadd, current_date, repeat, curdate, curtime, current_time, current_timestamp, utc_date, utc_time, utc_timestamp, localtime, localtimestamp, now, sysdate, interval, cast, extract, substr, mid, year, timestampdiff, substring, trim, coalesce, if, char, position, reverse, and default.
      • In M-compatible databases, if this parameter is specified and m_format_behavior_compat_options is set to 'select_column_name', 'select_column_name' takes effect preferentially.
    • If some secure encryption and decryption functions and masking functions are displayed completely by projection aliases, it may bring security problems. Therefore, only function names are displayed here, including: gs_encrypt_aes128, gs_decrypt_aes128, gs_encrypt, gs_decrypt, gs_encrypt_bytea, gs_decrypt_bytea, aes_encrypt, aes_decrypt, pg_create_physical_replication_slot_extern, dblink_connect, creditcardmasking, basicemailmasking, fullemailmasking, alldigitsmasking, shufflemasking, randommasking, regexpmasking, and gs_digest.
    • You cannot use => to specify parameters for the projection alias to display the complete function. The projection alias cannot contain double quotation marks (""), for example, select "power"(2,3).
    • To enable the projection alias to display the complete function, this function is not affected by parameters such as removing 0 at the end.

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:
  1. The behaviors of assigning values to elements of the array type and reading elements change.
  2. The behavior of calling related array functions changes.
  3. After this parameter is enabled, the maximum capacity of the varray type is verified.

For details about behavior differences and examples, see "Stored Procedure" > "Arrays, Collections, and Records" > Arrays" in Developer Guide.

NOTE:
  • Do not switch and use this parameter in the same session.
  • After a database is upgraded:
    • For array types that have been defined in stored procedures or packages, enabling the varray_compat parameter does not take effect. You need to rebuild the array type. That is, you need to rebuild the stored procedure or package by performing the CREATE OR REPLACE or DROP+CREATE operation. The parameter takes effect only after the array type is rebuilt.
    • If the content of the CREATE OR REPLACE PACKAGE/CREATE OR REPLACE PACKAGE BODY operation is the same as that of the original package or package body, the original array type will not be rebuilt. Enabling the varray_compat parameter does not take effect.

tableof_elem_constraints

After this parameter is enabled:

  1. For the collection type, the validity of elements will be verified.
  2. If the index is of the varchar collection type, the validity of the index length is also verified.

For details about behavior differences and examples, see "Stored Procedure" > "Arrays, Collections, and Records" > "Collections" in Developer Guide.

NOTE:
  • Do not switch and use this parameter in the same session.
  • After a database is upgraded:
    • For collection types that have been defined in stored procedures or packages, enabling the tableof_elem_constraints parameter does not take effect. You need to rebuild the collection type. That is, you need to rebuild the stored procedure or package by performing the CREATE OR REPLACE or DROP+CREATE operation. The parameter takes effect only after the collection type is rebuilt.
    • If the content of the CREATE OR REPLACE PACKAGE/CREATE OR REPLACE PACKAGE BODY operation is the same as that of the original package or package body, the original collection type will not be rebuilt. Enabling the tableof_elem_constraints parameter does not take effect.
    • If a collection type is created using the syntax: CREATE TYPE type_name IS TABLE OF data_type, you need to drop the type and rebuild it for the function to take effect.

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:
  • This parameter is valid only in an A-compatible database.

enable_bpcharlikebpchar_compare

Enables or disables the bpcharlikebpchar and bpcharnlikebpchar operators.

  • This parameter is enabled by default for a newly installed database.
  • After the database of a version earlier than 505.1.0 is upgraded, this parameter is disabled by default.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
gaussdb=# SELECT bpcharlikebpchar('455'::BPCHAR(10), '455  '::BPCHAR);
 bpcharlikebpchar 
------------------
 f
(1 row)
gaussdb=# SELECT bpcharlikebpchar('455 '::BPCHAR(10), '455 '::BPCHAR(10));
 bpcharlikebpchar 
------------------
 t
(1 row)
gaussdb=# SELECT bpcharlikebpchar('455 '::BPCHAR(10), '455'::BPCHAR(10));
 bpcharlikebpchar 
------------------
 t
(1 row)
gaussdb=# SELECT bpcharlikebpchar('455 '::BPCHAR(10), '455'::BPCHAR(11));
 bpcharlikebpchar 
------------------
 f
(1 row)
gaussdb=# CREATE TABLE op_test (
  col BPCHAR(2) DEFAULT NULL
 );
CREATE TABLE
gaussdb=# CREATE INDEX op_index ON op_test(col);
CREATE INDEX
gaussdb=# INSERT INTO op_test VALUES ('a');
INSERT 0 1
gaussdb=# INSERT INTO op_test VALUES ('1');
INSERT 0 1
gaussdb=# INSERT INTO op_test VALUES ('11');
INSERT 0 1
gaussdb=# INSERT INTO op_test VALUES ('12');
INSERT 0 1
gaussdb=# INSERT INTO op_test VALUES ('sd');
INSERT 0 1
gaussdb=# INSERT INTO op_test VALUES ('aa');
INSERT 0 1
gaussdb=# SHOW behavior_compat_options;
 behavior_compat_options 
-------------------------
(1 row)
-- If behavior_compat_options does not contain enable_bpcharlikebpchar_compare, the latest bpcharlikebpchar operator is not enabled and the result set returned by the matching between bpchars is not the same as expected (all data should be returned in normal cases).
gaussdb=# EXPLAIN (COSTS OFF) SELECT * FROM op_test WHERE col LIKE col::BPCHAR ORDER BY col;
              QUERY PLAN              
--------------------------------------
 Sort
   Sort Key: col
   ->  Seq Scan on op_test
         Filter: (col ~~ (col)::text)
(4 rows)
gaussdb=# SELECT * FROM op_test WHERE col LIKE col::BPCHAR ORDER BY col;
 col 
-----
 11
 12
 aa
 sd
(4 rows)
gaussdb=# SET behavior_compat_options = 'enable_bpcharlikebpchar_compare';
SET
gaussdb=# SHOW behavior_compat_options;
     behavior_compat_options     
---------------------------------
 enable_bpcharlikebpchar_compare
(1 row)
-- After this parameter is enabled, the latest bpcharlikebpchar operator is enabled, and the returned behavior meets the expected behavior during matching.
gaussdb=# EXPLAIN (COSTS OFF) SELECT * FROM op_test WHERE col LIKE col::BPCHAR ORDER BY col;
          QUERY PLAN          
------------------------------
 Sort
   Sort Key: col
   ->  Seq Scan on op_test
         Filter: (col ~~ col)
(4 rows)
gaussdb=# SELECT * FROM op_test WHERE col LIKE col::BPCHAR ORDER BY col;
 col 
-----
 1 
 11
 12
 a 
 aa
 sd
(6 rows)
gaussdb=# DROP TABLE op_test;
DROP TABLE
NOTE:
  • This parameter takes effect only when sql_compatibility is set to A.
  • If this parameter is enabled, the result set and execution plan for LIKE or NOT LIKE pattern matching between BPCHAR types are affected.

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.

  • This parameter is enabled by default for a newly installed database.
  • After the database of a version earlier than 505.1.0 is upgraded, this parameter is disabled by default.
  • Involved operators: =, <>, <, >, <=, and >=
  • After this parameter is enabled, the involved cross-type integers can be compared directly without implicit conversion. Here is an example that uses (int1 op int2):
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
gaussdb=# CREATE TABLE implicit_index(c1 int1);
CREATE TABLE
gaussdb=# CREATE INDEX idx1 ON implicit_index(c1);
CREATE INDEX
gaussdb=# SET behavior_compat_options='enable_crosstype_integer_operator';
SET
gaussdb=# EXPLAIN SELECT * FROM implicit_index WHERE c1 = 1::int2;
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 [Bypass]
 Index Only Scan using idx1 on implicit_index  (cost=0.00..4.48 rows=13 width=1)
   Index Cond: (c1 = 1::smallint)
(3 rows)

gaussdb=# SET behavior_compat_options='';
SET
gaussdb=# EXPLAIN SELECT * FROM implicit_index WHERE c1 = 1::int2;
                           QUERY PLAN                           
----------------------------------------------------------------
 Seq Scan on implicit_index  (cost=0.00..49.52 rows=13 width=1)
   Filter: ((c1)::bigint = 1::smallint)
(2 rows)

gaussdb=# DROP TABLE implicit_index;
DROP TABLE
NOTE:
  • This parameter takes effect only when sql_compatibility is set to A.
  • If this parameter is enabled, the execution plan of operators involved in the following scenarios will be affected:
    • (int1 op int2)
    • (int1 op int4)
    • (int1 op int8)
    • (int1 op int16)
    • (int1 op numeric)
    • (int2 op int1)
    • (int2 op int16)
    • (int2 op numeric)
    • (int4 op int1)
    • (int4 op int16)
    • (int4 op numeric)
    • (int8 op int1)
    • (int8 op int16)
    • (int8 op numeric)
    • (int16 op int1)
    • (int16 op int2)
    • (int16 op int4)
    • (int16 op int8)
    • (numeric op int1)
    • (numeric op int2)
    • (numeric op int4)
    • (numeric op int8)

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

  • After this parameter is enabled, the collection without an index does not support the input of character strings in the format of '{1,,3}'.
    gaussdb=# CREATE TYPE t1 IS TABLE OF int;
    CREATE TYPE
    gaussdb=# CREATE OR REPLACE PROCEDURE p1(v t1) IS
    gaussdb$# BEGIN
    gaussdb$#      FOR i IN 1..v.count LOOP
    gaussdb$#          dbe_output.print_line('v(' || i || ') is ' || v(i));
    gaussdb$#      END LOOP;
    gaussdb$# END;
    gaussdb$# /
    CREATE PROCEDURE
    gaussdb=# SET behavior_compat_options = 'forbid_skip_tableof_empty_str_elem';
    SET
    gaussdb=# CALL p1('{1,,3}');
    ERROR:  Malformed nesttable literal: "{1,,3}".
    LINE 1: call p1('{1,,3}');
                    ^
    gaussdb=# CALL p1('{1,}');
    ERROR:  Malformed nesttable literal: "{1, }".
    LINE 1: call p1('{1, }');
                    ^
    gaussdb=# CALL p1('{,3}');
    ERROR:  Malformed nesttable literal: "{ ,3}".
    LINE 1: call p1('{ ,3}');
                    ^
    gaussdb=# DROP PROCEDURE p1;
    DROP PROCEDURE
    gaussdb=# DROP TYPE t1;
    DROP TYPE
  • After this parameter is disabled, the collection without an index supports the input of character strings in the format of '{1,,3}'. Empty character strings are automatically filtered out during conversion.
    gaussdb=# CREATE TYPE t1 IS TABLE OF int;
    CREATE TYPE
    gaussdb=# CREATE OR REPLACE PROCEDURE p1(v t1) IS
    gaussdb$# BEGIN
    gaussdb$#      FOR i IN 1..v.count LOOP
    gaussdb$#          dbe_output.print_line('v(' || i || ') is ' || v(i));
    gaussdb$#      END LOOP;
    gaussdb$# END;
    gaussdb$# /
    CREATE PROCEDURE
    gaussdb=# SET behavior_compat_options = '';
    SET
    gaussdb=# CALL p1('{1,,3}');
    v(1) is 1
    v(2) is 3
     p1 
    ----
    
    (1 row)
    
    gaussdb=# CALL p1('{1,}');
    v(1) is 1
     p1 
    ----
    
    (1 row)
    
    gaussdb=# CALL p1('{,3}');
    v(1) is 3
     p1 
    ----
    
    (1 row)
    
    gaussdb=# DROP PROCEDURE p1;
    DROP PROCEDURE
    gaussdb=# DROP TYPE t1;
    DROP TYPE

sys_function_without_brackets

After this parameter is enabled, system functions without input parameters in A-compatible mode can be called without parentheses.

1
2
3
4
5
6
gaussdb=# SET behavior_compat_options='sys_function_without_brackets';
gaussdb=# SELECT systimestamp;
         systimestamp          
-------------------------------
 2024-04-16 15:55:21.614342+08
(1 row)

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.

Table 5 Compatibility configuration items

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 verification function supports only the following SQL statements: SELECT, INSERT, UPDATE, DELETE, and MERGE. DDL and DML statements are not supported.
  • Dynamic SQL statements are not verified.
  • False positives exist. As a result, alarms are generated when functions, stored procedures, and packages are created, invalid recompilation occurs, or the valid column of pg_object is false.

    Therefore, you are advised to use it in the test environment to debug functions, stored procedures, and packages and modify the definitions of functions, stored procedures, and packages to improve fault locating efficiency. It is not recommended in the production environment.

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.

Table 6 Compatibility configuration item

Configuration Item

Behavior

10c

A-compatible version.

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.

Table 7 Compatibility configuration items

Configuration Item

Behavior

s1

  • Compatible minor version of platform A, which affects functions TRUNC(date, fmt), ROUND(date, fmt), NVL2, LPAD, RPAD, ADD_MONTHS, MONTHS_BETWEEN, REGEXP_REPLACE, REGEXP_COUNT, TREAT, EMPTY_CLOB, INSTRB, trunc(number), greatest, least, mod, round(number), cast, to_date, to_timestamp, chr, rtrim, translate, to_char, to_number, and to_timestamp_tz.
  • Data type casting: A decimal character string is rounded off when it is converted to an integer (int1/int2/int4/int8/int16).
  • Data type conversion: Implicit conversion from timestamp with time zone to timestamp without time zone is supported.

s2

  • Compatible minor version of platform A, which affects functions such as dump, to_single_byte, to_multi_byte, nls_upper, nls_lower, initcap, ascii2, asciistr, unistr, vsize, cosh, remainder, sinh, tanh, nanvl, current_date, current_timestamp, dbtimezone, numtodsinterval, numtoyminterval, new_time, sessiontimezone, sys_extract_utc, tz_offset, to_binary_double, to_binary_float, to_dsinterval, to_yminterval, lnnvl, ora_hash, rawtohex2, bit2coding, and bit4coding.
  • Supports all behaviors when the compatibility configuration item is set to s1.

s3

  • Compatible minor version of platform A. If the parameter is enabled, nested calling of functions without parameters is supported.
  • Supports all behaviors when the compatibility configuration item is set to s2.

s4

  • Compatible minor version of platform A, which affects functions such as nchr(cvalue int|bigint), to_timestamp_tz, getclobval(xml), and getstringval(xml).
  • Supports all behaviors when the compatibility configuration item is set to s3.

s5

  • Compatible minor version of platform A. If the parameter is enabled, the composite type with the same name as a function is preferentially parsed. The sys_guid() function is affected.
  • Supports all behaviors when the compatibility configuration item is set to s4.

s6

  • The A platform is compatible with the iteration minor version. The parameter is enabled, and the function mod(text, int) is affected.
  • Supports all behaviors when the compatibility configuration item is set to s5.

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.

Table 8 Configuration Item

Configuration Item

Behavior

s1

  1. The following parameters are influenced:

    If this parameter is enabled, the datetime option in the disable_keyword_options parameter is removed, indicating that DATETIME is used as a non-reserved keyword. datetime can be used as a table name, column name, or alias, but cannot be used as a function name, stored procedure name, or parameter name of a function or stored procedure.

  2. The following functions are influenced:

    curdate, from_days, date_format, str_to_date, current_date, datediff, timestampdiff, date_add, subtime, month, time_to_sec, to_days, to_seconds, dayname, monthname, convert_tz, sec_to_time, addtime, adddate, date_sub, timediff, last_day, weekday, from_unixtime, unix_timestamp, subdate, day, year, weekofyear, dayofmonth, dayofyear, week, yearweek, dayofweek, time_format, hour, minute, second, microsecond, quarter, utc_date, get_format, extract, makedate, period_add, timestamppadd, period_diff, utc_time, utc_timestamp, sysdate, current_timestamp, maketime, curtime, current_time, localtime, localtimestamp, now, lc_time_names, default_week_format, json_object, and substring.

  3. The following types are influenced:
    1. Be compatible with the integer type tinyint with the value range changed to –128 to 127.
    2. Be compatible with the character string types char and varchar. The precision n of char(n) and varchar(n) is changed from the byte length to the character length.
    3. Be compatible with the text types tinytext, mediumtext, and longtext. These types are mapped to the text type after the parameter is enabled.
    4. Be compatible with binary types tinyblob, blob, mediumblob, and longblob. These types are mapped to the bytea type after the parameter is enabled.
    5. Be compatible with floating-point types double, float, and real. Among them, double is mapped to the float8 type, double(p, s) is mapped to the numeric type, float(p, s) is mapped to the numeric type, real is mapped to float8, and real(p, s) is mapped to the numeric type.
    6. Be compatible with high-precision types numeric, dec, and decimal. If the precision and scale are not specified, the default precision is changed to 10 and the scale is changed to 0.
    7. Be compatible with time types datetime, timestamp, time, and date. Replace datetime[(p)] with a timestamp without time zone[(p)]. Replace timestamp[(p)] with a timestamp with time zone[(p)]. The input, output, range, and precision specifications of these types are changed. For details, see "SQL Reference" > "Data Type" in Developer Guide.
    8. Be compatible with integer types tinyint, smallint, int, and bigint. The display width and ZEROFILL attributes of these types take effect after the parameter is enabled.
  4. The following syntax is influenced:

    The column constraint ON UPDATE update_expr can be specified.

  5. The following operators are influenced:
    1. An error is reported when the || operator between bytea types is used.
    2. The REGEXP operator is supported.

s2

The compatibility behavior controlled by s1 is included. In addition, the following impacts are included:

  1. The following parameters are influenced:
    1. The auto_increment_cache parameter takes effect only when this option is enabled.
    2. When this option is enabled, the multi_insert_min_rows parameter takes effect for auto-increment columns.
    3. When this option is enabled, standard_conforming_strings and escape_string_warning are set to off.
    4. The collation_connection and character_set_connection parameters take effect only when this option is enabled.
  2. The following functions are affected:
    1. When 0, NULL, and definite values are imported or batch inserted into the auto-increment column, the auto-increment count is updated immediately when the definite values are inserted, and then 0 or NULL increases automatically based on the definite values.
    2. The like operator does not report an error when an escape character is at the end of the matching string.
    3. The priorities of the sorting rules for the character sets and collations will change. For details, see "SQL Reference" > "Character Sets and Collations" > "Rules for Combining Character Sets and Collations" in Developer Guide.
    4. After the parameter is enabled, the collation of foreign keys cannot be inconsistent with that of columns.
  3. The following syntax is influenced:
    1. The CREATE TABLE table_name LIKE source_table syntax is supported.
    2. The syntaxes CREATE TABLE table_name LIKE source_table and CREATE TABLE table_name (LIKE source_table) cannot specify INCLUDING and EXCLUDING options. By default, INCLUDING ALL is specified.
    3. The LOAD DATA syntax is supported. Some syntax functions in gs_loader that are consistent with the LOAD DATA syntax will change.
    4. The collate clause can be specified by set names.
    5. The syntaxes for changing table names, such as ALTER TABLE and RENAME TABLE, have impact in the following scenario:

      If the character string corresponding to the new table name starts with "#MySQL50#" and is followed by other characters, "#MySQL50#" will be ignored.

      If the old and new table names are the same, no error is reported.

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.

m_format_dev_version takes effect only when sql_compatibility is set to M.

Parameter type: string.

Unit: none

Value range: an empty string "" or a compatibility configuration item in Table 9.

Table 9 Compatibility configuration items

Configuration Item

Behavior

s1

The following syntax is influenced:
  1. Disable the SELECT FETCH FIRST ROWS ONLY syntax.
  2. The TRUNCATE CASCADE/RESTRICT syntax is disabled.
  3. USING INDEX TABLESPACE tablespace_name syntax cannot be specified when a primary key is defined using CREATE TABLE.
  4. The semantics of CASCADE/RESTRICT syntax in DROP TABLE/VIEW/COLUMN is ignored, and the default behavior is RESTRICT.
  5. If the CREATE TABLE REFERENCES syntax is used as a column constraint, its semantics is changed to ignore the foreign key definition.

s2

The following operators are influenced:

  1. The conversion rule of the :: operator is changed to the default implicit conversion rule.

The following views are affected:

  1. In the information_schema.statistics view, the display of the INDEX_NAME column is changed from primary to PRIMARY in the PRIMARY KEY scenario. The COLUMN_NAME and SUB_PART columns support the prefix index scenario. The temporary table index is changed from displayed to not displayed. The index of the system column is not displayed in some scenarios.
  2. The data in the information_schema.global_variables view is reduced from all data in the pg_settings table to data whose context columns are internal, postmaster, backend, and sighup in the pg_settings table.
  3. The COLUMN_KEY column in the information_schema.columns view supports prefix indexes. Temporary tables created in the current session are not displayed.
  4. In the information_schema.table_constraints view, the CONSTRAINT_NAME column is displayed as PRIMARY in the PRIMARY KEY constraint scenario.
  5. In the information_schema.character_sets view, you can query information about all available character sets.
  6. In the information_schema.collations view, you can query information about all available collations.
  7. In the information_schema.collation_character_set_applicability view, you can query the mapping between all available collations and character sets.
  8. CHARACTER_SET_NAME in the information_schema.character_sets, information_schema.collations, and information_schema.collation_character_set_applicability views is changed to lowercase letters. The query result is displayed in utf8mb4 and utf8 based on the collation.

The following syntax is influenced:

  1. The syntax and semantics of the following transactions are changed to setting the next transaction feature:
    1. set @@transaction_isolation = value
    2. set @@transaction_read_only = value
    3. set transaction isolation level
    4. set transaction {read write | read only}
  2. The syntax and semantics of SET TRANSACTION are changed to session-level transaction features.
    1. set [local|session|@@session.] transaction_isolation = value
    2. set [local|session|@@session.] transaction_read_only = value
  3. show transaction_isolation is displayed as an isolation level with a hyphen.
  4. If the STORED and VIRTUAL keywords are not specified when a generated column is created, a virtual generated column is created by default.
  5. The result set returned by the SHOW CREATE TABLE syntax is changed, including:
    1. The position of the column or table comment is changed. The comment is returned in the COMMENT clause of the CREATE TABLE statement instead of the COMMENT ON statement.
    2. The positions of the primary key and unique constraint are changed. The ALTER TABLE ADD CONSTRAINT statement is used to return the primary key and unique constraint. The CREATE TABLE statement is used to return the primary key and unique constraint in the CONSTRAINT clause.
    3. The USING INDEX TABLESPACE option in the primary key and unique constraint is no longer displayed. This option is disabled after the compatible version control switch is set to s1.
    4. The position of the index comment is changed. The comment is returned in the COMMENT clause of the CREATE INDEX statement instead of the COMMENT ON statement.
    5. The position of the ON COMMIT option for global temporary tables is changed from the on_commit_delete_rows parameter in the WITH clause of the CREATE TABLE statement to the ON COMMIT clause in the CREATE TABLE statement.
  6. The SHOW CHARACTER SET syntax uses the information_schema.character_sets view for query. The query result varies with the content of the view.
  7. The SHOW COLLATION syntax uses the information_schema.collations view for query. The query result varies with the content of the view.
  8. In the UPDATE syntax:
    1. If an alias is specified for table_name, the table can be referenced only by the alias.
    2. Only when there are multiple columns in a single table, the expression of each column is calculated from left to right. If a column name is referenced, the updated data is used. The same column name can be modified multiple times.
  9. INSERT ... In the ON DUPLICATE KEY UPDATE syntax, when the ON DUPLICATE KEY UPDATE clause updates multiple columns, if a column name is referenced, the updated data is used. A column name can be modified multiple times.

The following GUC parameter is affected:

  1. The value range of the transaction_isolation parameter is added.

The following functions are affected:

  1. To delete the base column of a generated column, you need to delete the corresponding generated column first.
  2. The SET NULL, SET DEFAULT, and CASCADE keywords cannot be associated with the ON UPDATED and ON DELETE actions.
  3. If the ALTER TABLE statement is used to modify a table that involves storage generation columns, the CHECK constraint check on the generation columns takes effect.

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.

Table 10 Compatibility configuration items

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.

This parameter affects the following behaviors:
  • 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.
Table 11 Impact of foreign_key_checks on DML operations

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

Table 12 Keywords of the disable_keyword_options option that can be set for databases in different compatible modes

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.
Table 13 Compatibility configuration items

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.

Table 14 Compatibility configuration items

Configuration Item

Behavior

s1

  • Supports gs_loader specifying data type (CHAR[(length)]/INTEGER external[(length)]/FLOAT external[(length)]/DECIMAL external[(length)]/TIMESTAMP/DATE/DATE EXTERNAL/INTEGER/SMALLINT/RAW[(length)]) to import data.
  • Supports gs_loader converting expressions and extending scenarios for columns. If the original data does not meet the column constraints but the data that is converted using the transform expression meets the column constraints, the data fails to be imported when this parameter is disabled, and the data can be successfully imported to the database when this parameter is enabled. For example, if the maximum length of a column in a table is 10 and the original data length is 100, the substring of 10 characters is obtained by the transform expression.
  • When gs_loader imports data in CSV mode, spaces at the beginning of a column are skipped. If the first non-space character in a column is not an enclosed character, the enclosure setting is ignored. If no enclosed character is matched and the end of the line is matched first, an error is reported.

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.

  1. 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.
  2. 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.
  3. 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.
  4. 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.
Table 15 Functions and advanced package functions supporting special characters

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"

Table 16 gs_format_behavior compatibility configuration items

Configuration Item

Behavior

sqrt_karatsuba

  • If this parameter is set, the Karatsuba square root algorithm is used when the sqrt function is called. The Karatsuba algorithm has higher performance but its precision may be different from that of the Newton iteration algorithm in rare cases.
  • If this parameter is not set, the default Newton iteration algorithm is used to calculate the square root for the sqrt algorithm.

allow_textconcat_null

  • If this parameter is set, the value of the corresponding character string is returned when a character string is combined with the null value in PG-compatible mode.
    -- Run the following command in PG-compatible mode:
    gaussdb=# set gs_format_behavior_compat_options='allow_textconcat_null';
    SET
    gaussdb=# select 'a' || null || 'b';
     ?column?
    ----------
     ab
    (1 row)
    
  • If this parameter is not set, NULL is returned when a character string is combined with the null value in PG-compatible mode.

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.

  1. This parameter takes effect only when the A-compatible database is used and support_zero_character is set to on for all nodes.
  2. 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.
  3. 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.
  4. When the GUC parameter is enabled and different character sets are converted, the 0 character is directly returned and no error is reported.
  5. 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.
  6. 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.
  7. 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.)
  8. 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.
  9. 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.)
Table 17 List of system functions and advanced package functions supported by 0 characters

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

Table 18 Functions for reporting an error when the input parameter contains 0 characters

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.

Table 19 to_timestamp fmt conflicts

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.

Table 20 System function configuration items

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.