SET
SET
modifies the value of a session variable. By default, values are set for
the duration of the current session.
To see the current value of a session variable, use SHOW
.
Syntax
Field | Use |
---|---|
variable_name | The name of the session variable to modify. |
variable_value | The value to assign to the session variable. |
SESSION | (Default) Set the value for the duration of the current session. |
LOCAL | Set the value for the duration of a single transaction. |
DEFAULT | Reset the session variable’s default value. Equivalent to RESET . |
Session variables
Name | Default value | Description |
---|---|---|
cluster | default |
The current cluster. |
cluster_replica | The target cluster replica for SELECT queries. |
|
database | materialize |
The current database. |
search_path | public |
The schema search order for names that are not schema-qualified. |
transaction_isolation | strict serializable |
The transaction isolation level. For more information, see Consistency guarantees. Accepts values: serializable , strict serializable . |
Other session variables
Name | Default value | Description |
---|---|---|
auto_route_introspection_queries | true |
Boolean flag indicating whether to force queries that depend only on system tables to run on the mz_introspection cluster for improved performance. |
application_name | The application name to be reported in statistics and logs. This variable is typically set by an application upon connection to Materialize (e.g. psql ). |
|
client_encoding | UTF8 |
The client’s character set encoding. The only supported value is UTF-8 . |
client_min_messages | notice |
The message levels that are sent to the client. Accepts values: debug5 , debug4 , debug3 , debug2 , debug1 , log , notice , warning , error . Each level includes all the levels that follow it. |
datestyle | ISO, MDY |
The display format for date and time values. The only supported value is ISO, MDY . |
emit_introspection_query_notice | true |
Whether to print a notice when querying replica introspection relations. |
emit_timestamp_notice | false |
Boolean flag indicating whether to send a notice specifying query timestamps. |
emit_trace_id_notice | false |
Boolean flag indicating whether to send a notice specifying the trace ID, when available. |
enable_session_rbac_checks | false |
Read-only. Boolean flag indicating whether RBAC is enabled for the current session. |
extra_float_digits | 3 |
Boolean flag indicating whether to adjust the number of digits displayed for floating-point values. |
failpoints | Allows failpoints to be dynamically activated. | |
idle_in_transaction_session_timeout | 120 seconds |
The maximum allowed duration that a session can sit idle in a transaction before being terminated. If this value is specified without units, it is taken as milliseconds. A value of zero disables the timeout. |
integer_datetimes | true |
Read-only. Boolean flag indicating whether the server uses 64-bit-integer dates and times. |
intervalstyle | postgres |
The display format for interval values. The only supported value is postgres . |
is_superuser | Read-only. Reports whether the current session is a superuser with admin privileges. | |
max_identifier_length | 255 |
Read-only. The maximum length in bytes of object identifiers. |
max_query_result_size | 1073741824 |
The maximum size in bytes for a single query’s result. |
mz_version | Version-dependent | Read-only. Shows the Materialize server version. |
server_version | Version-dependent | Read-only. The PostgreSQL compatible server version. |
server_version_num | Version-dependent | Read-only. The PostgreSQL compatible server version as an integer. |
sql_safe_updates | false |
Boolean flag indicating whether to prohibit SQL statements that may be overly destructive. |
standard_conforming_strings | true |
Boolean flag indicating whether ordinary string literals ('...' ) should treat backslashes literally. The only supported value is true . |
statement_timeout | 10 seconds |
The maximum allowed duration of INSERT , SELECT , UPDATE , and DELETE operations. If this value is specified without units, it is taken as milliseconds. |
timezone | UTC |
The time zone for displaying and interpreting timestamps. The only supported value is UTC . |
Alias session variables
There are a few session variables that act as aliases for other session variables.
schema
:schema
is an alias forsearch_path
. Only one schema can be specified using this syntax. TheTO
and=
syntax are optional.names
:names
is an alias forclient_encoding
. TheTO
and=
syntax must be omitted.time zone
:time zone
is an alias fortimezone
. TheTO
and=
syntax must be omitted.
Examples
Set active cluster
SHOW cluster;
cluster
---------
default
SET cluster = 'quickstart';
SHOW cluster;
cluster
------------
quickstart
Set transaction isolation level
SET transaction_isolation = 'serializable';
Set search path
SET search_path = public, qck;
SET schema = qck;