Possible bug with Materialized View refreshes in Redshift

Question

Hi,

We are experiencing a bug where materialized views are pulling in 'old' data that does not match the query syntax after refreshing on a schedule for ~4 months.

**Context**

We have a set of materialized views that pull the data from the past 24 hours from a subset of our data in Redshift.

A part of the materialized view specifies to only pull data from the past 24 hours, which means that when the view refreshes, it needs to recompute from scratch instead of incrementally update.

Here is the syntax for our where statement (this is the same in all the MV's)

`where
    sl.interval_start >= current_timestamp - ' 24 hour':: interval`

We noticed recently that some of these materialized views were taking much longer to refresh than normally and had records from over 3 months ago. We dropped the MVs and rebuilt them with the same script & they appear to be working well now, but reporting this in case AWS folks know of a persisting issue.

meyerovb · Answer

Change `where sl.interval_start >= current_timestamp - ' 24 hour':: interval`

To `where sl.interval_start >= get_current_timestamp() - ' 24 hour':: interval`

And make a VOLATILE function get_current_timestamp

That should guarantee it doesn't like cache the value, which I guess was what happened...

MilindOke · Answer

Firstly thats for using this platform for sharing your findings. This can help others if they face a similar issue.

I request you to open a support ticket to have this investigated and root cause determined.

Possible bug with Materialized View refreshes in Redshift

Relevant content