| # | Name | Goal | Type | Data source ▲ | Last update | License | |
|---|---|---|---|---|---|---|---|
| 641 | Range lower bound restriction does not consider -infinity | This query identifies RANGE type columns in base tables that have a NOT NULL constraint on their lower bound and an additional CHECK constraint, but this check does not account for -infinity. This may indicate a "magic number" problem, where a fixed lower limit (e.g., '1900-01-01') is used instead of the more explicit and semantically correct unbounded (-infinity) value. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-07 12:03 | MIT License | View |
| 642 | Range upper bound can be NULL | This query identifies columns of base tables that use a RANGE data type but are configured to permit a NULL upper bound. This highlights ranges that can be "unbounded" on their ending side, which may be unintentional and could impact query logic and data constraints. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-07 12:03 | MIT License | View |
| 643 | Range upper bound is not restricted | This query finds range columns of base tables that are missing a safety check on their ending value. It looks for columns where the end of the range can be set to any value, without rules to ensure that value makes sense. This helps ensure that all ranges have proper limits defined for their ending points. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-07 12:03 | MIT License | View |
| 644 | Range upper bound restriction dos not consider infinity | This query identifies RANGE type columns in base tables that have a NOT NULL constraint on their upper bound and an additional CHECK constraint, but this check does not account for infinity. This may indicate a "magic number" problem, where a fixed upper limit (e.g., '2900-01-01') is used instead of the more explicit and semantically correct unbounded (infinity) value. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-07 12:03 | MIT License | View |
| 645 | Recursive triggers potentially causing infinite loops | This query identifies recursive triggers, which occur when a trigger's execution performs an operation (such as an UPDATE on the same table) that immediately causes the same trigger to fire again. This creates a potential infinite loop of execution. While PostgreSQL implements a stack depth limit to detect and terminate such runaway processes to prevent a complete system crash, relying on this fail-safe is poor engineering practice. These triggers consume significant system resources before failure and invariably result in aborted transactions. They should be refactored to avoid self-invocation. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-12-14 12:14 | MIT License | View |
| 646 | Redundant CHECK constraints (logical subsumption or equivalence) (empty strings) | This query identifies superfluous CHECK constraints by detecting logical subsumption. It targets columns where a generic validation ensuring the trimmed string is not empty (e.g., trim(column) <> '') is rendered redundant by a more specific constraint that enforces a minimum length on the trimmed string (e.g., char_length(trim(column)) > 0). Since a string with a positive length is inherently not empty, the generic check adds no functional value and should be removed to simplify the schema. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2026-01-21 09:43 | MIT License | View |
| 647 | Redundant CHECK constraints (logical subsumption or equivalence) (empty strings and strings that consist of whitespace characters) | This query identifies redundant CHECK constraints by detecting cases of logical equivalence or subsumption on a single column. For instance, a constraint like description !~ '^[[:space:]]*$' logically subsumes a less comprehensive constraint such as description <> '', rendering the latter superfluous. Eliminating such duplication is a best practice that improves schema clarity, reduces maintenance overhead, and removes logical noise. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-19 14:54 | MIT License | View |
| 648 | Redundant CHECK constraints (logical subsumption or equivalence) (empty strings and strings that consist of whitespace characters) (2) | This query identifies superfluous CHECK constraints by detecting logical subsumption. It targets columns where a general non-blankness constraint is made redundant by a more specific, format-validating constraint. For instance, if an e_mail column is validated by a format constraint from Set1 (e.g., e_mail LIKE '%@%'), that constraint implicitly ensures the string is not blank. Therefore, any co-existing constraint from Set2 (e.g., e_mail !~ '^[[:space:]]*$') is logically unnecessary and can be removed to reduce schema complexity. Example. Set1: {e_mail~'[[:alnum:]@]+'; position('@' in e_mail)>0; e_mail LIKE '%@%'} Set2: {e_mail~'\S'; e_mail!~'^[[:space:]]*$'; e_mail!~'^\s*$'} If column e_mail has a constraint from Set1, then it does not need a constraint from Set2. |
Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2026-01-21 09:39 | MIT License | View |
| 649 | Redundant DO INSTEAD NOTHING rules on naturally non-updatable views | This query identifies redundant rewrite rules within the database schema. It targets views that are inherently non-updatable (due to the presence of aggregates, joins, or set operations) but are nevertheless defined with a DO INSTEAD NOTHING rule. Since the PostgreSQL engine cannot perform DML operations on such views natively, the view is effectively read-only by definition. Consequently, the explicit rule serves no functional purpose in preventing data modification and represents superfluous schema metadata. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-12-26 09:52 | MIT License | View |
| 650 | Redundant explicit locking in initially deferred constraint triggers | This query identifies INITIALLY DEFERRED constraint triggers that utilize explicit locking mechanisms (e.g., LOCK TABLE, SELECT ... FOR SHARE). Deferred constraints are evaluated at transaction commit time, by which point the database engine automatically manages the necessary data consistency states. Consequently, acquiring manual locks within these triggers is technically redundant. Furthermore, it introduces performance risks by enforcing serialization at the critical end-stage of the transaction, potentially increasing the likelihood of deadlocks and reducing system throughput. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-12-10 13:29 | MIT License | View |
| 651 | Redundant leading or trailing wildcards in regular expressions | This query identifies regular expressions that contain superfluous .* constructs at the beginning or end of the pattern. In standard unanchored regular expression matching (as performed by PostgreSQL's ~ operator), the engine checks for the pattern's existence anywhere within the string by default. Therefore, a leading or trailing .* is typically redundant, as it explicitly instructs the engine to do what it would already be doing implicitly. Removing these unnecessary wildcards improves the readability and maintainability of the expression and can, in some cases, lead to better performance by reducing unnecessary backtracking. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-15 10:04 | MIT License | View |
| 652 | Redundant sequence usage privileges (missing table insert rights) | This query identifies a logical mismatch in access control lists (ACLs). It flags cases where a role is granted USAGE privilege on a sequence (allowing the generation of values via nextval) but lacks the INSERT privilege on the table associated with that sequence. Since the primary purpose of such a sequence is to generate surrogate keys for new rows, possessing the right to generate IDs without the right to insert rows renders the sequence privilege functionally useless. This violates the principle of least privilege and should be revoked to minimize the attack surface. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2026-01-21 11:50 | MIT License | View |
| 653 | Regular expression with possibly a LIKE pattern | Find expressions that use a regular expression with a like predicate pattern. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-07 10:11 | MIT License | View |
| 654 | Routine body has keywords that are not in uppercase | Keywords in uppercase improve readability. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-07 10:11 | MIT License | View |
| 655 | Routine body only in uppercase | Uppercase means screaming and having code entirely in uppercase makes its reading more difficult. On the other hand, it would be a good idea to have keywords in uppercase. Find routines that body contains a SQL data manipulation statement (which shouldn't be entirely in uppercase) but still the body is completely in uppercase. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-07 10:11 | MIT License | View |
| 656 | Routine body with ordering the query result based on positional references | Find routines where the query result is sorted based on the column number in the SELECT clause. Such query is sensitive towards changing the order of columns in the SELECT clause, i.e., if one changes the order of columns in the SELECT clause, then one must change the numbers in the ORDER BY clause as well, otherwise the query will produce undesired order of rows. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-07 10:11 | MIT License | View |
| 657 | Routine for reading data uses another routine to read some data | This query analyzes the call graph of user-defined routines to identify nested data retrieval patterns. It flags routines that are operationally read-only (performing no DML) but rely on invoking other routines to access additional data. This indicates a layered architecture where data access logic is encapsulated and chained. Identifying these routines is essential for performance profiling, as the total cost of execution is distributed across the call stack rather than being contained within a single procedure body. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-12-26 11:48 | MIT License | View |
| 658 | Routines lacking explicit locking (MVCC risks) | This query identifies user-defined routines that may be susceptible to concurrency anomalies due to a lack of explicit locking. PostgreSQL utilizes Multi-Version Concurrency Control (MVCC), where SELECT statements do not block data modification operations. Consequently, routines that read data to inform subsequent modifications without acquiring row-level locks (e.g., FOR UPDATE, FOR SHARE) or using isolation levels higher than READ COMMITTED are prone to race conditions. This query flags such routines for review to ensure transactional integrity is maintained. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-12-14 14:44 | MIT License | View |
| 659 | Routines that can be invoked with a variable number of arguments | Find routines with a VARIADIC parameter. These are routines that take as input an undefined number of arguments where the argument that is an undefined number are all of the same type and are the last input arguments. | General | INFORMATION_SCHEMA+system catalog base tables | 2025-11-07 10:11 | MIT License | View |
| 660 | Routines that use old syntax for limiting rows | This query identifies PL/pgSQL and SQL routines with no SQL-standard bodies that use the non-standard LIMIT clause for row limitation. It flags these routines because the official, cross-platform SQL standard specifies FETCH FIRST n ROWS ONLY for this purpose. Adhering to the standard improves code portability and maintainability. To ensure relevance, the query intelligently excludes routines that are part of installed extensions, focusing only on user-defined code. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-21 17:37 | MIT License | View |