CODEWITHSUNDEEP
viewamazon-redshift
ScottieB
ScottieB

Reputation: 4052

Redshift View keeps reverting to previous definition

I created a view in Redshift that unions two queries, and it works great. We've thought of a third query that would be worthwhile to add in. eg

CREATE VIEW stem_alumni as
SELECT name, email
FROM students
WHERE graduated < 2019 AND major = 'Engineering'

UNION 
SELECT name, email
FROM alumni
WHERE current_employer = 'Google'

The problem is when I try to add a third query in 

UNION
SELECT name, email
FROM professors
WHERE department = 'Engineering'

it'll persist for maybe an hour, but then revert to just the original query.

I've run CREATE OR REPLACE VIEW... and dropping/recreate and get the same result.

How do I get an updated view definition to persist?

Adding more context

I created the view using DBeaver, a local SQL client using my specific Redshift credentials. The view is called by Periscope, our cloud-based BI tool using shared credentials. Querying the view in Periscope or separate DBeaver windows will eventually revert the view to its original definition.

Upvotes: 0

Views: 825

Answers (1)

John Stark
John Stark

Reputation: 1297

Redshift shouldn't have a 'memory' of the view's prior DDL that it could revert to. I'm inclined to agree with the comments that something else is overwriting the updates to the view's DDL after you have committed them.

You should be able to see if something is overwriting the view, by querying the stl_query table:

SELECT q.starttime
     , u.usename
     , q.querytxt
FROM pg_user u
  JOIN stl_query q ON u.usesysid = q.userid
WHERE POSITION('<view_name>' IN q.querytxt) > 0
ORDER BY q.starttime DESC
;

This table only contains recent query information (2-5 days according to the Redshift Documentation), so if you haven't experienced this behavior from the view within that timescale, you may need to force it to occur again in order to troubleshoot who/what is altering the DDL.

Additionally, if the view is being overwritten by a user other than yourself, you will need to query stl_query using a super user account (by default, non-super users will only be able to view information for queries that they themselves have executed).

Upvotes: 1

Related Questions