| 541 |
Number of underlying tables of derived tables |
Find for each view or materialized view the number of tables based on which the derived table has been directly defined. These tables could be base tables or derived tables. |
Sofware measure |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 542 |
Number of used tables |
Find statistics about how many derived tables have how many different underlying tables. |
Sofware measure |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 543 |
Number of using views |
Find statistics about how many base tables have how many derived tables that use these tables. |
Sofware measure |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 544 |
Number of views with and without security barrier |
Find the number of views, the number of views with and without security barrier setting, and the names of views with and without the security barrier setting. |
Sofware measure |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 545 |
ON DELETE CASCADE is not needed (based on classifier tables) |
This query identifies foreign key constraints with ON DELETE CASCADE that reference classifier tables. Classifier tables hold reference data (e.g., status types, categories), and their rows should not be deleted if they are in use. Applying ON DELETE CASCADE to such a relationship is a critical design flaw, as it creates a direct path for the deletion of a single lookup value to trigger the mass deletion of operational business data. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2026-05-17 00:09 |
MIT License |
View |
| 546 |
Only ID primary key |
Find base base tables have the simple primary key that contains a column with the (case insensitive) name id and an integer type. In addition, the primary key values are generated automatically by the system by using a sequence generator. In addition the base table must not have any unique constraint. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 547 |
Only one value permitted in a base table or a foreign table column (based on check constraints) |
Find columns of base tables or foreign tables in case of which a check constraint on the column permits only one value in the column. The constraint may be correct if it is applied to a column of a subtable that is inherited from the supertable or is used to enfore the rule that the table can have at most one row. |
General |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 548 |
Only one value permitted in a base table or a foreign table column (based on enumeration types) |
Find columns of base tables or foreign tables in case of which the type of the column permits only one value in the column. The type is an enumeration type that specifies only one value. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 549 |
Only one value permitted in a non-inherited base table or a foreign table column (based on check constraints) |
Find columns of base tables or foreign tables in case of which a check constraint on the column permits only one value in the column. Exclude columns that are inherited from a supertable because the constraint may be correct if it is applied to a column of a subtable that is inherited from the supertable. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 550 |
ON UPDATE CASCADE is missing (based on classifier tables) |
This query identifies foreign key constraints referencing classifier tables that lack an ON UPDATE CASCADE clause. Classifier tables hold reference data (e.g., status types, categories). While their rows should not be deleted if they are currently in use, any modifications to their underlying codes should automatically cascade to all dependent tables to maintain data integrity. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2026-05-17 00:08 |
MIT License |
View |
| 551 |
ON UPDATE CASCADE is probably missing (based on data types) |
This query identifies foreign key constraints that likely require an ON UPDATE CASCADE action. It operates on a strong heuristic based on data type: the query flags foreign keys where the column type is neither integer nor uuid and the ON UPDATE CASCADE action is absent. This assumes that non-numeric, non-UUID keys are natural keys (e.g., VARCHAR, CHAR) whose values are meaningful and may need to be updated over time. The absence of ON UPDATE CASCADE on these relationships can impede data maintenance and violate referential integrity upon updates. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-08 10:33 |
MIT License |
View |
| 552 |
ON UPDATE CASCADE is probably missing (based on the properties of the referenced column) |
This query identifies foreign key constraints that are likely missing an ON UPDATE CASCADE action on what is inferred to be a simple, integer-based natural key. It isolates referenced keys that are single integer columns, are not themselves foreign keys, and, crucially, have no associated sequence generator. This combination strongly indicates a manually managed key whose values might change. The absence of ON UPDATE CASCADE on such relationships can lead to significant data maintenance issues if the natural key value ever needs to be updated. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-08 10:30 |
MIT License |
View |
| 553 |
Optimistic locking routines lacking execution feedback |
This query identifies SQL routines that implement optimistic concurrency control via the xmin system column but fail to provide an execution status to the invoker. Specifically, it flags functions that perform UPDATE or DELETE operations filtered by xmin (a version check) but do not return information regarding the operation's success (e.g., a row count or a BOOLEAN status). This is a critical logic flaw; if a concurrency conflict occurs (the row was modified by another transaction), the operation yields zero rows. Without a return value, the failure is silent, leaving the calling application unaware that the data modification did not occur. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-12-15 11:35 |
MIT License |
View |
| 554 |
Optional base table columns that participate in a UNIQUE constraint or index |
Find optional base table columns that participate in a UNIQUE constraint or index. Each base table has one or more candidate keys. One of these is usually selected to be the primary key, other are called alternate keys. To enforce an alternate key one should define a UNIQUE constraint and determine that all the key columns are mandatory (NOT NULL) just like the primary key columns are mandatory. Make sure that the NOT NULL constraint is not missing on these columns. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 555 |
Optional non-foreign key base table columns that participate in a UNIQUE constraint or index |
Find optional base table columns that participate in a UNIQUE constraint or index but do not participate in a foreign key constraint. Each base table has one or more candidate keys. One of these is usually selected to be the primary key, other are called alternate keys. To enforce an alternate key one should define a UNIQUE constraint and determine that all the key columns are mandatory (NOT NULL) just like the primary key columns are mandatory. Make sure that the NOT NULL constraint is not missing on these columns. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 556 |
Overloading |
Make sure that there is genuine overloading instead of duplication or dead code. "In some programming languages, function overloading or method overloading is the ability to create multiple functions of the same name with different implementations." (Wikipedia) In PostgreSQL one can do it automagically by having multiple routines with the same name but different parameters in the same schema. |
General |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 557 |
Overloading may cause runtime error |
Routines in the same schema that have the same name and that have parameters with different types or different number of parameters are not considered to be in conflict at the creation time. However, if defaults are provided in the definition of parameters, then these routines might be conflict during runtime. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 558 |
Oversized classifier code data typess |
This query identifies classifier tables where the code column uses a data type that accommodates unnecessarily large values for reference data. Specifically, it flags code columns defined as integer, bigint, or varchar(n) where n > 10. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2026-05-16 14:00 |
MIT License |
View |
| 559 |
Pairs of non-key column name and type pairs that have in different base tables a different default value |
Find non-key base table columns with the same name and type that have different default values. Be consistent. Columns with the same name and type shouldn't probably have different default values in case of different tables. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code) |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 560 |
Pairs of non-key column name and type pairs that have in some base tables a default value and some cases not |
Find non-key base table columns with the same name and type that have in some cases a default value and some cases not. Be consistent. Columns with the same name and type should probably either always have a default value in case of different tables or never have a default value in case of different tables. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code) |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |