PostgreSQL Query Catalog

Find routines with multiple raise exception commands. Perhaps it has multiple tasks and it violates the separation of concerns and single responsibilities principles.

Find routines with SQL-standard body that seem to update data in a table that has a column for modification time but the routine does not seem to update the modification time while updating the row and the table does not seem to have an UPDATE trigger that changes the modification time.

Find routines with the same parameters (same name and type) regardless of the order of parameters. Make sure that there is no accidental duplication. The query helps users to group together routines that probably have related tasks.

Find row level before delete triggers that only task is not to raise an exception and where the variable OLD is not used in the trigger routine outside the RETURN clause.

Find row level before insert and before update triggers that only task is not to raise an exception and where the variable NEW is not used in the trigger routine outside the RETURN clause.

Find ROW level BEFORE triggers that check a condition based on other rows, raise an exception but do not return the row if the condition check succeeds, i.e., exception is not raised.

Find row level BEFORE UPDATE triggers that do not return the new row version. Exclude triggers that raise WARNING/EXCEPTION.

Find row level triggers that have action condition (WHEN clause) but the Boolean expression in its specifications does not refer to neither NEW nor OLD variable.

Find row level update triggers that incorrectly implement update of password hash. It should not be that the new password hash is calculated based on the existing hash.

Find rule names that are used in a database in more than one schema. Different things should have different names. But here different rules have the same name. Also make sure that this is not a duplication.

Find schemas where PUBLIC has the usage privilege.

Find secondary indexes that have been created in the database.

Find SECURITY INVOKER routines that read rows from a table, add rows to a table, update rows in a table, or delete rows from a table. Better to have for these purposes SECURITY DEFINER routines, which make it possible to give to the users privileges to only execute routines without having rights to access their underlying tables.

Find sequence generators that are not associated with any column through the default value mechanism. Please note, that it is also possible to refer to a sequence generator from a routine or from an application. If these are indeed not used, then these should be dropped, otherwise these are dead code.

Find sequence generators that are not owned by a table column, i.e., if one drops the table or the column, then the sequence generator stays in place.

Find sets of columns that have both case sensitive and case insesitive unique constraints enforced based on these columns. In case of textual columns uniqueness could be checked either in case sensitive or case insensitive way. It is a contradiction to check the uniqueneness in both ways in case of the same key.

Find simple check constraints, i.e., check constraints that cover one column that seem to have multiple tasks. The corresponding code smell in case of cleaning code is "G30: Functions Should Do One Thing". (Robert C. Martin, Clean Code)

Find simple check constraints (involve one column) that are associated with a base table column instead of the domain of the column. Common checks of data in columns that share the same domain should be described at the level of domain not at the level of columns.

Find simple natural key columns that are associated with a sequence generator.

Find simple primary keys that column name does not contain the table name. The naming should be clear and consistent.