PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 90 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.

# 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 2024-12-22 14:53 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-12-13 14:57 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 2024-12-19 11:03 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