| 421 | Foreign key columns that have no index | Find foreign key columns that do not have an index. Foreign key columns are often used for performing join operations. It is useful to index such columns. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-02-25 17:30 | MIT License | |
| 422 | Foreign key constraint references to the columns of a UNIQUE constraint not to the columns of the PRIMARY KEY constraint | Find foreign key constraints that reference to a UNIQUE constraint columns not to the PRIMARY KEY constraint columns. This is legal in SQL. However, a tradition is to refer to the primary key columns. If most of the foreign keys refer to the primary key columns, then it raises a question as to whether this kind of design decision has a good reason in a particular case or whether it is an inconsistency. | Problem detection | system catalog base tables only | 2021-02-25 17:30 | MIT License | |
| 423 | Foreign key constraint references to the columns of a UNIQUE constraint not to the columns of the PRIMARY KEY constraint while the referenced table has the primary key | Find foreign key constraints that reference to a UNIQUE constraint columns not to the PRIMARY KEY constraint columns while at the same time the referenced table does have the primary key. This is legal in SQL. However, a tradition is to refer to the primary key columns. If most of the foreign keys refer to the primary key columns, then it raises a question as to whether this kind of design decision has a good reason in a particular case or whether it is an inconsistency. | Problem detection | system catalog base tables only | 2021-02-25 17:30 | MIT License | |
| 424 | Foreign key references a non-key (has optional columns) | Find foreign key constraints that referenced column is optional. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-03-10 12:12 | MIT License | |
| 425 | Foreign key references to a unique index columns not a unique key columns | Find foreign key constraints that reference to the columns that are covered by a unique index not a unique key. | Problem detection | system catalog base tables only | 2022-10-21 10:30 | MIT License | |
| 426 | Foreign key refers to a table that has at least one subtable in the inheritance hierarchy | Find foreign key constraints that refer to a base table that has at least one subtable in the inheritance hierarchy. Rows of the subtable do not belong to the supertable in terms of checking the referential integrity. Let us assume that there is a table T with a subtable Tsub. Let us also assume that table B has a foreign key that refers to the table T. If a row is inserted into Tsub, then this row cannot be referenced from B. | Problem detection | system catalog base tables only | 2021-02-25 17:30 | MIT License | |
| 427 | Foreign servers without user mappings | Find foreign servers that do not have any associated user mappings. "A user mapping typically encapsulates connection information that a foreign-data wrapper uses together with the information encapsulated by a foreign server to access an external data resource." | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-02-25 17:29 | MIT License | |
| 428 | FOR UPDATE in derived tables | Find derived tables that subquery uses FOR UPDATE construct. Reading a data element shouldn't block other read operations of the same element. Thus, you shouldn't use exclusive locking command in a view. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2022-12-12 11:11 | MIT License | |
| 429 | FOR UPDATE is not allowed with aggregate functions | Implement explicit locking correctly. Instead of writing, for instance, SELECT Count(*) AS cnt FROM person WHERE person_id=1 FOR UPDATE; one has to write SELECT person_id FROM person WHERE person_id=1 FOR UPDATE; | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 11:23 | MIT License | |
| 430 | FOR UPDATE is not needed if there is no FROM clause in the SELECT statement | Find routines that use SELECT … FOR UPDATE without selecting rows from a specific table. For instance: SELECT 'text' AS v FOR UPDATE; | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-05 14:10 | MIT License | |
| 431 | Full text search columns that have no gin or gist index | Find columns of base tabels and materialized views with the type tsvector that do not have a gin or a gist index. These are the preferred index types for text search. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-11-07 09:22 | MIT License | |
| 432 | Full text search columns with other type of index than gin or gist index | Find columns of base tabels and materialized views with the type tsvector that do not have a gin or a gist index but have another type of index (e.g., b-tree). Gin and Gist are the preferred index types for text search. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-11-07 09:22 | MIT License | |
| 433 | Function in a function-based index of a column is different from the function that is used in the subquery of a derived table | Find cases where the function of a function-based index of a column is different from the function that is used in the query in a derived table based on the column. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-02-25 17:29 | MIT License | |
| 434 | Function in a function-based index of a column is different of the function that is used in the query in a routine based on the column | Create appropriate indexes to speed up queries. If you apply a function to a column in a query, then create a function-based index based on the function to the column. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 17:26 | MIT License | |
| 435 | Functions that have transactional control | Find functions that contain transactional control statements (BEGIN, START TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT) in their body. PostgreSQL does not permit transaction control in functions. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 10:50 | MIT License | |
| 436 | Function Upper or Lower is used in an index on a non-textual column | Find function-based indexes that are based on function Upper or Lower but have been defined on a non-textual column. Such indexes support case insensitive search but in case of non-textual columns this does not have a meaning. | Problem detection | system catalog base tables only | 2021-02-25 17:30 | MIT License | |
| 437 | Generated stored base table columns duplicates another column in the table | Find generated stored columns in PostgreSQL base tables that duplicate other columns in the table. | Problem detection | INFORMATION_SCHEMA only | 2021-02-25 17:30 | MIT License | |
| 438 | Generated stored base table columns that expression does not refer to any column | Find generated stored base table columns that expression does not refer to any column of the table. It could be that there will be a constant value in every row in case of this column. The support of generated columns was added to PostgreSQL 12. | Problem detection | INFORMATION_SCHEMA only | 2021-02-25 17:29 | MIT License | |
| 439 | Grantable column privileges | Find column privileges that the carrier of the privilege can in turn grant to others, i.e., the privileges have been given WITH GRANT OPTION. The number of privileges that can be passed on should be as small as possible. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2024-01-07 13:43 | MIT License | |
| 440 | Grantable privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers | Find privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers that the carrier of the privilege can in turn grant to others, i.e., the privileges have been given WITH GRANT OPTION. The number of privileges that can be passed on should be as small as possible. | Problem detection | system catalog base tables only | 2024-01-07 13:43 | MIT License | |