Statement Behavior
This section describes related default parameters involved in the execution of SQL statements.
search_path
Parameter description: Specifies the order in which schemas are searched when an object is referenced with no schema specified. The value of this parameter consists of one or more schema names. Different schema names are separated by commas (,).
This parameter is a USERSET parameter. Set it based on instructions provided in Table 2.
- If the schema of temporary tables exists in the current session, the schema can be listed in 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. 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. 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.
Value range: a string
NOTE:
- 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 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
current_schema
Parameter description: Specifies the current schema.
This parameter is a USERSET parameter. Set it based on instructions provided in Table 2.
Value range: a string
Default value: "$user",public
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.
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.
This parameter is a USERSET parameter. Set it based on instructions provided in Table 2.
Value range: a string. An empty string indicates that the default tablespace is used.
Default value: empty
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.
The value of this parameter can be a list of names of tablespaces. When there is more than one name in the list, openGauss 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 string, openGauss will automatically use the default tablespace of the current database instead.
This parameter is a USERSET parameter. Set it based on instructions provided in Table 2.
Value range: a string An empty string indicates that all temporary objects are created only in the default tablespace of the current database. For details, see default_tablespace.
Default value: empty
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 word 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 is a USERSET parameter. Set it based on instructions provided in Table 2.
Value range: Boolean
- on indicates that validation of the function body string is enabled during the execution of CREATE FUNCTION.
- off indicates that validation of the function body string is disabled during the execution of CREATE FUNCTION.
Default value: on
default_transaction_isolation
Parameter description: Specifies the default isolation level of each transaction.
This parameter is a USERSET parameter. Set it based on instructions provided in Table 2.
Value range: enumerated values
- read committed indicates that the data read by a transaction is committed at the moment it is read.
- repeatable read indicates that the data that has been read by the current transaction cannot be modified by other transactions until the current transaction completes, thereby preventing unrepeatable reads.
- serializable: Currently, this isolation level is not supported in openGauss. It is equivalent to repeatable read.
Default value: read committed
default_transaction_read_only
Parameter description: Specifies whether each new transaction is in read-only state.
This parameter is a USERSET parameter. Set it based on instructions provided in Table 2.
CAUTION: If this parameter is set to on, the DML and write transactions cannot be executed.
Value range: Boolean
- on indicates that the transaction is in read-only state.
- off indicates that the transaction is in read/write state.
Default value: off
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.
openGauss does not support the serializable isolation level. Therefore, the parameter takes no effect.
This parameter is a USERSET parameter. Set it based on instructions provided in Table 2.
Value range: Boolean
- on indicates that a transaction is delayed by default.
- off indicates that a transaction is not delayed by default.
Default value: off
session_replication_role
Parameter description: Specifies the behavior of replication-related triggers and rules for the current session.
This parameter is a SUSET parameter. Set it based on instructions provided in Table 2.
NOTICE: Setting this parameter will discard all the cached execution plans.
Value range: enumerated values
- origin indicates that the system copies operations such as insert, delete, and update from the current session.
- replica indicates that the system copies operations such as insert, delete, and update from other places to the current session.
- local indicates that the system will detect the role that has logged in to the database when using the function to copy operations and will perform related operations.
Default value: origin
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 is a USERSET parameter. Set it based on instructions provided in Table 2.
Value range: an integer ranging from 0 to 2147483647. The unit is ms.
Default value: 0
vacuum_freeze_min_age
Parameter description: Specifies whether VACUUM replaces the xmin column of a record with FrozenXID when scanning a table (in the same transaction).
This parameter is a USERSET parameter. Set it based on instructions provided in Table 2.
Value range: an integer ranging from 0 to 576460752303423487
Default value: 2000000000
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 is a USERSET parameter. Set it based on instructions provided in Table 2.
Value range: an integer ranging from 0 to 576460752303423487
Default value: 4000000000
bytea_output
Parameter description: Specifies the output format for values of the bytea type.
This parameter is a USERSET parameter. Set it based on instructions provided in Table 2.
Value range: enumerated values
- hex indicates that the binary data is converted to hexadecimal format.
- escape indicates that the traditional PostgreSQL format is used. It takes the approach of representing a binary string as a sequence of ASCII characters, while converting those bytes that cannot be represented as an ASCII character into special escape sequences.
Default value: hex
xmlbinary
Parameter description: Specifies how binary values are to be encoded in XML.
This parameter is a USERSET parameter. Set it based on instructions provided in Table 2.
Value range: enumerated values
- base64
- hex
Default value: base64
xmloption
Parameter description: Specifies whether DOCUMENT or CONTENT is implicit when converting between XML and string values.
This parameter is a USERSET parameter. Set it based on instructions provided in Table 2.
Value range: enumerated values
- document indicates an HTML document.
- content indicates a common string.
Default value: content
max_compile_functions
Parameter description: Specifies the maximum number of function compilation results stored in the server. Excessive functions and compilation results of stored procedures may occupy large memory space. Setting this parameter to a proper value can reduce the memory usage and improve system performance.
This parameter is a POSTMASTER parameter. Set it based on instructions provided in Table 2.
Value range: an integer ranging from 1 to 2147483647
Default value: 1000
gin_pending_list_limit
Parameter description: Specifies the maximum size of the GIN pending list which is used when fastupdate is enabled. If the list grows larger than this maximum size, it is cleaned up by moving the entries in it to the main GIN data structure in batches. This setting can be overridden for individual GIN indexes by changing index storage parameters.
This parameter is a USERSET parameter. Set it based on instructions provided in Table 2.
Value range: an integer. The minimum value is 64 and the maximum value is INT_MAX. The default unit is KB.
Default value: 4 MB