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

SET SESSION LOCAL variable_name TO = variable_value DEFAULT
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 for search_path. Only one schema can be specified using this syntax. The TO and = syntax are optional.
  • names: names is an alias for client_encoding. The TO and = syntax must be omitted.
  • time zone: time zone is an alias for timezone. The TO 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;
Back to top ↑