Resetting Parameters

Background

openGauss provides multiple methods to set GUC parameters for databases, users, or sessions.

Parameter names are case-insensitive.
A parameter value can be an integer, floating point number, string, Boolean value, or enumerated value.
- The Boolean values can be on/off, true/false, yes/no, or 1/0, and are case-insensitive.
- The enumerated value range is specified in the enumvals column of the pg_settings system catalog.
For parameters using units, specify their units during the setting, or default units are used.
- The default units are specified in the unit column of pg_settings.
- The unit of memory can be KB, MB, or GB.
- The unit of time can be ms, s, min, h, or d.

Setting GUC Parameters

openGauss provides six types of GUC parameters. For details about parameter types and their setting methods, see Table 1.

Table 1 GUC parameters

Parameter Type	Description	Setting Method
INTERNAL	Fixed parameter. It is set during database creation and cannot be modified. Users can only view the parameter by running the SHOW command or in the pg_settings view.	None
POSTMASTER	Database server parameter. It can be set in the configuration file when the database is started.	Method 2 in Table 2
SIGHUP	Global database parameter. It can be set when the database is started or be modified later.	Method 2 in Table 2
BACKEND	Session connection parameter. It is specified during session connection creation and cannot be modified after that. The parameter setting becomes invalid when the session is disconnected. This is an internal parameter and not recommended for users to set it.	Method 2 in Table 2 Note: After this parameter is set on the The parameter setting takes effect when the next session is created.
SUSET	Database administrator parameter. It can be set by common users when or after the database is started. It can also be set by database administrators using SQL statements.	Method 3 by a database administrator in Table 2
USERSET	Common user parameter. It can be configured by any user at any time.	Method 1 in Table 2

openGauss provides four methods to modify GUC parameters. For details, see Table 2.

Table 2 Methods for setting GUC parameters

No.	Setting Method
Method 1	Set parameters at database, user, or session levels. Set a database-level parameter. openGauss=# ALTER DATABASE dbname SET paraname TO value; The setting takes effect in the next session. Set a user-level parameter. openGauss=# ALTER USER username SET paraname TO value; The setting takes effect in the next session. Set a session-level parameter. openGauss=# SET paraname TO value; Parameter value in the current session is changed. After you exit the session, the setting becomes invalid. Note: Session-level parameters set by SET have the highest priority, followed by parameters set by ALTER. Parameter values set by ALTER DATABASE have a higher priority than those set using ALTER USER. Priorities of the first three methods are all higher than those of gs_guc.
Method 2	Use ALTER SYSTEM SET to modify database parameters. Set a POSTMASERT-level parameter. openGauss=# ALTER SYSTEM SET paraname TO value; The setting takes effect after the system is restarted. Set a SIGHUP-level parameter. openGauss=# ALTER SYSTEM SET paraname TO value; The setting takes effect immediately. (Actually, there is a slight delay to wait for the thread reloading the parameter.) Set a BACKEND-level parameter. openGauss=# ALTER SYSTEM SET paraname TO value; The setting takes effect in the next session.

No.

Setting Method

Method 1

Set parameters at database, user, or session levels.

Set a database-level parameter.
```
openGauss=# ALTER DATABASE dbname SET paraname TO value;
```
The setting takes effect in the next session.
Set a user-level parameter.
```
openGauss=# ALTER USER username SET paraname TO value;
```
The setting takes effect in the next session.
Set a session-level parameter.
```
openGauss=# SET paraname TO value;
```
Parameter value in the current session is changed. After you exit the session, the setting becomes invalid.
Note:
Session-level parameters set by SET have the highest priority, followed by parameters set by ALTER. Parameter values set by ALTER DATABASE have a higher priority than those set using ALTER USER. Priorities of the first three methods are all higher than those of gs_guc.

Method 2

Use ALTER SYSTEM SET to modify database parameters.

Set a POSTMASERT-level parameter.
```
openGauss=# ALTER SYSTEM SET paraname TO value;
```
The setting takes effect after the system is restarted.
Set a SIGHUP-level parameter.
```
openGauss=# ALTER SYSTEM SET paraname TO value;
```
The setting takes effect immediately. (Actually, there is a slight delay to wait for the thread reloading the parameter.)
Set a BACKEND-level parameter.
```
openGauss=# ALTER SYSTEM SET paraname TO value;
```
The setting takes effect in the next session.

Procedure

The following example shows how to set explain_perf_mode using method 1.

Run the following command to connect to the database:

gsql -d postgres -p 8000

postgres is the name of the database to be connected, and 8000 is the port number of the database primary node.

If information similar to the following is displayed, the connection is successful:

gsql((openGauss x.x.x build f521c606) compiled at 2021-09-16 14:55:22 commit 2935 last mr 6385 release)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

openGauss=#

Check the explain_perf_mode parameter.

openGauss=# SHOW explain_perf_mode;
 explain_perf_mode 
-------------------
 normal
(1 row)

Set the explain_perf_mode parameter.
Perform one of the following operations:
- Set a database-level parameter.
```
openGauss=# ALTER DATABASE postgres SET explain_perf_mode TO pretty;
```
  If the following information is displayed, the setting succeeds:
```
ALTER DATABASE
```
  The setting takes effect in the next session.
- Set a user-level parameter.
```
openGauss=# ALTER USER omm SET explain_perf_mode TO pretty;
```
  If the following information is displayed, the setting succeeds:
```
ALTER ROLE
```
  The setting takes effect in the next session.
- Set a session-level parameter.
```
openGauss=# SET explain_perf_mode TO pretty;
```
  If the following information is displayed, the setting succeeds:
```
SET
```

Check whether the parameter is correctly configured.

openGauss=# SHOW explain_perf_mode;
 explain_perf_mode
--------------
 pretty
(1 row)

Examples

--Create a table named test1. openGauss=# CREATE TABLE test1 ( a1 smallint not null, a2 int not null, a3 bigint not null, a4 float not null, a5 double not null, a6 numeric not null, a7 varchar(5) not null );

--Failed to insert records into the table. openGauss=# insert into test1(a1,a2) values(123412342342314,3453453453434324); --Failed to query the table. openGauss=# select a1,a2 from test1 group by a1;

--A record is successfully inserted into the table. openGauss=# set dolphin.sql_mode = ''; openGauss=# insert into test1(a1,a2) values(123412342342314,3453453453434324); --The table is queried successfully. openGauss=# select a1,a2 from test1 group by a1;

--Drop a table. openGauss=# DROP TABLE test1;