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

0

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

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?

asked a year ago235 views
1 Answer
0

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.

AWS
Simmi_K
answered a year ago

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