| # | Name | Goal | Type ▲ | Data source | Last update | License | |
|---|---|---|---|---|---|---|---|
| 421 | Exclude constraint instead of simple UNIQUE | Find exclude constraints that implement a simple UNIQUE constraint. The checking might be slower compared to UNIQUE constraint. | Problem detection | system catalog base tables only | 2025-11-07 10:11 | MIT License | View |
| 422 | Explicit locking is missing | This query identifies concurrency risks in user-defined routines by flagging INSERT, UPDATE, or DELETE statements that utilize subqueries without a FOR SHARE locking clause. Failure to acquire a shared lock on source rows allows concurrent transactions to modify or delete them between the subquery's execution and the outer operation, potentially leading to data inconsistencies. Routines utilizing the xmin system column are excluded, operating on the assumption that they implement Optimistic Concurrency Control (version checking) and therefore do not require pessimistic locking. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-12-15 12:45 | MIT License | View |
| 423 | Explicit locking is missing (2) (ChatGPT version) | This query identifies concurrency risks in user-defined routines by flagging INSERT, UPDATE, or DELETE statements that utilize subqueries without a FOR SHARE locking clause. Failure to acquire a shared lock on source rows allows concurrent transactions to modify or delete them between the subquery's execution and the outer operation, potentially leading to data inconsistencies. Routines utilizing the xmin system column are excluded, operating on the assumption that they implement Optimistic Concurrency Control (version checking) and therefore do not require pessimistic locking. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-12-15 12:41 | MIT License | View |
| 424 | Extension routines in the schema "public" | Find extensions that routines are in the schema public. | Problem detection | system catalog base tables only | 2025-11-07 10:11 | MIT License | View |
| 425 | Extension routines that execution privilege has been granted to PUBLIC | Know the privileges that users have in your system. Probably all the database users do not need these privileges. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-07 10:11 | MIT License | View |
| 426 | Extreme contraction | Find names that contain extremely short terms, due to an excessive word contraction, abbreviation, or acronym usage. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-07 10:11 | MIT License | View |
| 427 | Field size is not sufficiently different in case of base table columns | Find base table columns that name refers to the possibility that these are used to register names. Find base table columns that name refers to the possibility that these are used to register comments/descriptions/etc. Find the cases where a base table contains columns from the both sets and the field size in case of the latter is not at least twice as big as in case of the former. For example, if the name of a service can be 100 character long, then it is quite illogical that the description of the service is only at most 200 characters long. | Problem detection | INFORMATION_SCHEMA only | 2025-11-07 10:11 | MIT License | View |
| 428 | FILLFACTOR is probably too big | This query identifies base tables with a potentially suboptimal FILLFACTOR setting, targeting those that likely undergo UPDATE operations. A high FILLFACTOR on such tables can lead to poor performance by reducing the likelihood of Heap-Only Tuple (HOT) updates. When a new row version cannot be stored on the same page as the old version, all indexes on the table must be updated with the new tuple's location. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-10 09:08 | MIT License | View |
| 429 | FILLFACTOR is probably too small | This query identifies base tables with a FILLFACTOR setting below 90, flagging them for potentially inefficient storage utilization. While a low FILLFACTOR is intended to accommodate UPDATEs, an excessively low value can lead to wasted disk space and reduced data density, negatively impacting the performance of operations like full table scans. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-10 09:10 | MIT License | View |
| 430 | FILLFACTOR is probably too small (2) | This query identifies base tables with a suboptimal FILLFACTOR setting, specifically targeting tables that are heuristically identified as junction tables in a many-to-many relationship. A table is considered a probable junction table if it primarily consists of foreign and primary key columns, with no additional data columns that would suggest UPDATE operations. For these tables, the workload is almost exclusively INSERT and DELETE. Therefore, a FILLFACTOR below 100 serves no performance benefit and results only in wasted storage space and reduced data density. The job of these linking tables is simple: you add a line to connect two things, or you remove the line to disconnect them. You almost never change a line that's already there. The fillfactor setting is used to leave empty space for changes. |
Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-10 09:13 | MIT License | View |
| 431 | Find columns that require new lookup tables (based on names and field sizes) | This query detects data attributes that function as implicit classifiers but lack a corresponding reference entity. It targets textual columns that are not currently constrained (no PK, UK, or FK) but exhibit characteristics of coded data: they either have a very short length (≤ 3 characters) or possess identifiers typical of classifiers (e.g., 'status', 'type'). To reduce false positives, it excludes obvious free-text fields (names, comments) and verifies that no table with a similar name currently exists. This suggests the need to extract these attributes into a new dedicated reference table to enforce domain integrity. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-12-30 09:14 | MIT License | View |
| 432 | Find inconsistency in double underscore/space usage | This query audits the database schema for inconsistency regarding the use of consecutive separators (double underscores or spaces) within identifiers. It groups objects by type (e.g., CHECK constraints, indexes) and identifies categories where a mixed naming convention exists—specifically, where some identifiers utilize consecutive separators (e.g., idx__name) while others of the same type do not (e.g., idx_name). This variation suggests a lack of enforced coding standards, leading to unpredictability in the schema. The query facilitates a review to establish and enforce a single, uniform naming convention. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-20 13:08 | MIT License | View |
| 433 | Find mixed usage of \w and [[:alnum:]] regex syntax | This query audits regular expressions within the database to detect inconsistent syntax for matching alphanumeric characters. It checks for the concurrent use of both the Perl-style shorthand \w and the POSIX character class [[:alnum:]]. While these are often functionally similar, their exact behavior can differ based on locale settings (e.g., \w may include underscores while [[:alnum:]] does not). Using both styles within the same codebase indicates a lack of a clear standard, which can lead to maintainability issues and subtle, locale-dependent bugs. Standardizing on a single, well-understood syntax is recommended for clarity and predictability. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-15 11:11 | MIT License | View |
| 434 | Find || operations missing coalesce() protection | This query identifies potential null-propagation defects in user-defined routines and views. It targets subqueries utilizing the standard concatenation operator (||) where operands are not protected by a coalesce() function. In PostgreSQL, the operation string || NULL yields NULL, causing the entire result to vanish if any component is missing. This behavior is often unintentional. The query flags these risky patterns, suggesting remediation via explicit null handling or the adoption of null-safe alternatives like concat(), concat_ws(), or format(). | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-27 11:50 | MIT License | View |
| 435 | Find pointless regular expressions that match any value | This query identifies regular expressions that are tautological, specifically those that effectively match any non-NULL string, such as ^.*$. It is superfluous because it evaluates to TRUE for any non-NULL value, including an empty string. It provides no actual data validation and acts as a no-operation (no-op) check. Such patterns are often artifacts of placeholder code, incomplete logic, or a fundamental misunderstanding of regular expression syntax. Removing them improves clarity and eliminates a useless computational step. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-15 11:16 | MIT License | View |
| 436 | Find useless coalesce, concat, or concat_ws calls with only one argument | This query identifies superfluous function calls within routines and views, specifically targeting invocations of coalesce(), concat(), or concat_ws() that are supplied with only a single argument. These functions are variadic and designed to operate on multiple values (e.g., returning the first non-null value or joining strings). When called with a single argument, they function as an identity operation, returning the input unchanged. This pattern indicates either a coding error (missing arguments) or redundant logic that should be removed to simplify the expression. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-12-30 13:51 | MIT License | View |
| 437 | Flag parameters | A Boolean parameter may be used to determine what task to fulfill. In this case the routine has multiple tasks and does not satisfy the separation of concerns principle. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-07 10:11 | MIT License | View |
| 438 | Foreign key column has a default value that is not present in the parent table | Find foreign key columns that have a default value that is not present in the parent table. Identify default values that cause violations of the referential constraints. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-07 10:11 | MIT License | View |
| 439 | Foreign key column has a simple check constraint that is attached directly to the table | Find foreign key columns that are covered by a simple check constraint, i.e., the constraint involves only one column. Look only constraints that are directly associated with the table, i.e., are not specified through a domain. Perhaps the constraint should be defined on the referenced candidate key column. | Problem detection | system catalog base tables only | 2025-11-07 10:11 | MIT License | View |
| 440 | Foreign key columns that are associated with a sequence generator | Find foreign key columns that are associated with a sequence generator. Foreign key values are selected amongst the values that are registered as corresponding primary key/unique key values. Values in the foreign key columns are not directly generated by the system. These values might be system generated indirectly - generated when a row is added to the primary (parent) table. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-07 10:11 | MIT License | View |