PostgreSQL Design Check Queries

Catalog of PostgreSQL queries for finding information about a PostgreSQL database and its design problems

	Choose collection:
AND	Choose query type:
AND	Choose query reliability:
AND	Choose category:	Queries of this category provide information that was not available in some earlier PostgreSQL version
AND	Choose data source:	From where does the query gets its information?
AND	Enter string:
AND	Has fixing queries?

All the queries about database objects contain a subcondition to exclude from the result information about the system catalog.
Although the statements use SQL constructs (common table expressions; NOT in subqueries) that could cause performance problems in case of large datasets it shouldn't be a problem in case of relatively small amount of data, which is in the system catalog of a database.
Statistics about the catalog content and project home in GitHub that has additional information.

There are 90 queries.

Seq nr	Name▲	Goal	Type	Data source	Last update	License	...
1	A non-parameterized table function instead of a view	Find table functions that do not have any parameters. Prefer simpler and more portable solutions.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-11-03 21:16	MIT License	View
2	A routine is invoked only once	Find user-defined routines that are invoked by exactly one user-defined routine.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-12-10 17:11	MIT License	View
3	Case insensitive search	Refers to the column pg_proc.prokind and thus works starting from PostgreSQL 11. Find user-defined routines and derived tables (views/materialized views) that have a subquery with case insensitive search (by using the upper or lower function or ILIKE predicate or (?i) modifier of a regular expression pattern).	General	INFORMATION_SCHEMA+system catalog base tables	2021-10-25 16:55	MIT License	View
4	Coalesce/Concat need at least two arguments	Find user-defined routines and derived tables (views/materialized views) that have a subquery that contain an invocation of Coalesce or Concat function with just one argument. You should use the Coalesce and Concat functions correctly by invoking these with at least two arguments. If one invokes these functions with one argument, then the functions will return the input value.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-12-08 11:49	MIT License	View
5	Coverage by routines that have the SQL-standard body	Find for each base table the list of routines (functions and procedures) that refer to the base table. If the database is used through the public database interface (virtual data layer), then, ideally, each table is referred from at least one routine.	General	INFORMATION_SCHEMA+system catalog base tables	2021-11-05 17:00	MIT License	View
6	Cycles in relationships	Find as to whether foreign key constraints between tables form a cycle. For instance, if table C refers to table B, table B refers to table A, table A refers to table C and all the foreign key columns are mandatory, then one cannot add data to the tables. Is cycle value in the result should not contain value TRUE. One should check manually as to whether the foreign key columns are mandatory or optional.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-11-28 15:15	MIT License	View
7	Deterministic (immutable) functions that do not have input parameters	Find deterministic functions that do not have any input parameters. Make sure that it is correct because in general a deterministic function must calculate a value based on input.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-11-09 19:53	MIT License	View
8	Deterministic (immutable) functions that do not return a value	Find deterministic (immutable) functions that do not return a value. This goes against the idea of deterministic functions.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-12-20 19:33	MIT License	View
9	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
10	Do not refer to the table schema in the references to columns	Find routines where in SELECT or UPDATE statements references to columns are prefixed with references to the table schema. Referring to schema in this context bloats the code.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-11-04 17:32	MIT License	View
11	Explicit locking	PostgreSQL uses Multi-version Concurrency Control (MVCC) and thus, sometimes, one has to explicitly lock certain rows or entire table. One has to use LOCK TABLE or SELECT … FOR UPDATE statements for that.	General	INFORMATION_SCHEMA+system catalog base tables	2023-12-11 15:02	MIT License	View
12	Explicit locking is missing	PostgreSQL uses multiversion concurrency control (MVCC). SELECTs do not block modifications and vice versa. One has to take steps to achieve correct behaviour.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2024-01-14 00:35	MIT License	View
13	Explicit locking is missing (2)	Find user-defined routines that have a subquery in a DELETE or UPDATE statement without the FOR UPDATE clause.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2023-12-06 12:15	MIT License	View
14	Explicit locking is probably not needed	You do not need explicit locking (LOCK TABLE or SELECT … FOR UPDATE) in case of routines that only search some data but do not modify any data and do not raise any exception.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-11-04 12:08	MIT License	View
15	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	2021-11-04 10:48	MIT License	View
16	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	View
17	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	View
18	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	View
19	Incorrect characterization of a user-defined routine as an "immutable" routine	Find immutable routines that contain SELECT statements from a database (including SELECT .. FOR UPDATE), INSERT, UPDATE, DELETE, or LOCK statement. The DBMS should get a correct background information about the behaviour of a routine. Immutable routines should not have side effects and should also not ask data from a database because it could change over time. Immutable routines cannot lock tables or its specific rows.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-11-05 14:08	MIT License	View
20	Incorrect characterization of a user-defined routine as a "stable" routine	Find stable routines that contain INSERT, UPDATE, DELETE, SELECT … FOR UPDATE, or LOCK statement. The DBMS should get a correct background information about the behaviour of a routine. Stable routines cannot modify data in a database, lock tables, or its specific rows.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-11-05 13:59	MIT License	View