Check for Invalid Views

Vertica does not prevent you from creating so-called invalid views by accident. For example:

CREATE TABLE test_table(x int, y int);
CREATE VIEW test_view(x) AS SELECT x FROM test_table;

-- The following operation is going to succeed in spite of the fact that "test_view" depends on "test_table":
DROP TABLE test_table;

-- "test_view" is now invalid. Running the following query is going to fail:
SELECT * FROM test_view;

Invalid views in your Data Warehouse instance makes it impossible for GoodData to fully operate the instance. Therefore, check for and fix invalid views regularly.

Steps:

1. Run the following query to determine first-level invalid views in your Data Warehouse instance:

   SELECT * FROM view_tables WHERE reference_table_id IS NULL AND table_schema != 'catalog'
   

   The query returns the views that have a direct dependency on a non-existent table.

2. Fix those views by either dropping them or pointing them to existing tables.

3. Run the same query again to determine any transitive dependencies, and fix them.

4. Keep running the query and fixing invalid views until the returned list is empty.