SHOW

SHOW displays the value of a session or system variable.

Syntax

SHOW variable_name ALL
Field Use
variable_name The name of the session or system variable to display.
ALL Display the values of all session and system variables.

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
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_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.
extra_float_digits 3 Adjusts 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. Reports 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.
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 Prohibits SQL statements that may be overly destructive.
standard_conforming_strings true Causes ordinary string literals ('...') to 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.

System variables

Materialize reserves system variables for region-wide configuration. Although it’s possible to SHOW system variables, you must contact us to change their value (e.g. increasing the maximum number of AWS PrivateLink connections in your region).

Name Default value Description
allowed_cluster_replica_sizes 3xsmall, 2xsmall, xsmall, small, medium, large, xlarge The allowed sizes when creating a new cluster replica.
max_aws_privatelink_connections 0 The maximum number of AWS PrivateLink connections in the region, across all schemas.
max_clusters 10 The maximum number of clusters in the region.
max_databases 1000 The maximum number of databases in the region.
max_objects_per_schema 1000 The maximum number of objects in a schema.
max_replicas_per_cluster 5 The maximum number of replicas of a single cluster.
max_result_size 1 GiB The maximum size in bytes for a single query’s result.
max_schemas_per_database 1000 The maximum number of schemas in a database.
max_secrets 100 The maximum number of secrets in the region, across all schemas.
max_sources 25 The maximum number of sources in the region, across all schemas.
max_sinks 25 The maximum number of sinks in the region, across all schemas.
max_tables 25 The maximum number of tables in the region, across all schemas.

Examples

Show active cluster

SHOW cluster;
 cluster
---------
 default

Show transaction isolation level

SHOW transaction_isolation;
 transaction_isolation
-----------------------
 strict serializable
Back to top ↑