PostgreSQL Query Catalog

Find table functions that do not have any parameters. Prefer simpler and more portable solutions.

Find user-defined routines that are invoked by exactly one user-defined routine.

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).

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.

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.

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.

Find deterministic (immutable) functions that do not return a value. This goes against the idea of deterministic functions.

Avoid creating user-defined routines that have the same name as some extension routine because it may cause confusion.

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.

This query identifies performance and concurrency bottlenecks caused by excessive locking. It flags INSERT, UPDATE, or DELETE statements that utilize subqueries containing the FOR UPDATE clause. Using FOR UPDATE acquires an exclusive lock, which is semantically inappropriate if the rows in the subquery are merely being read for reference or validation rather than being modified. This practice degrades system concurrency by unnecessarily blocking other transactions.

This query identifies user-defined routines that employ explicit locking mechanisms to supplement PostgreSQL's default Multi-Version Concurrency Control (MVCC). It detects the presence of table-level locking (LOCK TABLE) or explicit row-level locking clauses (e.g., SELECT ... FOR UPDATE, FOR SHARE). While MVCC generally provides sufficient isolation for concurrent transactions, explicit locking is necessary in specific race-condition scenarios. This inventory assists in auditing concurrency control strategies and detecting potential sources of deadlocks or serialization bottlenecks.

This query identifies concurrency risks in user-defined routines by flagging INSERT, UPDATE, or DELETE statements that utilize subqueries without a FOR SHARE locking clause. Failure to acquire a shared lock on source rows allows concurrent transactions to modify or delete them between the subquery's execution and the outer operation, potentially leading to data inconsistencies. Routines utilizing the xmin system column are excluded, operating on the assumption that they implement Optimistic Concurrency Control (version checking) and therefore do not require pessimistic locking.

This query identifies concurrency risks in user-defined routines by flagging INSERT, UPDATE, or DELETE statements that utilize subqueries without a FOR SHARE locking clause. Failure to acquire a shared lock on source rows allows concurrent transactions to modify or delete them between the subquery's execution and the outer operation, potentially leading to data inconsistencies. Routines utilizing the xmin system column are excluded, operating on the assumption that they implement Optimistic Concurrency Control (version checking) and therefore do not require pessimistic locking.

This query identifies potential null-propagation defects in user-defined routines and views. It targets subqueries utilizing the standard concatenation operator (||) where operands are not protected by a coalesce() function. In PostgreSQL, the operation string || NULL yields NULL, causing the entire result to vanish if any component is missing. This behavior is often unintentional. The query flags these risky patterns, suggesting remediation via explicit null handling or the adoption of null-safe alternatives like concat(), concat_ws(), or format().

This query identifies superfluous function calls within routines and views, specifically targeting invocations of coalesce(), concat(), or concat_ws() that are supplied with only a single argument. These functions are variadic and designed to operate on multiple values (e.g., returning the first non-null value or joining strings). When called with a single argument, they function as an identity operation, returning the input unchanged. This pattern indicates either a coding error (missing arguments) or redundant logic that should be removed to simplify the expression.

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.

This query identifies user-defined functions that contain restricted Transaction Control Language (TCL) statements, such as BEGIN, COMMIT, ROLLBACK, or SAVEPOINT. In PostgreSQL, functions execute within the context of the calling query's transaction and are strictly prohibited from managing transaction boundaries. Attempting to execute these commands within a function body results in a runtime error. To implement transactional logic (such as committing data in batches), the code must be refactored into a PROCEDURE, which supports transaction management.

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.

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.

Find PL/pgSQL routines that use comparison operators =< or =>.