| # | Name | Goal ▲ | Type | Data source | Last update | License | |
|---|---|---|---|---|---|---|---|
| 1 | 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 | 2024-12-19 11:03 | MIT License | View |
| 2 | Base tables that have a unique constraint but not the primary key | A common style is to declare in each base table one of the candidate keys as the primary key. All the other candidate keys would be alternate keys that will be enforce with the help of UNIQUE + NOT NULL constraints. | Problem detection | INFORMATION_SCHEMA only | 2021-02-25 17:30 | MIT License | View |
| 3 | Perhaps a too long PL/pgSQL routine | A large routine may have multiple tasks that should be split between multiple routines, each of which has a more focused task. Find the PL/pgSQL routines where the number of physical lines of code is bigger than 40. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2024-12-27 11:13 | MIT License | View |
| 4 | Perhaps a too long SQL routine | A large routine may have multiple tasks that should be split between multiple routines that have a more focused task. Find the SQL routines where the number of statements (logical lines of code) is bigger than 5. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2024-01-04 16:39 | MIT License | View |
| 5 | Potentially missing PRIMARY KEY or UNIQUE constraints (based on foreign keys) | All the keys must be enforced. Find base tables that implement M:N relationship types and that allow multiple relationships of the same type between the same entities. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-02-25 17:29 | MIT License | View |
| 6 | ROW level BEFORE triggers with RAISE EXCEPTION but without RETURN NULL | Although RAISE EXCEPTION stops the execution it would be a good style to still return. In this case the return should bring back NULL, i.e., the row will not be processed further | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-02-25 17:30 | MIT License | View |
| 7 | Base tables that have a surrogate key and do not have any uniqueness constraints | A surrogate key is a key that consist of one column. The values of this column do not have any meaning for the user and the system generates the values (integers) automatically. In case of defining a surrogate key in a table it is a common mistake to forget declaring existing natural keys in the table. The query discards tables with only one column. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-10-26 17:47 | MIT License | View |
| 8 | Base tables that have a surrogate key and all its unique constraints have an optional column | A surrogate key is a primary key that consist of one column. The values of this column do not have any meaning for the user and the system usually generates the values (integers) automatically. In case of defining a surrogate key in a table it is a common mistake to forget declaring existing natural keys in the table. If a key covers an optional column then it does not prevent duplicate rows where some values are missing and other values are equal. Because NULL is not a value and is not duplicate of another NULL the, follwing is possible: CREATE TABLE Uniq(a INTEGER NOT NULL, b INTEGER, CONSTRAINT ak_uniq UNIQUE (a, b)); INSERT INTO Uniq(a, b) VALUES (1, NULL); INSERT INTO Uniq(a, b) VALUES (1, NULL); |
Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-10-21 11:54 | MIT License | View |
| 9 | Unnecessary privileges to use trigger functions | A user that corresponds to an application does not have to have privileges to use trigger functions. If it has these, then it violates the principle of least privilege. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-02-25 17:30 | MIT License | View |
| 10 | Trigger routines with TG_OP variable that are not associated with a suitable trigger | Automatically defined TG_OP variable in a trigger function has data type text. Its value is a string of INSERT, UPDATE, DELETE, or TRUNCATE telling for which operation the trigger was fired. Find the routines that according to the TG_OP value must react to a certain operation but the routine is not associated with any triggers that are fired by the operation. For instance, the routine specifies reaction to DELETE operation but the routine is not associated with any DELETE trigger. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-12-25 01:26 | MIT License | View |
| 11 | Do not create user-defined routines that have the same name as some installed extension routine | Avoid creating user-defined routines that have the same name as some extension routine because it may cause confusion. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-10-25 16:14 | MIT License | View |
| 12 | User-defined routines that have the same name as some system-defined routine. | Avoid creating user-defined routines that have the same name as some system-defined routine because it may cause confusion. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-02-25 17:30 | MIT License | View |
| 13 | IS NOT DISTINCT FROM in derived tables | Avoid using IS NOT DISTINCT FROM because it makes the query planner to avoid using an index. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2024-09-28 13:08 | MIT License | View |
| 14 | IS NOT DISTINCT FROM in routines | Avoid using IS NOT DISTINCT FROM because it makes the query planner to avoid using an index. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2024-09-17 13:14 | MIT License | View |
| 15 | NOT IN or <> ALL in routines | Avoid using NOT IN or <>ALL with a non-correlated subquery in PostgreSQL because the query performance will be very poor. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2024-12-24 13:50 | MIT License | View |
| 16 | NOT IN or <> ALL in derived tables | Avoid using NOT IN or <>ALL with a non-correlated subquery in PostgreSQL because the query performance will be very poor, especially in case of large data sizes. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2024-12-24 13:50 | MIT License | View |
| 17 | Too generic names (routines) | "Avoid using the same word for two purposes. Using the same term for two different ideas is essentially a pun" (Robert C. Martin, Clean Code) Do not use the Estonian words like 'lisa', 'muuda', 'kustuta' or the corresponding English words 'add', 'delete', 'update' as the names of routines because it would make the code much less understandable. What is the task of the routine f_add? Register people? Register orders? What? Do not refer to the generic concepts like data and information because all routines work with these. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-01-07 20:38 | MIT License | View |
| 18 | Non-updatable views that have data modification privileges | Be precise and do not give impossible privileges. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-02-25 17:29 | MIT License | View |
| 19 | Use invocation of a precise function instead of casting in a default value expression | Be precise and write as little code as possible. Prefer expressions with simple invocations of functions like localtimestamp, current_timestamp, and current_date over expressions like (now())::date. Find table columns that have a default value that casts the type of the returned value of a non-deterministic function (now, localtimestamp, current_timestamp, and current_date). | Problem detection | INFORMATION_SCHEMA only | 2021-02-25 17:29 | MIT License | View |
| 20 | Definition of a non-minimal superkey instead of a candidate key (based on check constraints) | Candidate key is a minimal superkey, meaning that it is not possible to remove columns from the candidate key without losing its uniqueness property. One should define primary key's and unique constraints based on candidate keys. Find primary key and unique constraints where a proper subset of columns has a check constraint that permits only one value in the column. The candidate key should involve only columns without such constraint. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2024-04-30 20:25 | MIT License | View |