...`](/sql/create-materialized-view). |
## Details
### Replacing a materialized view
> **Public Preview:** This feature is in public preview.
You can use [`CREATE REPLACEMENT MATERIALIZED
VIEW`](/sql/create-materialized-view/) with [`ALTER MATERIALIZED VIEW ... APPLY
REPLACEMENT`](/sql/alter-materialized-view) to replace materialized views
in-place without recreating dependent objects or incurring downtime.
When replacing a materialized view, the operation:
See [Recommended checks before replacing a
view](/sql/alter-materialized-view/#recommended-checks-before-replacing-a-view).
#### Recommended checks before replacing a view
Before applying, verify that the replacement materialized view is hydrated
to avoid downtime:
SELECT
mv.name,
h.hydrated
FROM mz_catalog.mz_materialized_views AS mv
JOIN mz_internal.mz_hydration_statuses AS h ON (mv.id = h.object_id)
WHERE mv.name = '<replacement_view>';
Issue: Command does not return.
Common cause: The original materialized view is lagging behind the replacement. If
the original is lagging behind the replacement, the command waits for the
original view to catch up.
Action: Cancel the command and check whether the original materialized view is
lagging behind the replacement.
To check whether the original materialized view is lagging behind the replacement, run
the following query to check their write frontiers, substituting the names
of your original and replacement materialized views.
SELECT o.name, f.write_frontier
FROM mz_objects o, mz_cluster_replica_frontiers f
WHERE o.name in ('<view>', '<view_replacement>')
AND f.object_id = o.id;
If the original materialized view is behind, rerun the query to check the progress of the
original materialized view. If the rate of advancement suggests that catch
up will take an extended period of time, it is recommended to drop the
replacement view.
## Privileges
The privileges required to execute this statement are:
- Ownership of the materialized view.
- In addition, to change owners:
- Role membership in `new_owner`.
- `CREATE` privileges on the containing schema if the materialized view is
namespaced by a schema.
- In addition, to apply a replacement:
- Ownership of the replacement materialized view.
## Examples
### Replace a materialized view
> **Public Preview:** This feature is in public preview.
A replacement materialized view can only be applied to the target materialized
view specified in the `FOR` clause of the [`CREATE REPLACEMENT MATERIALIZED
VIEW`](/sql/create-materialized-view/) statement.
#### Example Prerequisite
The following example creates a replacement materialized view
`winning_bids_replacement` for the `winning_bids` materialized view. The
replacement view specifies a different filter `mz_now() > a.end_time` than
the existing view `mz_now() >= a.end_time`.
```mzsql
CREATE REPLACEMENT MATERIALIZED VIEW winning_bids_replacement
FOR winning_bids AS
SELECT DISTINCT ON (a.id) b.*, a.item, a.seller
FROM auctions AS a
JOIN bids AS b
ON a.id = b.auction_id
WHERE b.bid_time < a.end_time
AND mz_now() > a.end_time
ORDER BY a.id,
b.amount DESC,
b.bid_time,
b.buyer;
```
The replacement view hydrates in the background.
#### Apply the replacement
Assume that `winning_bids_replacement` is hydrated to avoid downtime (see
[Recommended checks before replacing a
view](/sql/alter-materialized-view/#recommended-checks-before-replacing-a-view)
for details).
The following example replaces the `winning_bids` materialized view
with `winning_bids_replacement`:
```mzsql
ALTER MATERIALIZED VIEW winning_bids
APPLY REPLACEMENT winning_bids_replacement;
```
For a step-by-step tutorial on replacing a materialized view, see [Replace
materialized views
guide](/transform-data/updating-materialized-views/replace-materialized-view/).
## Related pages
- [`CREATE MATERIALIZED VIEW`](/sql/create-materialized-view)
- [`SHOW MATERIALIZED VIEWS`](/sql/show-materialized-views)
- [`SHOW CREATE MATERIALIZED VIEW`](/sql/show-create-materialized-view)
- [`DROP MATERIALIZED VIEW`](/sql/drop-materialized-view)
---
## ALTER NETWORK POLICY (Cloud)
*Available for Materialize Cloud only*
`ALTER NETWORK POLICY` alters an existing network policy. Network policies are
part of Materialize's framework for [access control](/security/cloud/).
Changes to a network policy will only affect new connections
and **will not** terminate active connections.
## Syntax
```mzsql
ALTER NETWORK POLICY SET (
RULES (
(action='allow', direction='ingress', address=)
[, ...]
)
)
;
```
| Syntax element | Description |
| --- | --- |
| `` | The name of the network policy to modify. |
| `` | The name for the network policy rule. Must be unique within the network policy. |
| `` | The Classless Inter-Domain Routing (CIDR) block to which the rule applies. |
## Details
### Pre-installed network policy
When you enable a Materialize region, a default network policy named `default`
will be pre-installed. This policy has a wide open ingress rule `allow
0.0.0.0/0`. You can modify or drop this network policy at any time.
> **Note:** The default value for the `network_policy` session parameter is `default`.
> Before dropping the `default` network policy, a _superuser_ (i.e. `Organization
> Admin`) must run [`ALTER SYSTEM SET network_policy`](/sql/alter-system-set) to
> change the default value.
### Lockout prevention
To prevent lockout, the IP of the active user is validated against the policy
changes requested. This prevents users from modifying network policies in a way
that could lock them out of the system.
## Privileges
The privileges required to execute this statement are:
- Ownership of the network policy.
## Examples
```mzsql
CREATE NETWORK POLICY office_access_policy (
RULES (
new_york (action='allow', direction='ingress',address='1.2.3.4/28'),
minnesota (action='allow',direction='ingress',address='2.3.4.5/32')
)
);
```
```mzsql
ALTER NETWORK POLICY office_access_policy SET (
RULES (
new_york (action='allow', direction='ingress',address='1.2.3.4/28'),
minnesota (action='allow',direction='ingress',address='2.3.4.5/32'),
boston (action='allow',direction='ingress',address='4.5.6.7/32')
)
);
```
```mzsql
ALTER SYSTEM SET network_policy = office_access_policy;
```
## Related pages
- [`CREATE NETWORK POLICY`](../create-network-policy)
- [`DROP NETWORK POLICY`](../drop-network-policy)
---
## ALTER ROLE
`ALTER ROLE` alters the attributes of an existing role.[^1]
[^1]: Materialize does not support the `SET ROLE` command.
## Syntax
**Cloud:**
### Cloud
The following syntax is used to alter a role in Materialize Cloud.
```mzsql
ALTER ROLE
[[WITH] INHERIT]
[SET =|TO ]
[RESET ];
```
| Syntax element | Description |
| --- | --- |
| `INHERIT` | *Optional.* If specified, grants the role the ability to inherit privileges of other roles. *Default.* |
| `SET TO ` | *Optional.* If specified, sets the configuration parameter for the role to the `` or if the value specified is `DEFAULT`, the system's default (equivalent to `ALTER ROLE ... RESET `). To view the configuration parameter defaults for a role, see [`mz_role_parameters`](/reference/system-catalog/mz_catalog#mz_role_parameters). {{< note >}} - Altering the configuration parameter for a role only affects **new sessions**. - Role configuration parameters are **not inherited**. {{< /note >}} |
| `RESET ` | *Optional.* If specified, resets the configuration parameter for the role to the system's default. To view the configuration parameter defaults for a role, see [`mz_role_parameters`](/reference/system-catalog/mz_catalog#mz_role_parameters). {{< note >}} - Altering the configuration parameter for a role only affects **new sessions**. - Role configuration parameters are **not inherited**. {{< /note >}} |
**Note:**
- Materialize Cloud does not support the `NOINHERIT` option for `ALTER
ROLE`.
- Materialize Cloud does not support the `LOGIN` and `SUPERUSER` attributes
for `ALTER ROLE`. See [Organization
roles](/security/cloud/users-service-accounts/#organization-roles)
instead.
- Materialize Cloud does not use role attributes to determine a role's
ability to alter top level objects such as databases and other roles.
Instead, Materialize Cloud uses system level privileges. See [GRANT
PRIVILEGE](../grant-privilege) for more details.
**Self-Managed:**
### Self-Managed
The following syntax is used to alter a role in Materialize Self-Managed.
```mzsql
ALTER ROLE
[WITH]
[ SUPERUSER | NOSUPERUSER ]
[ LOGIN | NOLOGIN ]
[ INHERIT | NOINHERIT ]
[ PASSWORD ]]
[SET TO ]
[RESET ]
;
```
| Syntax element | Description |
| --- | --- |
| `INHERIT` | *Optional.* If specified, grants the role the ability to inherit privileges of other roles. *Default.* |
| `LOGIN` | *Optional.* If specified, allows a role to login via the PostgreSQL or web endpoints |
| `NOLOGIN` | *Optional.* If specified, prevents a role from logging in. This is the default behavior if `LOGIN` is not specified. |
| `SUPERUSER` | *Optional.* If specified, grants the role superuser privileges. |
| `NOSUPERUSER` | *Optional.* If specified, prevents the role from having superuser privileges. This is the default behavior if `SUPERUSER` is not specified. |
| `PASSWORD` | ***Public Preview*** *Optional.* This feature may have minor stability issues. If specified, allows you to set a password for the role. |
| `SET TO ` | *Optional.* If specified, sets the configuration parameter for the role to the `` or if the value specified is `DEFAULT`, the system's default (equivalent to `ALTER ROLE ... RESET `). To view the configuration parameter defaults for a role, see [`mz_role_parameters`](/reference/system-catalog/mz_catalog#mz_role_parameters). {{< note >}} - Altering the configuration parameter for a role only affects **new sessions**. - Role configuration parameters are **not inherited**. {{< /note >}} |
| `RESET ` | *Optional.* If specified, resets the configuration parameter for the role to the system's default. To view the configuration parameter defaults for a role, see [`mz_role_parameters`](/reference/system-catalog/mz_catalog#mz_role_parameters). {{< note >}} - Altering the configuration parameter for a role only affects **new sessions**. - Role configuration parameters are **not inherited**. {{< /note >}} |
**Note:**
- Self-Managed Materialize does not support the `NOINHERIT` option for
`ALTER ROLE`.
- With the exception of the `SUPERUSER` attribute, Self-Managed Materialize
does not use role attributes to determine a role's ability to create top
level objects such as databases and other roles. Instead, Self-Managed
Materialize uses system level privileges. See [GRANT
PRIVILEGE](../grant-privilege) for more details.
## Restrictions
You may not specify redundant or conflicting sets of options. For example,
Materialize will reject the statement `ALTER ROLE ... INHERIT INHERIT`.
## Examples
#### Altering the attributes of a role
```mzsql
ALTER ROLE rj INHERIT;
```
```mzsql
SELECT name, inherit FROM mz_roles WHERE name = 'rj';
```
```nofmt
rj true
```
#### Setting configuration parameters for a role
```mzsql
SHOW cluster;
quickstart
ALTER ROLE rj SET cluster TO rj_compute;
-- Role parameters only take effect for new sessions.
SHOW cluster;
quickstart
-- Start a new SQL session with the role 'rj'.
SHOW cluster;
rj_compute
-- In a new SQL session with a role that is not 'rj'.
SHOW cluster;
quickstart
```
#### Making a role a superuser (Self-Managed)
Unlike regular roles, superusers have unrestricted access to all objects in the system and can perform any action on them.
```mzsql
ALTER ROLE rj SUPERUSER;
```
To verify that the role has superuser privileges, you can query the `pg_authid` system catalog:
```mzsql
SELECT name, rolsuper FROM pg_authid WHERE rolname = 'rj';
```
```nofmt
rj t
```
#### Removing the superuser attribute from a role (Self-Managed)
NOSUPERUSER will remove the superuser attribute from a role, preventing it from having unrestricted access to all objects in the system.
```mzsql
ALTER ROLE rj NOSUPERUSER;
```
```mzsql
SELECT name, rolsuper FROM pg_authid WHERE rolname = 'rj';
```
```nofmt
rj f
```
#### Removing a role's password (Self-Managed)
> **Warning:** Setting a NULL password removes the password.
```mzsql
ALTER ROLE rj PASSWORD NULL;
```
#### Changing a role's password (Self-Managed)
```mzsql
ALTER ROLE rj PASSWORD 'new_password';
```
## Privileges
The privileges required to execute this statement are:
- `CREATEROLE` privileges on the system.
## Related pages
- [`CREATE ROLE`](../create-role)
- [`DROP ROLE`](../drop-role)
- [`DROP USER`](../drop-user)
- [`GRANT ROLE`](../grant-role)
- [`REVOKE ROLE`](../revoke-role)
- [`ALTER OWNER`](/sql/#rbac)
- [`GRANT PRIVILEGE`](../grant-privilege)
- [`REVOKE PRIVILEGE`](../revoke-privilege)
---
## ALTER SCHEMA
Use `ALTER SCHEMA` to:
- Swap the name of a schema with that of another schema.
- Rename a schema.
- Change owner of a schema.
## Syntax
**Swap with:**
### Swap with
To swap the name of a schema with that of another schema:
```mzsql
ALTER SCHEMA SWAP WITH ;
```
| Syntax element | Description |
| --- | --- |
| `` | The name of the schema you want to swap. |
| `` | The name of the other schema you want to swap with. |
**Rename schema:**
### Rename schema
To rename a schema:
```mzsql
ALTER SCHEMA RENAME TO ;
```
| Syntax element | Description |
| --- | --- |
| `` | The current name of the schema. |
| `` | The new name of the schema. |
See also [Renaming restrictions](/sql/identifiers/#renaming-restrictions).
**Change owner to:**
### Change owner to
To change the owner of a schema:
```mzsql
ALTER SCHEMA OWNER TO ;
```
| Syntax element | Description |
| --- | --- |
| `` | The name of the schema you want to change ownership of. |
| `` | The new owner of the schema. |
To change the owner of a schema, you must be the owner of the schema and have
membership in the ``. See also [Privileges](#privileges).
## Examples
### Swap schema names
Swapping two schemas is useful for a blue/green deployment. The following swaps
the names of the `blue` and `green` schemas.
```mzsql
CREATE SCHEMA blue;
CREATE TABLE blue.numbers (n int);
CREATE SCHEMA green;
CREATE TABLE green.tags (tag text);
ALTER SCHEMA blue SWAP WITH green;
-- The schema which was previously named 'green' is now named 'blue'.
SELECT * FROM blue.tags;
```
## Privileges
The privileges required to execute this statement are:
- Ownership of the schema.
- In addition,
- To swap with another schema:
- Ownership of the other schema
- To change owners:
- Role membership in `new_owner`.
- `CREATE` privileges on the containing database.
## See also
- [`SHOW CREATE VIEW`](/sql/show-create-view)
- [`SHOW VIEWS`](/sql/show-views)
- [`SHOW SOURCES`](/sql/show-sources)
- [`SHOW INDEXES`](/sql/show-indexes)
- [`SHOW SECRETS`](/sql/show-secrets)
- [`SHOW SINKS`](/sql/show-sinks)
---
## ALTER SECRET
Use `ALTER SECRET` to:
- Change the value of the secret.
- Rename a secret.
- Change owner of a secret.
## Syntax
**Change value:**
### Change value
To change the value of a secret:
```mzsql
ALTER SECRET [IF EXISTS] AS ;
```
| Syntax element | Description |
| --- | --- |
| `` | The identifier of the secret you want to alter. |
| `` | The new value for the secret. The _value_ expression may not reference any relations, and must be implicitly castable to `bytea`. |
**Rename:**
### Rename
To rename a secret:
```mzsql
ALTER SECRET [IF EXISTS] RENAME TO ;
```
| Syntax element | Description |
| --- | --- |
| `` | The current name of the secret. |
| `` | The new name of the secret. |
See also [Renaming restrictions](/sql/identifiers/#renaming-restrictions).
**Change owner:**
### Change owner
To change the owner of a secret:
```mzsql
ALTER SECRET [IF EXISTS] OWNER TO ;
```
| Syntax element | Description |
| --- | --- |
| `` | The name of the secret you want to change ownership of. |
| `` | The new owner of the secret. |
To change the owner, you must be a current owner as well as have membership in
the ``.
## Details
### Changing the secret value
After an `ALTER SECRET` command is executed:
* Future [`CREATE CONNECTION`], [`CREATE SOURCE`], and [`CREATE SINK`]
commands will use the new value of the secret immediately.
* Running sources and sinks that reference the secret will **not** immediately
use the new value of the secret. Sources and sinks may cache the old secret
value for several weeks.
To force a running source or sink to refresh its secrets, drop and recreate
all replicas of the cluster hosting the source or sink.
For a managed cluster:
```
ALTER CLUSTER storage_cluster SET (REPLICATION FACTOR = 0);
ALTER CLUSTER storage_cluster SET (REPLICATION FACTOR = 1);
```
For an unmanaged cluster:
```
DROP CLUSTER REPLICA storage_cluster.r1;
CREATE CLUSTER REPLICA storage_cluster.r1 (SIZE = '');
```
## Examples
```mzsql
ALTER SECRET kafka_ca_cert AS decode('c2VjcmV0Cg==', 'base64');
```
## Privileges
The privileges required to execute this statement are:
- Ownership of the secret being altered.
- In addition, to change owners:
- Role membership in `new_owner`.
- `CREATE` privileges on the containing schema if the secret is namespaced
by a schema.
## Related pages
- [`SHOW SECRETS`](/sql/show-secrets)
- [`DROP SECRET`](/sql/drop-secret)
[`CREATE CONNECTION`]: /sql/create-connection/
[`CREATE SOURCE`]: /sql/create-source
[`CREATE SINK`]: /sql/create-sink
[`ALTER SOURCE`]: /sql/alter-source
[`ALTER SINK`]: /sql/alter-sink
---
## ALTER SINK
Use `ALTER SINK` to:
- Change the relation you want to sink from. This is useful in the context of
[blue/green deployments](/manage/dbt/blue-green-deployments/).
- Rename a sink.
- Change owner of a sink.
## Syntax
**Change sink from relation:**
### Change sink from relation
To change the relation you want to sink from:
```mzsql
ALTER SINK SET FROM ;
```
| Syntax element | Description |
| --- | --- |
| `` | The name of the sink you want to change. |
| `` | The name of the relation you want to sink from. |
**Rename:**
### Rename
To rename a sink:
```mzsql
ALTER SINK RENAME TO ;
```
| Syntax element | Description |
| --- | --- |
| `` | The current name of the sink. |
| `` | The new name of the sink. |
See also [Renaming restrictions](/sql/identifiers/#renaming-restrictions).
**Change owner:**
### Change owner
To change the owner of a sink:
```mzsql
ALTER SINK OWNER TO ;
```
| Syntax element | Description |
| --- | --- |
| `` | The name of the sink you want to change ownership of. |
| `` | The new owner of the sink. |
To change the owner, you must be a current owner as well as have membership
in the ``.
## Details
### Changing sink from relation
#### Valid schema changes
For `ALTER SINK` to be successful, the newly specified relation must lead to a
valid sink definition with the same conditions as the original `CREATE SINK`
statement.
When using the Avro format with a schema registry, the generated Avro
schema for the new relation must be compatible with the previously published
schema. If that's not the case, the `ALTER SINK` command will succeed, but the
subsequent execution of the sink will result in errors and will not be able to
make progress.
To monitor the status of a sink after an `ALTER SINK` command, navigate to the
respective object page in the [Materialize console](/console/),
or query the [`mz_internal.mz_sink_statuses`](/reference/system-catalog/mz_internal/#mz_sink_statuses)
system catalog view.
#### Cutover timestamp
To alter the upstream relation a sink depends on while ensuring continuity in
data processing, Materialize must pick a consistent cutover timestamp. When you
execute an `ALTER SINK` command, the resulting output will contain:
- all updates that happened before the cutover timestamp for the old
relation, and
- all updates that happened after the cutover timestamp for the new
relation.
> **Note:** To select a consistent timestamp, Materialize must wait for the previous
> definition of the sink to emit results up until the oldest timestamp at which
> the contents of the new upstream relation are known. Attempting to `ALTER` an
> unhealthy sink that can't make progress will result in the command timing out.
#### Cutover scenarios and workarounds
Because Materialize emits updates from the new relation **only** if
they occur after the cutover timestamp, the following scenarios may occur:
##### Scenario 1: Topic contains stale value for a key
Since cutting over a sink to a new upstream relation using `ALTER SINK` does not
emit a snapshot of the new relation, all keys will appear to have the old value
for the key in the previous relation until an update happens to them. At that
point, the current value will be published to the topic.
Consumers of the topic must be prepared to handle an old value for a key, for
example by filling in additional columns with default values.
**Workarounds**:
- Use an intermediary, temporary view to handle the cutover scenario difference.
See [Example: Handle cutover scenarios](#handle-cutover-scenarios).
- Alternatively, forcing an update to all the keys after `ALTER SINK` will force
the sink to re-emit all the updates.
##### Scenario 2: Topic is missing a key that exists in the new relation
As a consequence of not re-emitting a snapshot after `ALTER SINK`, if additional
keys exist in the new relation that are not present in the old one, these will
not be visible in the topic after the cutover. The keys will remain absent until
an update occurs for the keys, at which point Materialize will emit a record to
the topic containing the new value.
**Workarounds**:
- Use an intermediary, temporary view to handle the cutover scenario difference.
See [Example: Handle cutover scenarios](#handle-cutover-scenarios).
- Alternatively, ensure that both the old and the new relations have identical
keyspaces to avoid the scenario.
##### Scenario 3: Topic contains a key that does not exist in the new relation
Materialize does not compare the contents of the old relation with the new
relation when cutting a sink over. This means that, if the old relation
contains additional keys that are not present in the new one, these records
will remain in the topic without a corresponding tombstone record. This may
cause readers to assume that certain keys exist when they don't.
**Workarounds**:
- Use an intermediary, temporary view to handle the cutover scenario difference.
See [Example: Handle cutover scenarios](#handle-cutover-scenarios).
- Alternatively, ensure that both the old and the new relations have identical
keyspaces to avoid the scenario.
### Catalog objects
A sink cannot be created directly on a [catalog object](/reference/system-catalog/).
As a workaround, you can create a materialized view on a catalog object and
create a sink on the materialized view.
## Privileges
The privileges required to execute this statement are:
- Ownership of the sink being altered.
- In addition,
- To change the sink from relation:
- `SELECT` privileges on the new relation being written out to an external system.
- `CREATE` privileges on the cluster maintaining the sink.
- `USAGE` privileges on all connections and secrets used in the sink definition.
- `USAGE` privileges on the schemas that all connections and secrets in the
statement are contained in.
- To change owners:
- Role membership in `new_owner`.
- `CREATE` privileges on the containing schema if the sink is namespaced
by a schema.
## Examples
### Alter sink
The following example alters a sink originally created from `matview_old` to use
`matview_new` instead.
That is, assume you have a Kafka sink `avro_sink` created from `matview_old`
(See [`CREATE SINK`:Kafka/Redpanda](/sql/create-sink/kafka/) for more
information):
```mzsql
CREATE SINK avro_sink
FROM matview_old
INTO KAFKA CONNECTION kafka_connection (TOPIC 'test_avro_topic')
KEY (key_col)
FORMAT AVRO USING CONFLUENT SCHEMA REGISTRY CONNECTION csr_connection
ENVELOPE UPSERT
;
```
To have the sink read from `matview_new` instead of `matview_old`, you can
use `ALTER SINK` to change the `FROM `:
{{< note >}}
`matview_new` must be compatible with the previously published
schema. Otherwise, the `ALTER SINK` command will succeed, but the
subsequent execution of the sink will result in errors and will not be able
to make progress. See [Valid schema changes](#valid-schema-changes) for
details.
{{< /note >}}
```mzsql
ALTER SINK avro_sink
SET FROM matview_new
;
```
{{< tip >}}
Because Materialize emits updates from the newly specified relation **only** if
they happen after the cutover timestamp, you might observe the following
scenarios:
- [Topic contains stale value for a
key](#scenario-1-topic-contains-stale-value-for-a-key)
- [Topic is missing a key that exists in the new relation](#scenario-2-topic-is-missing-a-key-that-exists-in-the-new-relation)
- [Topic contains a key that does not exist in the new relation](#scenario-3-topic-contains-a-key-that-does-not-exist-in-the-new-relation)
For workaround, see [Example: Handle cutover scenarios](#handle-cutover-scenarios)
{{< /tip >}}
### Handle cutover scenarios
Because Materialize emits updates from the newly specified relation **only** if
they happen after the cutover timestamp, you might observe the following
scenarios:
- [Topic contains stale value for a
key](#scenario-1-topic-contains-stale-value-for-a-key)
- [Topic is missing a key that exists in the new relation](#scenario-2-topic-is-missing-a-key-that-exists-in-the-new-relation)
- [Topic contains a key that does not exist in the new relation](#scenario-3-topic-contains-a-key-that-does-not-exist-in-the-new-relation)
To handle these scenarios, you can first alter sink to an intermediary
materialized view. The intermediary materialized view uses a temporary table
`switch` that switches the view's contents from old relation content to new
relation content. At the time of the switch, Materialize emits the diff of
the changes. Then, after the sink upper has advanced beyond the time of the
switch, you can `ALTER SINK` to the new relation (and remove the temporary
intermediary materialized view and table).
1. For example, create a table `switch` and a temporary materialized view
`transition` that contains either:
- the `matview_old` content if `switch.value` is `false`.
- the `matview_new` content if `switch.value` is `true`.
At first, the `switch.value` is `false`, so the `transition` materialized view contains the `matview_old` content.
```mzsql
CREATE TABLE switch (value bool);
INSERT INTO switch VALUES (false); -- controls whether we want the new or the old materialized view.
CREATE MATERIALIZED VIEW transition AS
(SELECT matview_old.* FROM matview_old JOIN switch ON switch.value = false)
UNION ALL
(SELECT matview_new.* FROM matview_new JOIN switch ON switch.value = true)
;
```
1. `ALTER SINK` to use `transition`, which currently contains `matview_old` content:
```mzsql
ALTER SINK avro_sink SET FROM transition;
```
1. Update `switch.value` to `true`, which causes the `transition` materialized view to contain `matview_new` content:
```mzsql
UPDATE switch SET value = true;
```
1. Wait for the sink's upper frontier
([`mz_frontiers`](/reference/system-catalog/mz_internal/#mz_frontiers)) to advance
beyond the time of the switch update. Once advanced, alter sink to use
`matview_new`:
```mzsql
-- After sink upper has advanced beyond the time of the switch UPDATE.
ALTER SINK avro_sink SET FROM matview_new;
```
1. Drop the `transition` materialized view and the `switch` table:
```mzsql
DROP MATERIALIZED VIEW transition;
DROP TABLE switch;
```
## See also
- [`CREATE SINK`](/sql/create-sink/)
- [`SHOW SINKS`](/sql/show-sinks)
---
## ALTER SOURCE
Use `ALTER SOURCE` to:
- Add a subsource to a source.
- Rename a source.
- Change owner of a source.
- Change retain history configuration for the source.
- Change timestamp interval for the source.
## Syntax
**Add subsource:**
### Add subsource
To add the specified upstream table(s) to the specified PostgreSQL/MySQL/SQL Server source:
```mzsql
ALTER SOURCE [IF EXISTS]
ADD SUBSOURCE|TABLE [AS ] [, ...]
[WITH ()]
;
```
| Syntax element | Description |
| --- | --- |
| `` | The name of the PostgreSQL/MySQL/SQL Server source you want to alter. |
| `` | The upstream table to add to the source. |
| **AS** `` | Optional. The name for the subsource in Materialize. |
| **WITH (TEXT COLUMNS (`` [, ...]))** | Optional. List of columns to decode as `text` for types that are unsupported in Materialize. |
> **Note:** When you add a new subsource to an existing source ([`ALTER SOURCE ... ADD
> SUBSOURCE ...`](/sql/alter-source/)), Materialize starts the snapshotting
> process for the new subsource. During this snapshotting, the data ingestion for
> the existing subsources for the same source is temporarily blocked. As such, if
> possible, you can resize the cluster to speed up the snapshotting process and
> once the process finishes, resize the cluster for steady-state.
**Rename:**
### Rename
To rename a source:
```mzsql
ALTER SOURCE RENAME TO ;
```
| Syntax element | Description |
| --- | --- |
| `` | The current name of the source you want to alter. |
| `` | The new name of the source. |
See also [Renaming restrictions](/sql/identifiers/#renaming-restrictions).
**Change owner:**
### Change owner
To change the owner of a source:
```mzsql
ALTER SOURCE OWNER TO ;
```
| Syntax element | Description |
| --- | --- |
| `` | The name of the source you want to change ownership of. |
| `` | The new owner of the source. |
To change the owner of a source, you must be the owner of the source and have
membership in the ``. See also [Privileges](#privileges).
**(Re)Set retain history config:**
### (Re)Set retain history config
To set the retention history for a source:
```mzsql
ALTER SOURCE [IF EXISTS] SET (RETAIN HISTORY [=] FOR );
```
| Syntax element | Description |
| --- | --- |
| `` | The name of the source you want to alter. |
| `` | ***Private preview.** This option has known performance or stability issues and is under active development.* Duration for which Materialize retains historical data, which is useful to implement [durable subscriptions](/transform-data/patterns/durable-subscriptions/#history-retention-period). Accepts positive [interval](/sql/types/interval/) values (e.g. `'1hr'`). Default: `1s`. |
To reset the retention history to the default for a source:
```mzsql
ALTER SOURCE [IF EXISTS] RESET (RETAIN HISTORY);
```
| Syntax element | Description |
| --- | --- |
| `` | The name of the source you want to alter. |
**(Re)Set timestamp interval:**
### (Re)Set timestamp interval
To set the timestamp interval for a source:
```mzsql
ALTER SOURCE [IF EXISTS] SET (TIMESTAMP INTERVAL [=] );
```
| Syntax element | Description |
| --- | --- |
| `` | The name of the source you want to alter. |
| `` | The interval at which timestamps are assigned to the data read from this source. Accepts positive [interval](/sql/types/interval/) values (e.g. `'500ms'`, `'1s'`). The value must be between the system parameters `min_timestamp_interval` and `max_timestamp_interval`. Default: `1s`. |
To reset the timestamp interval to the system default for a source:
```mzsql
ALTER SOURCE [IF EXISTS] RESET (TIMESTAMP INTERVAL);
```
| Syntax element | Description |
| --- | --- |
| `` | The name of the source you want to alter. |
## Context
### Adding subsources to a PostgreSQL/MySQL/SQL Server source
Note that using a combination of dropping and adding subsources lets you change
the schema of the PostgreSQL/MySQL/SQL Server tables that are ingested.
> **Important:** When you add a new subsource to an existing source ([`ALTER SOURCE ... ADD
> SUBSOURCE ...`](/sql/alter-source/)), Materialize starts the snapshotting
> process for the new subsource. During this snapshotting, the data ingestion for
> the existing subsources for the same source is temporarily blocked. As such, if
> possible, you can resize the cluster to speed up the snapshotting process and
> once the process finishes, resize the cluster for steady-state.
### Dropping subsources from a PostgreSQL/MySQL/SQL Server source
Dropping a subsource prevents Materialize from ingesting any data from it, in
addition to dropping any state that Materialize previously had for the table
(such as its contents).
If a subsource encounters a deterministic error, such as an incompatible schema
change (e.g. dropping an ingested column), you can drop the subsource. If you
want to ingest it with its new schema, you can then add it as a new subsource.
You cannot drop the "progress subsource".
## Examples
### Adding subsources
```mzsql
ALTER SOURCE pg_src ADD SUBSOURCE tbl_a, tbl_b AS b WITH (TEXT COLUMNS [tbl_a.col]);
```
> **Important:** When you add a new subsource to an existing source ([`ALTER SOURCE ... ADD
> SUBSOURCE ...`](/sql/alter-source/)), Materialize starts the snapshotting
> process for the new subsource. During this snapshotting, the data ingestion for
> the existing subsources for the same source is temporarily blocked. As such, if
> possible, you can resize the cluster to speed up the snapshotting process and
> once the process finishes, resize the cluster for steady-state.
### Dropping subsources
To drop a subsource, use the [`DROP SOURCE`](/sql/drop-source/) command:
```mzsql
DROP SOURCE tbl_a, b CASCADE;
```
### Changing the timestamp interval
To set a custom timestamp interval for a source:
```mzsql
ALTER SOURCE kafka_src SET (TIMESTAMP INTERVAL = '500ms');
```
To reset the timestamp interval to the system default:
```mzsql
ALTER SOURCE kafka_src RESET (TIMESTAMP INTERVAL);
```
## Privileges
The privileges required to execute this statement are:
- Ownership of the source being altered.
- In addition, to change owners:
- Role membership in `new_owner`.
- `CREATE` privileges on the containing schema if the source is namespaced
by a schema.
## See also
- [`CREATE SOURCE`](/sql/create-source/)
- [`DROP SOURCE`](/sql/drop-source/)
- [`SHOW SOURCES`](/sql/show-sources)
---
## ALTER SYSTEM RESET
Use `ALTER SYSTEM RESET` to globally restore the value of a configuration
parameter to its default value. This command is an alternative spelling for
[`ALTER SYSTEM SET...TO DEFAULT`](../alter-system-set).
To see the current value of a configuration parameter, use [`SHOW`](../show).
## Syntax
```mzsql
ALTER SYSTEM RESET ;
```
Syntax element | Description
---------------|------------
`` | The configuration parameter's name.
### Key configuration parameters
Name | Default value | Description | Modifiable?
--------------------------------------------|---------------------------|-----------------------------------------------------------------------|--------------
`cluster` | `quickstart` | The current cluster. | Yes
`cluster_replica` | | The target cluster replica for `SELECT` queries. | Yes
`database` | `materialize` | The current database. | Yes
`search_path` | `public` | The schema search order for names that are not schema-qualified. | Yes
`transaction_isolation` | `strict serializable` | The transaction isolation level. For more information, see [Consistency guarantees](/overview/isolation-level/).
Accepts values: `serializable`, `strict serializable`. | Yes
### Other configuration parameters
Name | Default value | Description | Modifiable?
--------------------------------------------|---------------------------|------------------------------------------------------------------------------------------------------------------------------------------------------------------------|----------------------------------------------------------------
`allowed_cluster_replica_sizes` | *Varies* | The allowed sizes when creating a new cluster replica. | [Contact support]
`application_name` | | The application name to be reported in statistics and logs. This parameter is typically set by an application upon connection to Materialize (e.g. `psql`). | Yes
`auto_route_catalog_queries` | `true` | Boolean flag indicating whether to force queries that depend only on system tables to run on the `mz_catalog_server` cluster for improved performance. | Yes
`client_encoding` | `UTF8` | The client's character set encoding. The only supported value is `UTF-8`. | Yes
`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. | Yes
`datestyle` | `ISO, MDY` | The display format for date and time values. The only supported value is `ISO, MDY`. | Yes
`emit_introspection_query_notice` | `true` | Whether to print a notice when querying replica introspection relations. | Yes
`emit_timestamp_notice` | `false` | Boolean flag indicating whether to send a `notice` specifying query timestamps. | Yes
`emit_trace_id_notice` | `false` | Boolean flag indicating whether to send a `notice` specifying the trace ID, when available. | Yes
`enable_rbac_checks` | `true` | Boolean flag indicating whether to apply RBAC checks before executing statements. | Yes
`enable_session_rbac_checks` | `false` | Boolean flag indicating whether RBAC is enabled for the current session. | No
`extra_float_digits` | `3` | Boolean flag indicating whether to adjust the number of digits displayed for floating-point values. | Yes
`failpoints` | | Allows failpoints to be dynamically activated. | No
`idle_in_transaction_session_timeout` | `120s` | 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 (`ms`). A value of zero disables the timeout. | Yes
`integer_datetimes` | `true` | Boolean flag indicating whether the server uses 64-bit-integer dates and times. | No
`intervalstyle` | `postgres` | The display format for interval values. The only supported value is `postgres`. | Yes
`is_superuser` | | Reports whether the current session is a _superuser_ with admin privileges. | No
`max_aws_privatelink_connections` | `0` | The maximum number of AWS PrivateLink connections in the region, across all schemas. | [Contact support]
`max_clusters` | `10` | The maximum number of clusters in the region | [Contact support]
`max_connections` | `5000` | The maximum number of concurrent connections in the region | [Contact support]
`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. | [Contact support]
`max_databases` | `1000` | The maximum number of databases in the region. | [Contact support]
`max_identifier_length` | `255` | The maximum length in bytes of object identifiers. | No
`max_kafka_connections` | `1000` | The maximum number of Kafka connections in the region, across all schemas. | [Contact support]
`max_mysql_connections` | `1000` | The maximum number of MySQL connections in the region, across all schemas. | [Contact support]
`max_objects_per_schema` | `1000` | The maximum number of objects in a schema. | [Contact support]
`max_postgres_connections` | `1000` | The maximum number of PostgreSQL connections in the region, across all schemas. | [Contact support]
`max_query_result_size` | `1073741824` | The maximum size in bytes for a single query's result. | Yes
`max_replicas_per_cluster` | `5` | The maximum number of replicas of a single cluster | [Contact support]
`max_result_size` | `1 GiB` | The maximum size in bytes for a single query's result. | [Contact support]
`max_roles` | `1000` | The maximum number of roles in the region. | [Contact support]
`max_schemas_per_database` | `1000` | The maximum number of schemas in a database. | [Contact support]
`max_secrets` | `100` | The maximum number of secrets in the region, across all schemas. | [Contact support]
`max_sinks` | `1000` | The maximum number of sinks in the region, across all schemas. | [Contact support]
`max_sources` | `25` | The maximum number of sources in the region, across all schemas. | [Contact support]
`max_tables` | `200` | The maximum number of tables in the region, across all schemas | [Contact support]
`mz_version` | Version-dependent | Shows the Materialize server version. | No
`network_policy` | `default` | The default network policy for the region. | Yes
`real_time_recency` | `false` | Boolean flag indicating whether [real-time recency](/get-started/isolation-level/#real-time-recency) is enabled for the current session. | [Contact support]
`real_time_recency_timeout` | `10s` | Sets the maximum allowed duration of `SELECT` statements that actively use [real-time recency](/get-started/isolation-level/#real-time-recency). If this value is specified without units, it is taken as milliseconds (`ms`). | Yes
`server_version_num` | Version-dependent | The PostgreSQL compatible server version as an integer. | No
`server_version` | Version-dependent | The PostgreSQL compatible server version. | No
`sql_safe_updates` | `false` | Boolean flag indicating whether to prohibit SQL statements that may be overly destructive. | Yes
`standard_conforming_strings` | `true` | Boolean flag indicating whether ordinary string literals (`'...'`) should treat backslashes literally. The only supported value is `true`. | Yes
`statement_timeout` | `10s` | The maximum allowed duration of the read portion of write operations; i.e., the `SELECT` portion of `INSERT INTO ... (SELECT ...)`; the `WHERE` portion of `UPDATE ... WHERE ...` and `DELETE FROM ... WHERE ...`. If this value is specified without units, it is taken as milliseconds (`ms`). | Yes
`timezone` | `UTC` | The time zone for displaying and interpreting timestamps. The only supported value is `UTC`. | Yes
[Contact support]: /support
## Privileges
The privileges required to execute this statement are:
- [_Superuser_ privileges](/security/cloud/users-service-accounts/#organization-roles)
## Related pages
- [`SHOW`](../show)
- [`ALTER SYSTEM SET`](../alter-system-set)
---
## ALTER SYSTEM SET
Use `ALTER SYSTEM SET` to globally modify the value of a configuration parameter.
To see the current value of a configuration parameter, use [`SHOW`](../show).
## Syntax
```mzsql
ALTER SYSTEM SET [TO|=]
```
Syntax element | Description
---------------|------------
`` | The name of the configuration parameter to modify.
`` | The value to assign to the configuration parameter.
**DEFAULT** | Reset the configuration parameter's default value. Equivalent to [`ALTER SYSTEM RESET`](../alter-system-reset).
### Key configuration parameters
Name | Default value | Description | Modifiable?
--------------------------------------------|---------------------------|-----------------------------------------------------------------------|--------------
`cluster` | `quickstart` | The current cluster. | Yes
`cluster_replica` | | The target cluster replica for `SELECT` queries. | Yes
`database` | `materialize` | The current database. | Yes
`search_path` | `public` | The schema search order for names that are not schema-qualified. | Yes
`transaction_isolation` | `strict serializable` | The transaction isolation level. For more information, see [Consistency guarantees](/overview/isolation-level/).
Accepts values: `serializable`, `strict serializable`. | Yes
### Other configuration parameters
Name | Default value | Description | Modifiable?
--------------------------------------------|---------------------------|------------------------------------------------------------------------------------------------------------------------------------------------------------------------|----------------------------------------------------------------
`allowed_cluster_replica_sizes` | *Varies* | The allowed sizes when creating a new cluster replica. | [Contact support]
`application_name` | | The application name to be reported in statistics and logs. This parameter is typically set by an application upon connection to Materialize (e.g. `psql`). | Yes
`auto_route_catalog_queries` | `true` | Boolean flag indicating whether to force queries that depend only on system tables to run on the `mz_catalog_server` cluster for improved performance. | Yes
`client_encoding` | `UTF8` | The client's character set encoding. The only supported value is `UTF-8`. | Yes
`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. | Yes
`datestyle` | `ISO, MDY` | The display format for date and time values. The only supported value is `ISO, MDY`. | Yes
`emit_introspection_query_notice` | `true` | Whether to print a notice when querying replica introspection relations. | Yes
`emit_timestamp_notice` | `false` | Boolean flag indicating whether to send a `notice` specifying query timestamps. | Yes
`emit_trace_id_notice` | `false` | Boolean flag indicating whether to send a `notice` specifying the trace ID, when available. | Yes
`enable_rbac_checks` | `true` | Boolean flag indicating whether to apply RBAC checks before executing statements. | Yes
`enable_session_rbac_checks` | `false` | Boolean flag indicating whether RBAC is enabled for the current session. | No
`extra_float_digits` | `3` | Boolean flag indicating whether to adjust the number of digits displayed for floating-point values. | Yes
`failpoints` | | Allows failpoints to be dynamically activated. | No
`idle_in_transaction_session_timeout` | `120s` | 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 (`ms`). A value of zero disables the timeout. | Yes
`integer_datetimes` | `true` | Boolean flag indicating whether the server uses 64-bit-integer dates and times. | No
`intervalstyle` | `postgres` | The display format for interval values. The only supported value is `postgres`. | Yes
`is_superuser` | | Reports whether the current session is a _superuser_ with admin privileges. | No
`max_aws_privatelink_connections` | `0` | The maximum number of AWS PrivateLink connections in the region, across all schemas. | [Contact support]
`max_clusters` | `10` | The maximum number of clusters in the region | [Contact support]
`max_connections` | `5000` | The maximum number of concurrent connections in the region | [Contact support]
`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. | [Contact support]
`max_databases` | `1000` | The maximum number of databases in the region. | [Contact support]
`max_identifier_length` | `255` | The maximum length in bytes of object identifiers. | No
`max_kafka_connections` | `1000` | The maximum number of Kafka connections in the region, across all schemas. | [Contact support]
`max_mysql_connections` | `1000` | The maximum number of MySQL connections in the region, across all schemas. | [Contact support]
`max_objects_per_schema` | `1000` | The maximum number of objects in a schema. | [Contact support]
`max_postgres_connections` | `1000` | The maximum number of PostgreSQL connections in the region, across all schemas. | [Contact support]
`max_query_result_size` | `1073741824` | The maximum size in bytes for a single query's result. | Yes
`max_replicas_per_cluster` | `5` | The maximum number of replicas of a single cluster | [Contact support]
`max_result_size` | `1 GiB` | The maximum size in bytes for a single query's result. | [Contact support]
`max_roles` | `1000` | The maximum number of roles in the region. | [Contact support]
`max_schemas_per_database` | `1000` | The maximum number of schemas in a database. | [Contact support]
`max_secrets` | `100` | The maximum number of secrets in the region, across all schemas. | [Contact support]
`max_sinks` | `1000` | The maximum number of sinks in the region, across all schemas. | [Contact support]
`max_sources` | `25` | The maximum number of sources in the region, across all schemas. | [Contact support]
`max_tables` | `200` | The maximum number of tables in the region, across all schemas | [Contact support]
`mz_version` | Version-dependent | Shows the Materialize server version. | No
`network_policy` | `default` | The default network policy for the region. | Yes
`real_time_recency` | `false` | Boolean flag indicating whether [real-time recency](/get-started/isolation-level/#real-time-recency) is enabled for the current session. | [Contact support]
`real_time_recency_timeout` | `10s` | Sets the maximum allowed duration of `SELECT` statements that actively use [real-time recency](/get-started/isolation-level/#real-time-recency). If this value is specified without units, it is taken as milliseconds (`ms`). | Yes
`server_version_num` | Version-dependent | The PostgreSQL compatible server version as an integer. | No
`server_version` | Version-dependent | The PostgreSQL compatible server version. | No
`sql_safe_updates` | `false` | Boolean flag indicating whether to prohibit SQL statements that may be overly destructive. | Yes
`standard_conforming_strings` | `true` | Boolean flag indicating whether ordinary string literals (`'...'`) should treat backslashes literally. The only supported value is `true`. | Yes
`statement_timeout` | `10s` | The maximum allowed duration of the read portion of write operations; i.e., the `SELECT` portion of `INSERT INTO ... (SELECT ...)`; the `WHERE` portion of `UPDATE ... WHERE ...` and `DELETE FROM ... WHERE ...`. If this value is specified without units, it is taken as milliseconds (`ms`). | Yes
`timezone` | `UTC` | The time zone for displaying and interpreting timestamps. The only supported value is `UTC`. | Yes
[Contact support]: /support
## Privileges
The privileges required to execute this statement are:
- [_Superuser_ privileges](/security/cloud/users-service-accounts/#organization-roles)
## Related pages
- [`ALTER SYSTEM RESET`](../alter-system-reset)
- [`SHOW`](../show)
---
## ALTER TABLE
Use `ALTER TABLE` to:
- Rename a table.
- Change owner of a table.
- Change retain history configuration for the table.
## Syntax
**Rename:**
### Rename
To rename a table:
```mzsql
ALTER TABLE RENAME TO ;
```
| Syntax element | Description |
| --- | --- |
| `` | The current name of the table you want to alter. |
| `` | The new name of the table. |
See also [Renaming restrictions](/sql/identifiers/#renaming-restrictions).
**Change owner:**
### Change owner
To change the owner of a table:
```mzsql
ALTER TABLE OWNER TO ;
```
| Syntax element | Description |
| --- | --- |
| `` | The name of the table you want to change ownership of. |
| `` | The new owner of the table. |
To change the owner of a table, you must be the owner of the table and have
membership in the ``. See also [Privileges](#privileges).
**(Re)Set retain history config:**
### (Re)Set retain history config
To set the retention history for a user-populated table:
```mzsql
ALTER TABLE SET (RETAIN HISTORY [=] FOR );
```
| Syntax element | Description |
| --- | --- |
| `` | The name of the table you want to alter. |
| `` | ***Private preview.** This option has known performance or stability issues and is under active development.* Duration for which Materialize retains historical data, which is useful to implement [durable subscriptions](/transform-data/patterns/durable-subscriptions/#history-retention-period). Accepts positive [interval](/sql/types/interval/) values (e.g. `'1hr'`). Default: `1s`. |
To reset the retention history to the default for a user-populated table:
```mzsql
ALTER TABLE RESET (RETAIN HISTORY);
```
| Syntax element | Description |
| --- | --- |
| `