Query goal: | Find derived tables (views, materialized views) that are based on more than five tables and that have more than 15 columns. This view might produce "a denormalized world view" where all the data is together in one table and applications make queries based on this single view to fulfill their specific tasks. Such view does not follow the separation of concerns principle. |
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 body 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. In the list of table columns the separator is 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: | Replace a big view with multiple smaller views, each of which has more specific tasks. |
Data source: | INFORMATION_SCHEMA+system catalog |
SQL query: | Click on query to copy it
WITH dependencies AS (SELECT DISTINCT nt.nspname AS table_schema, d.refobjid::regclass::text AS table_name, CASE WHEN c.relkind='r' THEN 'BASE TABLE' WHEN c.relkind='f' THEN 'FOREIGN TABLE' WHEN c.relkind='v' THEN 'VIEW' ELSE 'MATERIALIZED VIEW' END AS table_type, nv.nspname AS dependent_table_schema, v.oid::regclass::text AS dependent_table_name, CASE WHEN v.relkind='v' THEN 'VIEW' ELSE 'MATERIALIZED VIEW' END AS dependent_table_type, v.relnatts AS number_of_dependent_table_columns, regexp_replace(pg_get_viewdef(v.oid),'[\r\n]','<br>','g') AS dependent_view_def 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 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 dependent_table_schema, dependent_table_name, dependent_table_type, number_of_dependent_table_columns, dependent_view_def, Count(*) AS number_of_underlying_tables, string_agg(table_schema || '.' || table_name || '(' || table_type || ')', ';<br>' ORDER BY table_schema, table_name) AS underlying_tables FROM dependencies WHERE number_of_dependent_table_columns>15 GROUP BY dependent_table_schema, dependent_table_name, dependent_table_type, number_of_dependent_table_columns, dependent_view_def HAVING Count(*)>5 ORDER BY Count(*) DESC, number_of_dependent_table_columns DESC, dependent_table_schema, dependent_table_name; |
Category name | Category description |
---|---|
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/ |
https://en.wikipedia.org/wiki/Separation_of_concerns |
Rule 3 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). |
Smell "God table": Sharma, T., Fragkoulis, M., Rizou, S., Bruntink, M. and Spinellis, D.: Smelly relations: measuring and understanding database schema quality. In: Proceedings of the 40th International Conference on Software Engineering: Software Engineering in Practice, pp. 55-64. ACM, (2018). |