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, 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".
I understand that this may cause a slight inconvenience to you and I again sincerely apologies on behalf of AWS for the same.
Redshift Data API not recognized by Boto3Accepted Answerasked 2 years ago
Can Redshift authenticate to AWS Managed AD and how?Accepted Answerasked 2 years ago
How do I get a Redshift Auqa cluster to stop pausing and actually pause?asked 3 months ago
How can I drop a RedShift AutoMV?Accepted Answerasked 22 days ago
Can you use a Redshift Spectrum Table in a CTE?asked 6 months ago
Setting up refresh schedules for Amazon Redshift materialized viewAccepted Answerasked 2 years ago
Redshift Automatic Table Optimization and table swapsAccepted Answerasked 2 years ago
Are redshift auto materialized views schema bound?asked 6 days ago
Redshift Managed Storage vs SpectrumAccepted Answerasked 2 years ago
How do I solve a "Failed" message on a Redshift Serverless workgroup?asked a month ago