Goal This query identifies read-only routines that unnecessarily utilize explicit locking mechanisms. PostgreSQL's Multi-Version Concurrency Control (MVCC) ensures that readers do not block writers (and writers do not block readers) during standard query operations. Consequently, routines that perform no data modification (DML) and do not raise exceptions have no functional need to acquire table-level locks (LOCK TABLE) or row-level locks (FOR UPDATE / FOR SHARE). Acquiring locks in purely read-only contexts provides no consistency benefits while actively degrading database concurrency by causing transaction contention. The query explicitly excludes routines whose names indicate they were intentionally designed to handle locking.
Notes This query references the pg_proc.prokind catalog column and is therefore compatible with PostgreSQL 11 and later. In the returned routine body, newline characters are replaced with HTML line break (br) tags to improve readability when query results are rendered in a web browser. Because PostgreSQL supports function overloading—allowing multiple routines in the same schema to share an identical name with distinct parameter lists—the query outputs parameter signatures alongside the schema and routine name to ensure unique identification. Functionally, the query's heuristic assumes that explicit locking is legitimate only when a routine reads data to conditionally determine whether to modify data, or when validating state before potentially raising an exception. Furthermore, it accounts for the possibility that a seemingly read-only routine may serve as a dedicated helper called by other routines specifically to acquire locks within an active transaction. The heuristic attempts to filter out such helper routines if their names clearly reflect a locking purpose, leaving unlabelled helper routines to be flagged for architectural review.
Type Problem detection Each row in the result could represent a flaw in the design
Reliability Medium Medium number of false-positive results
License MIT (opens in new tab)
Data Source INFORMATION_SCHEMA+system catalog
SQL Query
SELECT 
  pg_namespace.nspname AS routine_schema, 
  pg_proc.proname AS routine_name, 
  pg_get_function_identity_arguments(pg_proc.oid) AS parameters,
  CASE WHEN pg_proc.prokind='f' THEN 'FUNCTION'
  WHEN pg_proc.prokind='p' THEN 'PROCEDURE'
  WHEN pg_proc.prokind='w' THEN 'WINDOW FUNCTION' END AS routine_type,
   regexp_replace( regexp_replace(pg_get_functiondef(pg_proc.oid),'<','&lt','g'),'[\r\n]','<br>','g')  AS routine_src
FROM 
  pg_catalog.pg_proc, 
  pg_catalog.pg_namespace
WHERE 
  pg_proc.pronamespace = pg_namespace.oid
  AND pg_proc.prokind<>'a'
 AND pg_proc.proname NOT IN ('f_assume_you_must_use_files', 'f_check_format_comma_separated_list', 'f_check_password', 'f_default_value_with_no_match') 
 AND pg_namespace.nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
AND pg_proc.proname!~'(lukusta|lock)' 
AND (pg_get_functiondef(pg_proc.oid)~*'([[:space:]]|[(])*(SELECT|PERFORM)[[:space:]]')  
AND (pg_get_functiondef(pg_proc.oid)~*'([[:space:]]|[(])*(LOCK[[:space:]]|for[[:space:]]+(update|no[[:space:]]+key[[:space:]]+update|share|key[[:space:]]+share))') 
AND (pg_get_functiondef(pg_proc.oid)!~*'([[:space:]]|[(])*(RAISE[[:space:]]+EXCEPTION|INSERT[[:space:]]|UPDATE[[:space:]]|DELETE[[:space:]]|MERGE[[:space:]])') 
ORDER BY routine_schema, routine_name, parameters;

Collections

This query belongs to the following collections:

Find problems automatically

Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .

NameDescription
Find problems automaticallyQueries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .

Categories

This query is classified under the following categories:

Concurrency control

Queries of this category provide information about concurrency control.

Does not work in some earlier PostgreSQL version

Queries of this category provide information that was not available in some earlier PostgreSQL version

Performance

Queries of this category provide information about indexes in a database.

Result quality depends on names

Queries of this category use names (for instance, column names) to try to guess the meaning of a database object. Thus, the goodness of names determines the number of false positive and false negative results.

Transactions

Queries of this catergory provide information about the use of transactions.

User-defined routines

Queries of this category provide information about the user-defined routines

NameDescription
Concurrency controlQueries of this category provide information about concurrency control.
Does not work in some earlier PostgreSQL versionQueries of this category provide information that was not available in some earlier PostgreSQL version
PerformanceQueries of this category provide information about indexes in a database.
Result quality depends on namesQueries of this category use names (for instance, column names) to try to guess the meaning of a database object. Thus, the goodness of names determines the number of false positive and false negative results.
TransactionsQueries of this catergory provide information about the use of transactions.
User-defined routinesQueries of this category provide information about the user-defined routines