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 catergory provide information about the use of transactions.
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 14 queries.

Seq nr	Name▲	Goal	Type	Data source	Last update	License	...
1	Deferrable constraints	Find all deferrable constraints.	General	system catalog base tables only	2021-10-08 11:25	MIT License	View
2	Deferrable foreign key constraint with a RESTRICT compensating action	Find deferrable foreign key constraint with ON UPDATE RESTRICT or ON DELETE RESTRICT compensating action. Referential actions are carried out before, and are not part of, the checking of a referential constraint. Deferring a referential constraint defers the checking of the of the constraint (a ) but does not defer the referential actions of the referential constraint. In PostgreSQL the essential difference between NO ACTION and RESTRICT is that NO ACTION allows the check to be deferred until later in the transaction, whereas RESTRICT does not. Thus RESTRICT could result with the failure of data modification where in case of NO ACTION the modification would succeed.	Problem detection	system catalog base tables only	2021-10-08 11:29	MIT License	View
3	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
4	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
5	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
6	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
7	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
8	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
9	Initially deferred constraint triggers with unnecessary locking	Initially deferred constraint triggers do not need explicit statements for locking tables or rows.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-10-08 11:25	MIT License	View
10	No point to have in a procedure COMMIT without ROLLBACK or vice versa	If you end transaction in a procedure, then there should be a possibility to either commit or rollback the transaction based on some condition. Procedures appeared in PostgreSQL 11.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-11-04 12:00	MIT License	View
11	Procedures cannot have START TRANSACTION and SAVEPOINT	You cannot use a START TRANSACTION or a SAVEPOINT statement in a procedure. Procedures appeared in PostgreSQL 11.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-11-04 11:50	MIT License	View
12	SECURITY DEFINER procedures cannot end transactions	You cannot use COMMIT and ROLLBACK in a SECURITY DEFINER procedure. Procedures appeared in PostgreSQL 11.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-11-04 12:12	MIT License	View
13	Triggers with SELECT (i.e., probably check data based on another table)	If a trigger is used for enforcing a constraint, then it should take into account that due to the implementation of multiversion concurrency control (MVCC) in PostgreSQL, reading data does not block data modification and vice versa. Thus, there may be a need to lock the entire table or some row explicitly.	General	INFORMATION_SCHEMA+system catalog base tables	2020-11-06 14:51	MIT License	View
14	User-defined routines that read data	Find user-defined routines that contain SELECT … FROM or PERFORM … FROM operations. PostgreSQL uses multiversion concurrency control (MVCC). Therefore, SELECTs do not block modifications and vice versa. One has to take steps to achieve correct behaviour of data access code. In addition, one should not ask data with multiple queries if it is possible to achieve the result with only one query.	General	INFORMATION_SCHEMA+system catalog base tables	2023-12-21 15:00	MIT License	View