SHOW
SHOW displays the value of a session or system variable.
Syntax
| 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 |
|---|---|---|
| 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. |
Special Syntax
SHOW SCHEMA will show the first resolvable schema on the search path, or NULL if no such schema exists.
System variables
Materialize reserves system variables for region-wide configuration. Although it’s possible to SHOW system variables, you must contact us to set the value of most variables (e.g. increasing the maximum number of AWS PrivateLink connections in your region).
Some system variables can be modified by any superuser (i.e. Frontegg admin).
| Name | Default value | Description | Settable by superuser |
|---|---|---|---|
| allowed_cluster_replica_sizes | 3xsmall, 2xsmall, xsmall, small, medium, large, xlarge |
The allowed sizes when creating a new cluster replica. | No |
| enable_rbac_checks | true |
Boolean flag indicating whether to apply RBAC checks before executing statements. | Yes |
| max_aws_privatelink_connections | 0 |
The maximum number of AWS PrivateLink connections in the region, across all schemas. | No |
| max_clusters | 10 |
The maximum number of clusters in the region | No |
| max_connections | 1000 |
The maximum number of concurrent connections in the region | No |
| max_credit_consumption_rate | 1024 |
The maximum rate of credit consumption in a region. Credits are consumed based on the size of cluster replicas in use. | No |
| max_databases | 1000 |
The maximum number of databases in the region. | No |
| max_kafka_connections | 1000 |
The maximum number of Kafka connections in the region, across all schemas. | No |
| max_objects_per_schema | 1000 |
The maximum number of objects in a schema. | No |
| max_postgres_connections | 1000 |
The maximum number of PostgreSQL connections in the region, across all schemas. | No |
| max_replicas_per_cluster | 5 |
The maximum number of replicas of a single cluster | No |
| max_result_size | 1 GiB |
The maximum size in bytes for a single query’s result. | No |
| max_roles | 1000 |
The maximum number of roles in the region. | No |
| max_schemas_per_database | 1000 |
The maximum number of schemas in a database. | No |
| max_secrets | 100 |
The maximum number of secrets in the region, across all schemas. | No |
| max_sources | 25 |
The maximum number of sources in the region, across all schemas. | No |
| max_sinks | 25 |
The maximum number of sinks in the region, across all schemas. | No |
| max_tables | 25 |
The maximum number of tables in the region, across all schemas | No |
Examples
Show active cluster
SHOW cluster;
cluster
---------
default
Show transaction isolation level
SHOW transaction_isolation;
transaction_isolation
-----------------------
strict serializable