Statement Behavior

search_path

Parameter description: Specifies the order in which schemas are searched when an object is referenced with no schema specified. This parameter can be set at the PDB level.

Parameter type: string.

Unit: none

Value range: a character string consisting of one or more schema names. Different schema names are separated by commas (,).

• When this parameter is set to '"$user",public', shared use of a database (where no users have private schemas, and all share use of public), private per-user schemas, and combinations of them are supported. Other effects can be obtained by modifying the default search path setting, either globally or per-user.
• When this parameter is set to a null character string (''), the system automatically converts it into a pair of double quotation marks ("").
• If the content contains double quotation marks, the system considers them as insecure characters and converts each double quotation mark into a pair of double quotation marks.

Default value: '"$user",public'. In the PDB scenario, if this parameter is not set, the global setting is inherited.

$user indicates the name of the schema with the same name as the current session user. If the schema does not exist, $user will be ignored.

Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.

Setting suggestion:

• If schemas of temporary tables exist in the current session, the schemas can be listed in the search path by using the alias pg_temp, for example, 'pg_temp,public'. The schema of temporary tables has the highest search priority and is always searched before all the other schemas specified in pg_catalog and search_path. Therefore, do not explicitly specify pg_temp to be searched after other schemas in search_path. If pg_temp is specified in search_path, this setting will not take effect and an error message will be displayed. If the alias pg_temp is used, the temporary schema will be searched only for tables, views, and data types, and not for functions or operators.
• The system catalog schema, pg_catalog, has the second highest search priority and is the first to be searched among all the schemas, excluding pg_temp, specified in search_path. Therefore, do not explicitly specify pg_catalog to be searched after other schemas in search_path. If pg_catalog is specified in search_path, this setting will not take effect and an error message will be displayed.
• When an object is created without a specific target schema, the object will be placed in the first valid schema listed in search_path. An error is reported if the search path is empty.
• The current effective value of the search path can be examined through the SQL function current_schema(). This is different from examining the value of search_path, because the current_schema() function displays the first valid schema name in search_path.

Risks and impacts of improper settings: Change the parameter value after you fully understand the parameter meaning and test the parameter.

current_schema

Parameter description: Specifies the current schema. This parameter can be set at the PDB level.

Parameter type: string.

Unit: none

Value range: a character string consisting of one or more schema names. Different schema names are separated by commas (,).

Default value: '"$user",public'. In the PDB scenario, if this parameter is not set, the global setting is inherited.

$user indicates the name of the schema with the same name as the current session user. If the schema does not exist, $user will be ignored.

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.

default_tablespace

Parameter description: Specifies the default tablespace of the created objects (tables and indexes) when a CREATE command does not explicitly specify a tablespace. This parameter can be set at the PDB level.

• The value of this parameter is either the name of a tablespace, or an empty string that indicates the use of the default tablespace of the current database. If a non-default tablespace is specified, users must have CREATE privilege for it. Otherwise, creation attempts will fail.
• This parameter is not used for temporary tables. For them, the temp_tablespaces is used instead.
• This parameter is not used when users create databases. By default, a new database inherits its tablespace setting from the template database.

Parameter type: string.

Unit: none

Value range: Specifies the name of an existing tablespace or an empty string. An empty string indicates that the default tablespace is used.

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.

temp_tablespaces

Parameter description: Specifies one or more tablespaces to which temporary objects (temporary tables and their indexes) will be created when a CREATE command does not explicitly specify a tablespace. Temporary files for sorting large data sets are created in these tablespaces. This parameter can be set at the PDB level.

The value of this parameter can be a list of names of tablespaces. When there is more than one name in the list, GaussDB chooses a random tablespace from the list upon the creation of a temporary object each time. However, within a transaction, successively created temporary objects are placed in successive tablespaces in the list. If the element selected from the list is an empty character string, GaussDB will automatically use the default tablespace of the current database instead.

Parameter type: string.

Unit: none

Value range: name of an existing tablespace or an empty string. An empty character string indicates that all temporary objects are created only in the default tablespace of the current database. For details, see default_tablespace.

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.

check_function_bodies

Parameter description: Specifies whether to enable validation of the function body string during the execution of CREATE FUNCTION. Verification is occasionally disabled to avoid problems, such as forward references when you restore function definitions from a dump. After the function is enabled, the syntax of the PL/SQL in the stored procedure is verified, including the data type, statement, and expression. The SQL statements in the stored procedure are not checked in the CREATE phase. Instead, they are checked during running. This parameter can be set at the PDB level.

Parameter type: Boolean.

Unit: none

Value range:

• on: Validation of the function body string is enabled during the execution of CREATE FUNCTION.
• off: Validation of the function body string is disabled during the execution of CREATE FUNCTION.

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.

default_transaction_isolation

Parameter description: Specifies the default isolation level of each transaction. This parameter can be set at the PDB level.

The current version does not support the setting of the default transaction isolation level. The default value is read committed. Do not change the value.

Value type: enumerated type.

Unit: none

Value range:

• read committed: The transaction is read and committed.
• read uncommitted: The transaction is read but not committed.
• repeatable read: The transaction can be read repeatedly.
• serializable: Currently, this isolation level is not supported in GaussDB. It is equivalent to repeatable read.

Default value: read committed. 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.

default_transaction_read_only

Parameter description: Specifies whether each new transaction is in read-only state.

Parameter type: Boolean.

Unit: none

Value range:

• on: The new transaction is in read-only state.
• off: The new transaction is not in read-only state.

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, DML operations and transactions cannot be written.

default_transaction_deferrable

Parameter description: Specifies the default deferrable status of each new transaction. It currently has no effect on read-only transactions or those running at isolation levels lower than serializable.

GaussDB does not support the serializable isolation level. Therefore, the parameter takes no effect. This parameter can be set at the PDB level.

Parameter type: Boolean.

Unit: none

Value range:

• on: delayed by default.
• off: no delay by default.

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.

session_replication_role

Parameter description: Specifies the behavior of replication-related triggers and rules for the current session. This parameter can be set at the PDB level.

Modifying this parameter will discard all the cached execution plans.

Value type: enumerated type.

Unit: none

Value range:

• origin: The system copies operations such as insert, delete, and update from the current session.
• replica: The system copies operations such as insert, delete, and update from other places to the current session.
• local: The system detects the role that has logged in to the database when using the function to copy operations and performs related operations.

Default value: origin. 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.

statement_timeout

Parameter description: If the statement execution time (starting from the time the server receives the command) is longer than the duration specified by the parameter, error information is displayed and the statement exits. This parameter can be set at the PDB level.

Parameter type: integer.

Unit: millisecond

Value range: 0 to 2147483647

Default value: 0, indicating that the timeout error reporting function is disabled. 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. For example, if the value is 2000 without a unit, statement_timeout indicates 2000 ms. If the value is 2s, statement_timeout indicates 2s. The unit must be ms, s, min, h, or d if required.

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.

vacuum_freeze_min_age

Parameter description: Specifies whether VACUUM replaces the xmin column (minimum time) of a record with FrozenXID when scanning a table (in the same transaction). This parameter can be set at the PDB level.

Parameter type: integer.

Unit: none

Value range: 0 to 2000000000

Although you can set this parameter to any value, VACUUM will limit the effective value to 50% of autovacuum_freeze_max_age by default.

Default value: 2000000000. 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 large value, the freeze time is delayed and the recycling efficiency is reduced.

vacuum_freeze_table_age

Parameter description: Specifies when VACUUM scans the whole table and freezes old tuples. VACUUM performs a full table scan if the difference between the current transaction ID and the value of pg_class.relfrozenxid64 is greater than the specified time. This parameter can be set at the PDB level.

Parameter type: integer.

Unit: none

Value range: 0 to 2000000000

Although you can set this parameter to any value, VACUUM will limit the effective value to 95% of autovacuum_freeze_max_age by default. Therefore, a periodic manual VACUUM has a chance to run before an anti-wraparound autovacuum is launched for the table.

Default value: 4000000000. 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 large value, tablespace expansion may occur.

bytea_output

Parameter description: Specifies the output format for values of the bytea type. This parameter can be set at the PDB level.

Value type: enumerated type.

Unit: none

Value range:

• hex indicates the binary data is converted to the two hexadecimal digits per byte.
• escape uses ASCII character sequences to represent binary strings, and converts those binary strings that cannot be represented as ASCII characters into special escape sequences.

Default value: hex. 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.

xmlbinary

Parameter description: Specifies the encoding format of binary values in XML files. This parameter can be set at the PDB level.

Value type: enumerated type.

Unit: none

Value range:

• base64: The Base64 encoding format is used.
• hex: The hex encoding format is used.

Default value: base64. 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.

xmloption

Parameter description: Specifies whether XML data is treated as a document or a normal string when it is implicitly converted or serialized. This parameter can be set at the PDB level.

Value type: enumerated type.

Unit: none

Value range:

• document indicates an HTML document.
• content indicates a common string.

Default value: content. 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.

max_compile_functions

Parameter description: Specifies the maximum number of compilation results of stored procedures and functions that can be stored on a node, including system stored procedures and user-defined stored procedures and functions. If the compilation result exceeds the value of this parameter, the system automatically triggers memory eviction and deletes unused common stored procedures and functions.

Parameter type: integer.

Unit: none

Value range: 1 to 2147483647

Default value: 1000 (196-core CPU/1536 GB memory, 128-core CPU/1024 GB memory, 104-core CPU/1024 GB memory, 96-core CPU/1024 GB memory, 96-core CPU/768 GB memory, 80-core CPU/640 GB memory, 64-core CPU/512 GB memory, 60-core CPU/480 GB memory, 32-core CPU/256 GB memory, 16-core CPU/128 GB memory, 8-core CPU/64 GB memory, 4-core CPU/32 GB memory); 10 (4-core CPU/16 GB memory)

Setting method: This is a POSTMASTER parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value to reduce memory usage and improve system performance.

Risks and impacts of improper settings: If the number of compilation results exceeds the value of this parameter and the system cannot find the stored procedures and functions that can be deleted, a large amount of memory is occupied, causing performance deterioration.

gin_pending_list_limit

Parameter description: Specifies the maximum size of the UGIN pending list which is used when fastupdate is enabled. If the pending list grows larger than this maximum size, it is cleaned up by moving the entries in it to the main UGIN data structure in batches. This setting can be overridden for an individual UGIN by changing index storage parameters. This parameter can be set at the PDB level.

Parameter type: integer.

Unit: KB

Value range: 64 to 2147483647

Default value: 4096, that is, 4 MB. 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. For example, if the value is 1024 without a unit, gin_pending_list_limit indicates 1024 KB. If the value is 1MB, gin_pending_list_limit indicates 1 MB. The unit must be KB, MB, or GB if required.

Setting suggestion: Set this parameter to a proper value based on the update frequency to improve the performance of synchronizing UGINs.

Risks and impacts of improper settings: If this parameter is set to a value greater than work_mem, the performance of synchronizing incremental data and scanning data may deteriorate.