How can I drop a RedShift AutoMV?

Question

I'm trying to alter a table definition, but it's dependent on by a materialized view which is managed by RedShift Auto Materialization and not by us. So attempting

```sql
ALTER TABLE my_table
    ALTER COLUMN my_column TYPE VARCHAR(100);
```

Errors with

> [0A000] ERROR: cannot alter type of a column used by a materialized view

So I attempt to drop the materialized view

```sql
DROP MATERIALIZED VIEW pg_automv.auto_mv_53022635;
```

Which gives a permission error

> [42501] ERROR: permission denied for schema pg_automv

What's the solution here?

Accepted Answer

**Hi, I opened a ticket to AWS and this is their answer :/**

Dear Customer,
From case notes i understand that while trying to alter varchar column of table , you are facing below error due to the Redshift auto_mv feature. Please correct me if I have misunderstood or missed addressing any of your concerns.

Error Amazon 500310 Invalid operation: cannot alter type of a column used by a materialized view.

In this regards, I would like to provide the information as below :

The current workaround is to disable the auto_mv parameter and reboot the cluster. This will drop all existing auto materialized views and will not create any going forward unless you reset the parameter group to its default.

According to documentation[1], the AutoMV feature is fully system managed. Therefore, unlike manual materialized views, AutoMVs are not visible to users and can’t be queried directly. They also don’t appear in any system tables like stv_mv_info or svl_mv_refresh_status. Finally, if the AutoMV hasn’t been used for some time by the workload, it’s automatically dropped and the storage released.

With that being said, it's unlikely to manually find all of the AutoMV and its dependencies, and eventually to drop them. 
 
The Redshift AutoMV feature can be turned off by updating your Redshift cluster parameter group “auto_mv" parameter to "false".[2]

I understand that this may cause a slight inconvenience to you and I again sincerely apologies on behalf of AWS for the same.

How can I drop a RedShift AutoMV?

Relevant content