- Newest
- Most votes
- Most comments
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.
Relevant content
- asked 2 years ago
- AWS OFFICIALUpdated 2 years ago
- AWS OFFICIALUpdated a year ago
- AWS OFFICIALUpdated 2 years 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.