Configuring GUC Parameters

Parameter Types and Values

• The GUC parameters of DWS are classified into the following types:
  • SUSET: database administrator parameters. This type of parameters takes effect immediately after they are set. You do not need to restart the cluster. If a parameter of this type is set in the current session, the parameter takes effect only in the current session.
  • USERSET: common user parameters. This type of parameters takes effect immediately after they are set. You do not need to restart the cluster. If a parameter of this type is set in the current session, the parameter takes effect only in the current session.
  • POSTMASTER: database server parameters. This type of parameters takes effect only after the cluster is restarted. After you modify a parameter of this type, the system displays a message indicating that the cluster is to be restarted. You are advised to manually restart the cluster during off-peak hours for the setting to take effect.
  • SIGHUP: global database parameters. This type of parameters takes effect globally and cannot take effect for single sessions.
  • BACKEND: global database parameters. This type of parameters takes effect globally and cannot take effect for single sessions.
• All 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 system catalog pg_settings.
• 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

You can configure GUC parameters in the following ways:

• Method 1: After a cluster is created, log in to the DWS console and modify the database parameters of the cluster. For details, see Modifying GUC Parameters of the DWS Cluster.
• Method 2: Connect to a cluster and run SQL commands to configure the parameters of the SUSET or USERSET type.

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