Query goal: | Find derived tables that subquery uses FOR UPDATE construct. Reading a data element shouldn't block other read operations of the same element. Thus, you shouldn't use exclusive locking command in a view. |
Notes about the query: | 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: | High (Few or no false-positive results) |
Query license: | MIT License |
Fixing suggestion: | Drop the view and recreate it without using FOR UPDATE in the subquery. |
Data source: | INFORMATION_SCHEMA+system catalog |
SQL query: | Click on query to copy it
SELECT table_schema, table_name, type, view_definition FROM (SELECT views.table_schema, views.table_name, 'VIEW' AS type, views.view_definition FROM information_schema.views WHERE 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) UNION SELECT schemaname, matviewname, 'MATERIALIZED VIEW' AS type, regexp_replace(definition,'[\r\n]','<br>','g') AS definition FROM pg_catalog.pg_matviews) AS foo WHERE view_definition ~*'[[:space:]]+for[[:space:]]+update' ORDER BY table_schema, table_name; |
SQL query | Description |
---|---|
SELECT format('DROP VIEW %1$I.%2$I;', views.table_schema, views.table_name) AS statements FROM information_schema.views WHERE 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) AND view_definition ~*'^.+[[:space:]]+for[[:space:]]+update.*$' ORDER BY table_schema, table_name; | Drop the view. |
SELECT format('DROP MATERIALIZED VIEW %1$I.%2$I;', schemaname, matviewname) AS statements FROM pg_catalog.pg_matviews WHERE definition ~*'^.+[[:space:]]+for[[:space:]]+update.*$' ORDER BY schemaname, matviewname; | Drop the materialized view. |
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 |
---|---|
Concurrency control | Queries of this category provide information about concurrency control. |
Derived tables | Queries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer. |
Performance | Queries of this category provide information about indexes in a database. |
Reference |
---|
https://devcenter.heroku.com/articles/postgresql-concurrency |
https://dba.stackexchange.com/questions/189240/how-does-select-for-update-behave-with-views-in-postgresql |