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.

Syntax

• Set transaction parameters of a session.

  1 2	ALTER SESSION SET [ SESSION CHARACTERISTICS AS ] TRANSACTION { ISOLATION LEVEL { READ COMMITTED | READ UNCOMMITTED } | { 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

• SESSION

  Specifies that the specified parameters take effect for the current session. If neither SESSION nor LOCAL appears, SESSION is the default value.

  If this command is executed in a transaction, the effect of the command disappears after the transaction is rolled back. Once the surrounding transaction is committed, the effect will persist until the end of the session, unless the parameters are reset by another SET statement.

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

  Example: set schema 'public';

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

• XML OPTION option

  Specifies the XML parsing mode.

  Value range: CONTENT (default) and DOCUMENT.

• config_parameter

  Specifies the name of a configurable GUC parameter. You can use SHOW ALL to view the available GUC parameters.

Examples

 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
37
38
39
40
41

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

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

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

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

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

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

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

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

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

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

-- Drop the ds schema.
openGauss=# DROP SCHEMA ds;

-- Drop the role joe.
openGauss=# DROP ROLE joe;

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

Helpful Links

SET