Redshift Editor V2 doesn't script materialized view with autorefresh YES

Question

I have a materialized view with autorefresh set to on. When I script it out, it doesn't show the argument `AUTO REFRESH YES`.

select autorefresh,*
from STV_MV_INFO;

![Enter image description here](/media/postImages/original/IMONWe0B69SXyyHUx4NZwOZQ)

It shows this when i right click on the proc and click view definition:

`CREATE MATERIALIZED VIEW globaldata.vwdeviceplatform
AS 
select xyz..`

it should show this:

`CREATE MATERIALIZED VIEW globaldata.vwdeviceplatform
AUTO REFRESH YES
as 
select xyz...`

Also strangely, getting the viewdef through here also doesn't script out the autorefresh portion
SELECT pg_catalog.pg_get_viewdef('globaldata.vwdeviceplatform'::regclass::oid, true);
;

I'm signed to AWS Editor V2 via Okta and I'm using a role, as opposed to a direct redshift login/pw. Is this just a limitation?

Answer

Hello,

In this context, please note that STV_MV_INFO table contains DDL used while creating the materialized view.

If the materialized view is created with below command:

—> CREATE MATERIALIZED VIEW globaldata.vwdeviceplatform AS select xyz..

After creating the mv we can turn on AUTO REFRESH Using  -

—> ALTER MATERIALIZED VIEW globaldata.vwdeviceplatform  AUTO REFRESH YES;

[+] https://docs.aws.amazon.com/redshift/latest/dg/r_ALTER_MATERIALIZED_VIEW.html

Now even if autorefresh=’t’ in STV_MV_INFO table, the view definition will be same as below:

CREATE MATERIALIZED VIEW globaldata.vwdeviceplatform AS select xyz..

Same goes with pg_get_viewdef function. So, in both the cases you will get the DDL used while creating the mv only.

That being said, if you would like resource based troubleshooting, please raise a support case with AWS for further information. If a support case has already been created please be assured that we will get back to you and assist you in the best way possible.

Redshift Editor V2 doesn't script materialized view with autorefresh YES

Relevanter Inhalt