ALTER SESSION

Description

Defines or modifies the conditions or parameters that affect the current session. Modified session parameters are kept until the current session is disconnected.

Precautions

If the START TRANSACTION statement is not executed before the SET TRANSACTION statement, the transaction is ended instantly and the statement does not take effect.
You can use the transaction_mode(s) method declared in the START TRANSACTION statement to avoid using the SET TRANSACTION statement. For details, see START TRANSACTION.

Syntax

Set transaction parameters of a session.

ALTER SESSION SET [ SESSION CHARACTERISTICS AS ] TRANSACTION
    { ISOLATION LEVEL { READ COMMITTED | READ UNCOMMITTED } } | { READ ONLY  | READ WRITE } } [, ...] ;

Click to enlarge

Set other GUC parameters of a session.

ALTER SESSION SET 
    {{config_parameter { { TO  | =  }  { value | DEFAULT } | FROM CURRENT }} 
      | CURRENT_SCHEMA { TO | = } { schema | DEFAULT  }
      | TIME ZONE time_zone
      | SCHEMA 'schema'      
| NAMES encoding_name
      | ROLE role_name PASSWORD 'password'
      | SESSION AUTHORIZATION { role_name PASSWORD 'password' | DEFAULT }
      | XML OPTION { DOCUMENT | CONTENT }
    } ;

Click to enlarge

Parameters

config_parameter
Specifies the name of a configurable GUC parameter. You can use SHOW ALL to view available GUC parameters.
- value
  Specifies the new value of config_parameter. This parameter can be specified as string constants, identifiers, numbers, or comma-separated lists of these. DEFAULT is used to set default values for parameters.
  - DEFAULT
  - OFF
  - RESET
  - User-specified value: The value must meet the restriction of the modified parameter.
- FROM CURRENT
  Uses the value of configuration_parameter of the current session.

TIME ZONE timezone
Specifies the local time zone for the current session.

Value range: a valid local time zone. The corresponding GUC parameter is TimeZone. The default value is PRC.
CURRENT_SCHEMA
schema

Specifies the current schema.

Value range: an existing schema name. If the schema name does not exist, the value of CURRENT_SCHEMA will be empty.
SCHEMA schema
Specifies the current schema. Here the schema is a string.
NAMES encoding_name
Specifies the client character encoding. This statement is equivalent to set client_encoding to encoding_name.

Value range: a valid character encoding name. The GUC parameter corresponding to this option is client_encoding. The default encoding is UTF8.
role_name
Value range: a string. It must comply with the naming convention.
password
Specifies the password of a role. It must comply with the password convention.
SESSION AUTHORIZATION
Sets the session user identifier of the current session.
XML OPTION { DOCUMENT | CONTENT }
Specifies the XML parsing mode.

Value range: CONTENT (default) and DOCUMENT
ISOLATION LEVEL
Specifies the transaction isolation level that determines the data that a transaction can view if other concurrent transactions exist.

Value range:
- READ COMMITTED: Only committed data can be read. This is the default value.
- READ UNCOMMITTED: After this isolation level is set, its behavior is the same as that of READ COMMITTED.

Examples

Set transaction parameters of a session.

The keyword ALTER SESSION can be omitted in the example.

-- Start a transaction and set the transaction level.
gaussdb=# START TRANSACTION;
gaussdb=# ALTER SESSION SET TRANSACTION READ ONLY;
gaussdb=# END;

Set other GUC parameters of a session.

The keyword ALTER SESSION can be omitted in the example.

      
           -- Create the ds schema.
gaussdb=# CREATE SCHEMA ds;

-- Set the search path of a schema.
gaussdb=# SET SEARCH_PATH TO ds, public;

-- Set the time/date type to the traditional postgres format (date before month).
gaussdb=# SET DATESTYLE TO postgres, dmy;

-- Set the character code of the current session to UTF8.
gaussdb=# ALTER SESSION SET NAMES 'UTF8';

-- Set the time zone to Berkeley of California.
gaussdb=# SET TIME ZONE 'PST8PDT';

-- Set the time zone to Italy.
gaussdb=# SET TIME ZONE 'Europe/Rome';

-- Set the current schema.
gaussdb=# ALTER SESSION SET CURRENT_SCHEMA TO tpcds;

-- Set XML OPTION to DOCUMENT.
gaussdb=# ALTER SESSION SET XML OPTION DOCUMENT;

-- Create the role joe, and set the session role to joe.
gaussdb=# CREATE ROLE joe WITH PASSWORD '********';
gaussdb=# ALTER SESSION SET SESSION AUTHORIZATION joe PASSWORD '********';

-- Switch to the default user.
gaussdb=> ALTER SESSION SET SESSION AUTHORIZATION default;

-- Delete the ds schema.
gaussdb=# DROP SCHEMA ds;

-- Delete joe.
gaussdb=# DROP ROLE joe;