Goal This query identifies concurrency risks in user-defined routines by flagging INSERT, UPDATE, or DELETE statements that utilize subqueries or Common Table Expressions (WITH clauses) without an explicit FOR SHARE or FOR UPDATE locking clause. Failure to acquire a lock on source rows allows concurrent transactions to modify or delete them between the subquery's execution and the outer data modification, potentially leading to race conditions and data inconsistencies. Routines utilizing the xmin system column or incrementing an explicit row-version column (e.g., version, rea_versioon) are excluded, as they implement Optimistic Concurrency Control (OCC). Additionally, routines that explicitly lock target rows in a preceding SELECT ... FOR UPDATE/SHARE statement immediately prior to a WITH clause are excluded.
Notes Refers to the column pg_proc.prokind and thus works starting from PostgreSQL 11. For better readability in a web browser, the query escapes < characters, replaces newline characters with HTML line break (
) tags, and highlights the SELECT and FOR SHARE keywords in bold (). Because multiple routines in the same schema can share a name (function overloading), routine parameters are included alongside the schema and routine name for unique identification. The query detects INSERT, UPDATE, and DELETE statements combined with SELECT ... FROM ... WHERE subqueries or CTEs that lack FOR SHARE or FOR UPDATE locking within the same scope, while respecting subquery parenthesis boundaries to avoid false positives across multiple CTEs. It excludes routines that implement optimistic locking (via xmin or version column incrementing) or acquire locks in a standalone SELECT ... FOR UPDATE/SHARE statement right before a WITH block (which is required in PostgreSQL when modifying the locked table in the outer query).
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(
      regexp_replace(
        regexp_replace(pg_get_functiondef(pg_proc.oid), '<', '&lt', 'g'),
        '[\r\n]', '<br>', 'g'
      ),
      'SELECT', '<b>SELECT</b>', 'gi'
    ),
    'FOR[[:space:]]+SHARE', '<b>FOR SHARE</b>', 'gi'
  ) 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_get_functiondef(pg_proc.oid) ~* '(INSERT|UPDATE|DELETE)[^;]+(?<!=[[:space:]]*[(][[:space:]]*)SELECT(?=[^;)]+FROM[[:space:]]+[^;)]+WHERE)(?![^;]+FOR[[:space:]]+(SHARE|UPDATE))'
    OR 
    pg_get_functiondef(pg_proc.oid) ~* 'WITH[^;]+(?<!=[[:space:]]*[(][[:space:]]*)SELECT(?=[^;)]+FROM[[:space:]]+[^;)]+WHERE)(?![^;]+FOR[[:space:]]+(SHARE|UPDATE))[^;]+(INSERT|UPDATE|DELETE)[[:space:]]'
  )
  AND pg_get_functiondef(pg_proc.oid) !~* '\yxmin\y'
  AND pg_get_functiondef(pg_proc.oid) !~* '\y(rea_versioon|versioon|version)\s*=\s*([a-z0-9_]+\.)?(rea_versioon|versioon|version)\s*\+\s*1\y'
  AND pg_get_functiondef(pg_proc.oid) !~* 'SELECT[^;]+FOR[[:space:]]+(UPDATE|SHARE)[^;]*;[[:space:]]*WITH\y'
ORDER BY routine_schema, routine_name, parameters;

Fixing Suggestion

Use SELECT … FOR SHARE in the subquery to block concurrent modification of data based on that the current modification is made.

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

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