By using AWS re:Post, you agree to the Terms of Use

How can I drop a RedShift AutoMV?

0

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

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

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?

asked 22 days ago80 views
1 Answers
0
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.

answered 17 days ago
  • Ooof. So the only practical way to do schema migrations is to turn off AutoMV, reboot and hope we don't trash the DB load that has been relying on AutoMV. That.. is harsh. Thanks for answering.

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions