Query goal: | Do not build multiple levels of derived tables (views and materialized views) because it will hamper evolvability and understandability of the tables. |
Notes about the query: | The query uses a fragment of a query from: https://www.cybertec-postgresql.com/en/tracking-view-dependencies-in-postgresql/. In the returned subquery of view/materialized view the query replaces each newline character with the line break (br) tag for the better readability in case the query result is displayed in a web browser. |
Query type: | Problem detection (Each row in the result could represent a flaw in the design) |
Query reliability: | Medium (Medium number of false-positive results) |
Query license: | MIT License |
Fixing suggestion: | Instead of creating a derived table (view, materialized view) on top of another derived table create it on top of base tables. |
Data source: | INFORMATION_SCHEMA+system catalog |
SQL query: | Click on query to copy it
WITH view_on_view AS (SELECT DISTINCT nt.nspname AS view_schema, d.refobjid::regclass::text AS view_name, CASE WHEN c.relkind='v' THEN 'VIEW' ELSE 'MATERIALIZED VIEW' END AS view_type, nv.nspname AS dependent_view_schema, v.oid::regclass::text AS dependent_view_name, v.oid AS dependent_view_oid, CASE WHEN v.relkind='v' THEN 'VIEW' ELSE 'MATERIALIZED VIEW' END AS dependent_view_type FROM pg_attribute AS a JOIN pg_depend AS d ON d.refobjsubid = a.attnum AND d.refobjid = a.attrelid JOIN pg_rewrite AS r ON r.oid = d.objid JOIN pg_class AS v ON v.oid = r.ev_class JOIN pg_namespace AS nv ON nv.oid=v.relnamespace JOIN pg_class AS c ON c.oid=d.refobjid JOIN pg_namespace AS nt ON nt.oid=c.relnamespace WHERE v.relkind IN ('v','m') AND c.relkind IN ('v','m') AND d.classid = 'pg_rewrite'::regclass AND d.refclassid = 'pg_class'::regclass AND d.deptype = 'n' AND a.attisdropped='f' AND nt.nspname NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL) AND nv.nspname NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL)) SELECT view_schema, view_name, view_type, dependent_view_schema, dependent_view_name, dependent_view_type, regexp_replace(pg_get_viewdef(dependent_view_oid),'[\r\n]','<br>','g') AS dependent_view_def FROM view_on_view ORDER BY view_schema, view_name, dependent_view_schema, dependent_view_name; |
Collection name | Collection description |
---|---|
Find problems automatically | Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not . |
Category name | Category description |
---|---|
Comfortability of database evolution | Queries of this category provide information about the means that influence database evolution. |
Derived tables | Queries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer. |
Reference |
---|
https://www.cybertec-postgresql.com/en/tracking-view-dependencies-in-postgresql/ |
Rule 9 in: Delplanque, J., Etien, A., Auverlot, O., Mens, T., Anquetil, N., Ducasse, S.: CodeCritics applied to database schema: Challenges and first results. In: 2017 IEEE 24th International Conference on Software Analysis, Evolution and Reengineering (SANER), pp. 432-436. IEEE, (2017). |