Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
SoftWare Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive

Statement Behavior

Updated on 2024-06-03 GMT+08:00

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 is a USERSET parameter. Set it based on instructions provided in Table 1.

  • If the schema of temporary tables exists in the current session, the schemas 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

NOTE:

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

current_schema

Parameter description: Specifies the current schema.

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

Value range: a string

Default value: "$user",public

NOTE:

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

If you need to obtain a schema during kernel development, use the value of search_path because the schema is determined by search_path. For compatibility, current_schema is used only to modify the value of search_path.

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 is a USERSET parameter. Set it based on instructions provided in Table 1.

Value range: a string. An empty string indicates that the default tablespace is used.

Default value: empty

default_storage_nodegroup

Parameter description: Specifies the node group where a table is created by default. This parameter takes effect only for ordinary tables.

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

  • installation indicates that tables will be created in the node group created during database installation.
  • A value other than installation indicates that tables will be created in the node group specified by this parameter.

Value range: a string

Default value: installation

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, 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 string, GaussDB will automatically use the default tablespace of the current database instead.

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

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 is a USERSET parameter. Set it based on instructions provided in Table 1.

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 is a USERSET parameter. Set it based on instructions provided in Table 1.

NOTE:

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 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 transaction can be repeatedly read.
  • serializable: Currently, this isolation level is not supported in GaussDB. 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.

CAUTION:

If this parameter is set to on, the DML and write transactions cannot be executed.

Parameter type: Boolean.

Unit: none

Value range:

  • on indicates that the transaction is in read-only state.
  • off indicates that the transaction is not in read-only state.

Default value: off

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

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 is a USERSET parameter. Set it based on instructions provided in Table 1.

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 is a SUSET parameter. Set it based on instructions provided in Table 1.

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 is a USERSET parameter. Set it based on instructions provided in Table 1. The default value is 0, indicating that the parameter does not take effect.

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

Parameter type: integer.

Unit: none

Value range: 0 to 576460752303423487

NOTE:

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

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

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.

Parameter type: integer.

Unit: none

Value range: 0 to 576460752303423487

NOTE:

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

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

bytea_output

Parameter description: Specifies the output format for values of the bytea type.

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

Value range: enumerated values

  • hex indicates that the binary data is converted to hexadecimal format.
  • 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

xmlbinary

Parameter description: Specifies how binary values are to be encoded in XML.

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

NOTE:

Currently, this parameter does not support data of the XML type.

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 is a USERSET parameter. Set it based on instructions provided in Table 1.

NOTE:

Currently, this parameter does not support data of the XML type.

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.

Parameter type: integer.

Unit: none

Value range: 1 to 2147483647.

Default value: 1000

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

Setting suggestion: 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.

We use cookies to improve our site and your experience. By continuing to browse our site you accept our cookie policy. Find out more

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback