| 41 |
Perhaps the type of a base table column/domain should be BOOLEAN (based on CHECK constraints) |
This query identifies base table columns and domains that utilize CHECK constraints to simulate boolean logic on non-boolean data types. It targets constraints that restrict the domain of permitted values to binary sets, such as {0, 1}, {'Y', 'N'}, {'T', 'F'}, or string literals like 'true'/'false'. While functional, this "pseudo-boolean" pattern is considered suboptimal in PostgreSQL. The native BOOLEAN data type is preferred for its storage efficiency, semantic clarity, and built-in support for logical operators, rendering such manual constraints unnecessary. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-12-14 14:37 |
MIT License |
View |
| 42 |
CHECK constraints are inconsistent with DEFAULT values |
This query identifies a logical contradiction between data validation rules and default value definitions. It flags CHECK constraints involving two columns where both columns share the same DEFAULT value, yet the constraint enforces a strict inequality (e.g., col1 < col2) or non-equality (col1 <> col2). This configuration is logically flawed because attempting to insert a row using the default values for both columns will result in an immediate constraint violation (as X < X evaluates to false). This renders the default values mutually exclusive and unusable in practice. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-12-14 13:28 |
MIT License |
View |
| 43 |
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 |
| 44 |
Perhaps incorrect column name (based on default values) |
This query identifies a semantic mismatch between column definitions and their identifiers. It flags base table columns that are configured with a DEFAULT value of CURRENT_USER or SESSION_USER (indicating they store user identity) but whose names fail to reflect this purpose. Specifically, it searches for columns lacking semantic cues such as "user", "login", "owner", or "by" in their names. This obscuration reduces schema self-documentation, as developers cannot intuitively determine that the column is intended for audit or ownership tracking. |
Problem detection |
INFORMATION_SCHEMA only |
2025-12-14 13:09 |
MIT License |
View |
| 45 |
CHECK constraints on TIMESTAMP and DATE columns that explicitly specify a time zone |
This query identifies semantic anomalies in CHECK constraints applied to columns defined as DATE or TIMESTAMP (without time zone). It flags constraints that incorporate time zone conversion logic (e.g., using AT TIME ZONE). Since these data types store "naive" values devoid of time zone offsets, attempting to apply time zone logic makes the constraint's outcome dependent on the current session or server configuration. This non-deterministic behavior is a design flaw, as data validity should be intrinsic and immutable, not dependent on the environment. |
Problem detection |
INFORMATION_SCHEMA only |
2025-12-14 12:48 |
MIT License |
View |
| 46 |
Functions that have transactional control |
This query identifies user-defined functions that contain restricted Transaction Control Language (TCL) statements, such as BEGIN, COMMIT, ROLLBACK, or SAVEPOINT. In PostgreSQL, functions execute within the context of the calling query's transaction and are strictly prohibited from managing transaction boundaries. Attempting to execute these commands within a function body results in a runtime error. To implement transactional logic (such as committing data in batches), the code must be refactored into a PROCEDURE, which supports transaction management. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-12-14 12:25 |
MIT License |
View |
| 47 |
Row level triggers that update or delete data |
This query identifies row-level triggers that contain UPDATE or DELETE statements within their execution body. Embedding data modification logic directly within row-level triggers introduces implicit side effects that can complicate transaction management and debugging. Furthermore, this pattern significantly increases the risk of causing cascading trigger chains or infinite recursion loops, potentially degrading system performance and stability. Such logic should be carefully audited to ensure it is strictly necessary and correctly implemented. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-12-14 12:20 |
MIT License |
View |
| 48 |
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 |
| 49 |
Column names that make joining more difficult (foreign key column name contains the table name) |
This query identifies foreign key columns where the identifier diverges from the referenced candidate key solely due to the redundant inclusion of the referencing table's name (as a prefix or suffix). Such naming redundancy precludes the use of the simplified SQL USING syntax in join operations, forcing the use of the more verbose ON clause. Harmonizing these column names (i.e., making the foreign key name identical to the referenced column name) enables more concise query formulation and improves schema readability. |
Problem detection |
system catalog base tables only |
2025-12-14 11:56 |
MIT License |
View |
| 50 |
Column names that make joining tables more difficult (table names) |
This query identifies foreign key relationships where the identifier of the referenced column diverges from the foreign key column solely due to the inclusion of the table name. It specifically targets cases where the referenced column name is formed by concatenating the target table name with the target column name (e.g., referenced_col = table_name || '_' || fk_col). This naming redundancy prevents the use of the simplified SQL USING syntax for join operations, necessitating verbose ON clauses. Harmonizing these identifiers by standardizing the naming convention improves schema readability and query conciseness. |
Problem detection |
system catalog base tables only |
2025-12-14 11:53 |
MIT License |
View |
| 51 |
Different suffixes of a candidate key column and a referencing foreign key column |
This query identifies naming inconsistencies in foreign key relationships by comparing the suffixes of foreign key columns against their referenced candidate key columns. It flags pairs where the suffixes diverge (e.g., referencing user_id via a column named user_code or user_ref). Such discrepancies prevent the use of the simplified ANSI SQL USING syntax for joins, forcing the use of explicit ON clauses. The goal is to enforce a standardized suffix convention (typically _id) across the schema to improve maintainability and query readability. |
Problem detection |
system catalog base tables only |
2025-12-14 11:43 |
MIT License |
View |
| 52 |
Column names that make joining tables more difficult |
This query identifies foreign key columns where the identifier differs from the referenced candidate key identifier. It explicitly excludes self-referencing constraints (recursive relationships), where name divergence is structurally mandatory. The primary objective is to identify opportunities to harmonize column names across the schema. Synchronizing the foreign key name with the referenced column name facilitates the use of the ANSI SQL USING clause in join operations (e.g., JOIN t1 USING (client_id)), which is significantly more concise than the explicit ON predicate required when names differ. |
Problem detection |
system catalog base tables only |
2025-12-14 11:31 |
MIT License |
View |
| 53 |
Column names that make joining tables more difficult (very similar names) |
This query identifies foreign key columns where the identifier exhibits a minimal textual deviation (exactly one character) from the referenced candidate key. This specific proximity often indicates a typographical error or a singular/plural inconsistency (e.g., user_id vs users_id). The query explicitly excludes self-referencing constraints, where name divergence is structurally mandatory. Harmonizing these names enables the use of the simplified SQL USING syntax for joins, replacing verbose ON clauses and improving query readability. |
Problem detection |
system catalog base tables only |
2025-12-14 11:30 |
MIT License |
View |
| 54 |
Column names that make joining tables more difficult (quite similar names) |
This query identifies foreign key columns where the identifier deviates slightly from the referenced candidate key, specifically exhibiting a textual difference (Levenshtein distance) of two to four characters. This range typically captures minor prefixes (e.g., fk_) or suffixes that prevent the use of the concise SQL USING syntax in join operations. The query explicitly excludes self-referencing constraints (recursive relationships), where distinct column names are structurally mandatory. Aligning these names allows for cleaner, more readable query formulation. |
Problem detection |
system catalog base tables only |
2025-12-14 11:29 |
MIT License |
View |
| 55 |
Inconsistent usage of string concatenation methods within or accross different database objects |
This query audits the database schema to assess the consistency of string concatenation practices. It identifies the concurrent usage of multiple disparate methods for joining text: the standard concatenation operator (||) and the variadic functions concat() and concat_ws(). While each method has specific use cases (e.g., concat for null safety), indiscriminate mixing of these techniques across similar contexts indicates a lack of coding standards. The output facilitates a review to enforce a more uniform approach where appropriate. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-12-13 14:49 |
MIT License |
View |
| 56 |
Find views that can accept data modification statements |
This query identifies all views against which data modification statements (INSERT, UPDATE, DELETE) can be executed without raising an error. It evaluates the is_insertable_into and is_updatable attributes to detect both "naturally" updatable views (where PostgreSQL automatically maps changes to base tables) and views made updatable via the rewrite rule system (specifically DO INSTEAD rules). Notably, this definition encompasses views defined with DO INSTEAD NOTHING rules; while such views do not physically modify data, they are technically considered updatable because they accept DML statements without failure. |
General |
INFORMATION_SCHEMA only |
2025-12-13 12:34 |
MIT License |
View |
| 57 |
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 |
| 58 |
Columns with array or user-defined type |
This query identifies columns defined with complex data types, specifically Arrays or User-Defined Types (UDTs). While PostgreSQL supports these advanced features, their indiscriminate use often signals an over-engineered schema. The query serves as a prompt to audit these columns and verify that the complex type is strictly necessary for performance or domain logic, and that a standard relational structure (e.g., scalar types or a child table) would not be a more appropriate and flexible design choice. |
General |
system catalog base tables only |
2025-12-12 17:23 |
MIT License |
View |
| 59 |
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 |
| 60 |
Unnecessary explicit locking in read-only routines |
This query identifies read-only routines that unnecessarily utilize explicit locking mechanisms. PostgreSQL's Multi-Version Concurrency Control (MVCC) ensures that readers do not block writers (and vice versa) for standard query operations. Therefore, routines that perform no data modification (DML) and do not raise exceptions have no functional need to acquire table-level (LOCK TABLE) or row-level (FOR SHARE/UPDATE) locks. Using them in this context provides no benefit while actively degrading system concurrency by blocking other transactions. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-12-10 13:23 |
MIT License |
View |