Updated on 2022-07-29 GMT+08:00

Configuring GUC Parameters

Context

You can configure the GUC parameters of databases, users, or sessions in GaussDB(DWS).

  • 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.
  • You can set a parameter for CNs and DNs at the same time, but cannot do the same to other types of parameters.

Setting GUC Parameters

You can configure GUC parameters in the following ways:

  • Method 1: After a cluster is created, you can log in to the GaussDB(DWS) management console and modify the database parameters of the cluster. For details, see "Modifying Database Parameters" in Data Warehouse Service (DWS) User Guide.
  • Method 2: Connect to a cluster and run SQL commands to configure the parameters of the SUSET or USERSET type.
    • SUSET parameters are database administrator parameters. They can be set by common users when or after the database is started. They can also be set by database administrators using SQL commands.
    • USERSET parameters are common user parameters. They can be configured by any user at any time.

    Configure parameters at database, user, or session levels.

    • Set a database-level parameter.
      1
      ALTER DATABASE dbname SET paraname TO value;
      

      The setting takes effect in the next session.

    • Set a user-level parameter.
      1
      ALTER USER username SET paraname TO value;
      

      The setting takes effect in the next session.

    • Set a session-level parameter.
      1
      SET paraname TO value;
      

      Parameter value in the current session is changed. After you exit the session, the setting becomes invalid.

Procedure

The following example shows how to set explain_perf_mode.

  1. View the value of explain_perf_mode.

    1
    2
    3
    4
    5
    SHOW explain_perf_mode;
     explain_perf_mode 
    -------------------
     normal
    (1 row)
    

  2. Set explain_perf_mode.

    Perform one of the following operations:

    • Set a database-level parameter.
      1
      ALTER DATABASE gaussdb SET explain_perf_mode TO pretty;
      

      If the following information is displayed, the setting has been modified.

      ALTER DATABASE

      The setting takes effect in the next session.

    • Set a user-level parameter.
      1
      ALTER USER dbadmin SET explain_perf_mode TO pretty;
      

      If the following information is displayed, the setting has been modified.

      ALTER USER

      The setting takes effect in the next session.

    • Set a session-level parameter.
      1
      SET explain_perf_mode TO pretty;
      

      If the following information is displayed, the setting has been modified.

      SET

  3. Check whether the parameter is correctly set.

    1
    2
    3
    4
    5
    SHOW explain_perf_mode;
     explain_perf_mode
    --------------
     pretty
    (1 row)