Resetting Parameters

Background

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

For details, see dolphin GUC Parameters。

Setting GUC Parameters

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

Table 1 GUC parameters

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

Table 2 Methods for setting GUC parameters

Procedure

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

1. Log in as the OS user omm to the primary node of the database.

2. 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((MogDB 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.
   
   MogDB=# 

3. Check the explain_perf_mode parameter.

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

4. Set the explain_perf_mode parameter.

   Perform one of the following operations:

   • Set a database-level parameter.

     MogDB=# 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.

     MogDB=# 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.

     MogDB=# SET explain_perf_mode TO pretty;

     If the following information is displayed, the setting succeeds:

     SET

5. Check whether the parameter is correctly configured.

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

Examples

-- Create a table named test1.
MogDB=# 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.
MogDB=# insert into test1(a1,a2) values(123412342342314,3453453453434324); 

-- Failed to query the table.
MogDB=# select a1,a2 from test1 group by a1;

-- A record is successfully inserted into the table.
MogDB=# set dolphin.sql_mode = ''; 
MogDB=# insert into test1(a1,a2) values(123412342342314,3453453453434324); 

-- The table is queried successfully.
MogDB=# select a1,a2 from test1 group by a1;

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