ALTER SESSION

Description

ALTER SESSION 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.

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

  

• Set other GUC parameters of a session.

  1 2 3 4 5 6 7 8 9 10

  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 } } ;

  

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.

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

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

• SCHEMA schema

  Specifies the current schema. The schema here 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. Encrypted passwords are not supported.

• 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

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.

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

  -- 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 the role joe. gaussdb=# DROP ROLE joe;

Helpful Links

SET