| 1041 |
Using unreserved (in PostgreSQL) SQL keywords as the names of a database object (aggregate view) |
"Names in software are 90 percent of what make software readable. You need to take the time to choose them wisely and keep them relevant. Names are too important to treat carelessly. Names should not cause confusion." (Robert C. Martin, Clean Code) Names should not cause confusion. Find the distinct names (identifiers) of user-defined objects that are SQL keywords that are completely unreserved in PostgreSQL. In PostgreSQL "there are several different classes of tokens ranging from those that can never be used as an identifier to those that have absolutely no special status in the parser as compared to an ordinary identifier. " (PostgreSQL manual) Although the names are unreserved keywords in PostgreSQL these could be reserved keywords in other systems, which would complicate database migration. Moreover, such identifiers are often too general, i.e., do not provide enough information about the named object. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 1042 |
Using unreserved (in PostgreSQL) SQL keywords as the names of a database object (detailed view) |
"Names in software are 90 percent of what make software readable. You need to take the time to choose them wisely and keep them relevant. Names are too important to treat carelessly. Names should not cause confusion." (Robert C. Martin, Clean Code) Names should not cause confusion. Find the names (identifiers) of user-defined objects that are SQL keywords that are completely unreserved in PostgreSQL. In PostgreSQL "there are several different classes of tokens ranging from those that can never be used as an identifier to those that have absolutely no special status in the parser as compared to an ordinary identifier. " (PostgreSQL manual) Although the names are unreserved keywords in PostgreSQL these could be reserved keywords in other systems, which would complicate database migration. Moreover, such identifiers are often too general, i.e., do not provide enough information about the named object. Think as to whether some better name would be possible. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 1043 |
Vacuum and analyze status of base tables |
Find for each base table the last time when the table has been vacuumed or analyzed (either manually or automatically). |
General |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 1044 |
Validation method does not confirm |
Find user-defined SQL and PL/pgSQL routines that do not return a value although the name suggest that it should return a value (starts with "validate" or "check"). |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 1045 |
Very similar (but not equal) routine names |
This query audits the schema for semantic ambiguity by identifying pairs of routine names (functions, procedures) that exhibit high textual similarity but are not identical. It filters for name pairs with a Levenshtein edit distance of exactly one (less than two, but excluding equality). This specific filter targets typographical errors (e.g., calc_tax vs. calc_tux) or inconsistent singular/plural naming (e.g., get_user vs. get_users), while correctly ignoring valid method overloading where names are identical. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-25 17:29 |
MIT License |
View |
| 1046 |
Very similar column names |
This query performs an intra-table analysis to detect potential schema ambiguities. It identifies pairs of columns within the same table that exhibit both high textual similarity and structural equivalence. Specifically, it flags pairs where the names have a Levenshtein edit distance of exactly one and the columns share the same data type or domain. This combination suggests a high probability of typographical errors (e.g., status vs statuss), inconsistent naming (singular vs. plural), or improper denormalization (e.g., item1 vs item2), all of which undermine schema clarity. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-25 17:32 |
MIT License |
View |
| 1047 |
Very similar domain names |
This query identifies potential redundancy or ambiguity in the schema by detecting pairs of user-defined domain names with high textual similarity. It utilizes the Levenshtein distance algorithm to find name pairs that differ by fewer than two characters. Crucially, the query implements a filter to exclude pairs where the divergence is attributable solely to numerical digits. This heuristic prevents false positives for valid domain variations based on size or version (e.g., d_name_20 vs. d_name_50), focusing the analysis strictly on likely typographical errors or semantic duplicates. |
Problem detection |
INFORMATION_SCHEMA only |
2025-12-13 12:07 |
MIT License |
View |
| 1048 |
Very similar table names |
This query identifies potential redundancy or ambiguity in the schema by detecting pairs of table names with high textual similarity. It utilizes the Levenshtein distance algorithm to find name pairs that differ by fewer than two characters (i.e., a distance of 0 or 1). This check applies across different types of tables (base tables, foreign tables, derived tables), helping to uncover typographical errors (e.g., users vs user), inconsistent pluralization, or confusingly named entities that violate the principle of distinct and descriptive identifiers. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-25 17:24 |
MIT License |
View |
| 1049 |
Views without security barrier |
Find views that do not have the security barrier option. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 1050 |
Views with security invoker |
Find views that have the security invoker option. Such option is possible starting from PostgreSQL 15. In case of using such views one cannot create a system where data is accessed through views and the users (applications) do not have direct access to the base tables. |
Problem detection |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 1051 |
Views with the WITH LOCAL CHECK OPTION constraint |
Find updatable views that have WITH LOCAL CHECK OPTION constraint. The predicate of a view is the conjunction of the predicates of its (directly and indirectly) underlying tables (both base tables and derived tables) as well as the predicate of the view itself. In case of using WITH LOCAL CHECK OPTION constraint "New rows are only checked against the conditions defined directly in the view itself. Any conditions defined on underlying base views are not checked (unless they also specify the CHECK OPTION)." (PostgreSQL manual) Thus, use instead WITH CASCADED CHECK option to instruct the system to check new rows against the entire predicate of the view. |
Problem detection |
INFORMATION_SCHEMA only |
2025-11-07 10:11 |
MIT License |
View |
| 1052 |
Views with unnecessary security invoker |
Find views with security invoker option that do not have any underlying base table with a security policy. Security invoker option of views is possible starting from PostgreSQL 15. "The main use case (and the one that inspired the feature) is to be able to use views and still check row-level security policies on the underlying tables as the invoker." |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 1053 |
Views with WHERE but without security barrier |
Find views that do not have the security barrier option but restrict rows in some way. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |