Query goal: | Find non-updatable views that have a DO INSTEAD NOTHING rule. The rule is used to prevent updates. However, the view is aniway non-updatable. |
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: | Drop the rules. |
Data source: | INFORMATION_SCHEMA+system catalog |
SQL query: | Click on query to copy it
WITH non_updatable_views AS (SELECT table_schema AS schema, table_name AS view FROM Information_schema.views WHERE (view_definition~'(JOIN|GROUP BY|HAVING|LIMIT|OFFSET|FETCH FIRST|DISTINCT|WITH|UNION|INTERSECT|EXCEPT)' OR view_definition~*'(sum|count|avg|min|max|over)[[:space:]]{0,1}[(]') AND table_schema NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL)) SELECT schemaname, tablename, rulename, definition FROM pg_rules WHERE definition~'ON[[:space:]](INSERT|UPDATE|DELETE).*DO[[:space:]]INSTEAD[[:space:]]NOTHING' AND (schemaname, tablename) IN (SELECT schema, view FROM non_updatable_views) ORDER BY schemaname, tablename, rulename; |
SQL query | Description |
---|---|
WITH non_updatable_views AS (SELECT table_schema AS schema, table_name AS view FROM Information_schema.views WHERE (view_definition~'(JOIN|GROUP BY|HAVING|LIMIT|OFFSET|FETCH FIRST|DISTINCT|WITH|UNION|INTERSECT|EXCEPT)' OR view_definition~*'(sum|count|avg|min|max|over)[[:space:]]{0,1}[(]') AND table_schema NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL)) SELECT format('DROP RULE %1$I ON %2$I.%3$I;', rulename, schemaname, tablename) AS statements FROM pg_rules WHERE definition~'ON[[:space:]](INSERT|UPDATE|DELETE).*DO[[:space:]]INSTEAD[[:space:]]NOTHING' AND (schemaname, tablename) IN (SELECT schema, view FROM non_updatable_views) ORDER BY schemaname, tablename, rulename; | Drop the rules. |
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 |
---|---|
Derived tables | Queries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer. |
Triggers and rules | Queries of this category provide information about triggers and rules in a database. |