| # | Name | Goal ▲ | Type | Data source | Last update | License | |
|---|---|---|---|---|---|---|---|
| 981 | 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 |
| 982 | Is does not return a boolean | This query identifies user-defined SQL and PL/pgSQL routines (excluding triggers) that exhibit a semantic inconsistency between their name and return type. It flags non-trigger routines whose names begin with a predicate prefix (e.g., is_, has_, can_, on_) but whose defined return type is not BOOLEAN. Such a mismatch violates the principle of self-documenting code, as the name creates a strong expectation of a TRUE/FALSE return value, which the routine's signature then contradicts. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-13 14:03 | MIT License | View |
| 983 | Updatable views that have not been turned to read only | This query identifies views that are automatically updatable by the database engine but lack explicit safeguards to prevent data modification. Specifically, it targets views that meet the criteria for auto-updatability (typically simple projections of a single base table) yet are missing an INSTEAD OF trigger or a DO INSTEAD NOTHING rule. Without these mechanisms, any INSERT, UPDATE, or DELETE operation performed against the view will seamlessly propagate to the underlying base table, which may violate the intended read-only design contract. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-12-16 15:31 | MIT License | View |
| 984 | Domain CHECK constraint name contains table name | This query identifies violations of modular design principles within user-defined domains. It detects CHECK constraints belonging to a domain where the constraint's name incorporates the identifier of a specific base table that utilizes that domain. Since domains are intended as reusable, abstract data types applicable across multiple entities, embedding a specific implementation context (like a table name) into the constraint's identifier creates semantic coupling. This makes the domain confusing to reuse in other contexts. Constraint identifiers should reflect the inherent logic of the domain (e.g., check_email_format), not the object to which it is applied. | Problem detection | INFORMATION_SCHEMA only | 2025-12-14 13:17 | MIT License | View |
| 985 | Insufficient number of user-defined base tables | This query performs a basic structural assessment of the database schema by counting the number of user-defined base tables. It verifies whether the data model meets a minimum complexity threshold, requiring the existence of at least 7 distinct base tables. This metric serves as a proxy for the scope and depth of the implemented domain model. | Problem detection | INFORMATION_SCHEMA only | 2025-11-30 09:30 | MIT License | View |
| 986 | Privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers | This query performs a comprehensive security audit of access control lists (ACLs) across a wide range of database objects, including the database itself, schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers. It retrieves privileges granted to any principal other than the object's owner or the systemic postgres superuser. The objective is to identify and validate "third-party" access rights, ensuring compliance with the principle of least privilege and preventing unauthorized access accumulation. | General | system catalog base tables only | 2026-01-21 10:52 | MIT License | View |
| 987 | 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 |
| 988 | Insufficient number of user-defined non-trigger routines | This query performs a quantity-based assessment of the database's procedural logic. It verifies whether the schema meets a minimum complexity requirement by counting the number of user-defined, non-trigger routines (functions and procedures). The check passes only if the count is equal to or greater than the threshold of 4. This metric is used to ensure a baseline level of backend logic implementation. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-30 08:47 | MIT License | View |
| 989 | Trigger function comments implying operation implementation rather than validation | This query performs a semantic analysis of trigger function comments to identify potential violations of separation of concerns. It flags triggers whose documentation references explicit database operations (e.g., OP1, OP2) but lacks terminology associated with validation or invariant enforcement (e.g., "check", "ensure", "validate"). This linguistic pattern suggests that the trigger may be improperly implementing the business operation itself (a side effect) rather than serving its primary role as an integrity guardrail, or that the documentation inaccurately reflects the trigger's behavior. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-12-19 20:28 | MIT License | View |
| 990 | Frequency of table name lengths based on the table type | This query provides a statistical analysis of identifier length across the schema. It calculates a frequency distribution by grouping base tables, views, and materialized views based on the character length of their names. The result is a count of how many objects exist for each distinct name length, which can be used to audit naming conventions or identify outliers. | Sofware measure | INFORMATION_SCHEMA+system catalog base tables | 2025-11-13 12:56 | MIT License | View |
| 991 | Names of database objects that perhaps end with a sequence number (aggregate view) | This query provides a statistical overview of a potential naming convention issue by counting the number of user-defined database object identifiers that terminate in one or two numerical digits. This naming pattern is a design smell, often indicating bad database structure or semantic ambiguity. Instead of listing each individual name, the query returns an aggregate count, which is useful for quickly assessing the overall prevalence of this issue within the schema and tracking remediation progress over time. | Sofware measure | INFORMATION_SCHEMA+system catalog base tables | 2025-11-17 13:18 | MIT License | View |
| 992 | All key constraints | This query retrieves a comprehensive list of all PRIMARY KEY and UNIQUE constraints defined on base tables within the database. These constraints are the fundamental mechanisms for enforcing entity integrity and uniqueness. The output provides essential information for schema auditing, documentation generation, and analyzing the data model's key structures. It allows administrators and developers to quickly verify how uniqueness is enforced for each table. | General | system catalog base tables only | 2025-11-15 11:24 | MIT License | View |
| 993 | User-defined non-trigger SQL and PL/pgSQL routines | This query retrieves a comprehensive list of user-defined routines (functions and procedures) written in SQL or PL/pgSQL. It explicitly filters the result set to exclude:
The result provides an inventory of the application's explicit, callable database logic. |
General | INFORMATION_SCHEMA+system catalog base tables | 2025-11-30 08:41 | MIT License | View |
| 994 | Names of columns with the type BOOLEAN | This query retrieves the names of all columns defined with the BOOLEAN data type to facilitate an audit of naming consistency. The primary objective is to verify adherence to a recommended best practice: boolean column names should be prefixed with a semantic predicate, such as is_ (in English) or on_ (in Estonian). This convention enhances the self-documenting nature of the schema and improves the readability of SQL statements by framing the column's purpose as a true/false question. | General | INFORMATION_SCHEMA+system catalog base tables | 2025-11-13 14:08 | MIT License | View |
| 995 | Inconsistent prefixing of generic column names | This query validates column naming consistency, specifically targeting generic attributes such as comments or descriptions. It flags tables that exhibit a mixed convention: containing both prefixed (e.g., table_comment) and unprefixed (e.g., description) generic columns within the same database context. This excludes primary and foreign keys. The goal is to enforce a uniform style—either consistently prefixing generic columns with the table name or consistently omitting the prefix—rather than allowing a hybrid approach. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-12-05 09:44 | MIT License | View |
| 996 | Inconsistent use of gratuitous context in the names of non-foreign key and non-primary key columns | This query validates column naming conventions. It returns a row only for tables that have an inconsistent mix of column naming styles—specifically, where some columns (that are not part of a primary or foreign key) are prefixed with the table name and others are not. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-12-05 10:12 | MIT License | View |
| 997 | Perhaps too many input parameters | Too many parameters (in this case four or more) could be a sign of not separating concerns and having a routine that has more than one task. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-07 10:11 | MIT License | View |
| 998 | The number of user-defined triggers by schema, by type, and in total | Triggers can be used to maintain data integrity in a database by causing rejection of data that does not conform to certain rules. Therefore, the number of triggers in a database gives an indication about the state of enforcing constraints at the database level. | Sofware measure | INFORMATION_SCHEMA+system catalog base tables | 2025-11-07 10:11 | MIT License | View |
| 999 | The number of user-defined triggers | Triggers can be used to maintain data integrity in a database by causing rejection of data that does not conform to certain rules. Therefore, the number of triggers in a database gives some indications about the state of enforcing constraints at the database level. The query does not count internal triggers. | Sofware measure | INFORMATION_SCHEMA+system catalog base tables | 2025-11-07 10:11 | MIT License | View |
| 1000 | Do not leave out the referential constraints (islands) | Try to find missing foreign key constraints. Find base tables that do not participate in any referential constraint (as the referenced table or as the referencing table). These tables are like "islands" in the database schema. | Problem detection | system catalog base tables only | 2025-11-07 10:11 | MIT License | View |