• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. Setting Configuration Parameters
  5. Resetting Parameters

Resetting Parameters

Context

DWS provides multiple methods to set the parameters of databases, users, or sessions.

  • Parameter names are case-insensitive.
  • A parameter value can be an integer, floating point value, 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 CN and DN parameters at a time, but cannot do the same to other parameters.

For details about the parameters, see GUC Parameter Usage.

Setting GUC Parameters

DWS provides the following GUC parameters. Table 1 lists their types and setting methods.

Table 1 GUC parameters

Type

Description

Setting Method

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

Method by a database administrator in Table 2

USERSET

Common user parameter. It can be set by any user at any time.

Method in Table 2

You can modify the GUC parameters in DWS using the methods listed in Table 2.
Table 2 Setting GUC parameters

No.

Method

Method

Set parameters at database, user, or session levels.

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

    The setting takes effect in the next session.

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

    The setting takes effect in the next session.

  • Set a session-level parameter.
    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.

    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.
      ALTER DATABASE postgres SET explain_perf_mode TO pretty;

      The setting takes effect in the next session.

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

      The setting takes effect in the next session.

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

  3. Check whether the parameter is correctly set.

    SHOW explain_perf_mode;
     explain_perf_mode
    --------------
     pretty
    (1 row)