Goal This query identifies performance and concurrency bottlenecks in user-defined routines caused by executing a standalone SELECT ... FOR [NO KEY] UPDATE or PERFORM ... FOR [NO KEY] UPDATE statement on a table prior to modifying the exact same table with an UPDATE or DELETE statement.

In PostgreSQL, UPDATE and DELETE statements automatically acquire the appropriate row-level write lock (FOR NO KEY UPDATE for non-key updates, or FOR UPDATE for key-column updates and deletes) on target rows that satisfy the WHERE predicate. Executing a separate, preceding SELECT/PERFORM ... FOR [NO KEY] UPDATE on the same table (without an ORDER BY clause for deterministic multi-row deadlock prevention) is redundant—incurring an unnecessary extra scan and lock operation—and degrades concurrency when the initial lock predicate is less restrictive than the subsequent modification (for instance, locking a row solely by its primary key before verifying its status or optimistic lock version). If the subsequent UPDATE or DELETE affects zero rows due to a version mismatch or invalid state, the transaction still retains the write lock acquired by the preliminary query, unnecessarily blocking concurrent transactions.
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 (br) tags, and highlights FOR UPDATE between and tags in the returned routine body. Because multiple routines in the same schema can share a name (function overloading), routine parameters are presented alongside the schema and routine name for unique identification. The query uses a regular expression with a backreference (\1) to capture the target table name in a standalone SELECT or PERFORM ... FROM ... FOR UPDATE; statement and verify whether the identical table is subsequently modified by an UPDATE or DELETE FROM statement within the same routine. Statements containing an ORDER BY clause inside the locking query are excluded, as deterministic lock ordering is a valid pattern for preventing deadlocks during multi-row updates.
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
WITH routines_src AS (
  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,
    CASE 
      WHEN pg_proc.prokind <> 'a' THEN pg_get_functiondef(pg_proc.oid) 
      ELSE NULL 
    END AS raw_src
  FROM 
    pg_catalog.pg_proc
    INNER JOIN pg_catalog.pg_namespace ON pg_proc.pronamespace = pg_namespace.oid
  WHERE 
    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
    )
)
SELECT 
  routine_schema, 
  routine_name, 
  parameters,
  routine_type,
  regexp_replace(
    regexp_replace(
      regexp_replace(raw_src, '<', '&lt', 'g'),
      '[\r\n]', '<br>', 'g'
    ),
    '(FOR[[:space:]]+(?:NO[[:space:]]+KEY[[:space:]]+)?UPDATE)', '<b>\1</b>', 'gi'
  ) AS routine_src
FROM routines_src
WHERE 
  -- Tuvastab nii FOR UPDATE kui ka FOR NO KEY UPDATE enne sama tabeli (\1) UPDATE/DELETE lauset:
  raw_src ~* '\y(?:SELECT|PERFORM)\y(?![^;]+\yORDER[[:space:]]+BY\y)[^;]+\yFROM[[:space:]]+([[:alnum:]_."]+)[^;]+\yFOR[[:space:]]+(?:NO[[:space:]]+KEY[[:space:]]+)?UPDATE\y[^;]*;[\s\S]*?\y(?:UPDATE|DELETE[[:space:]]+FROM)[[:space:]]+(?:ONLY[[:space:]]+)?\1\y'
ORDER BY routine_schema, routine_name, parameters;

Fixing Suggestion

Remove the redundant standalone SELECT ... FOR UPDATE or PERFORM ... FOR UPDATE statement preceding the UPDATE or DELETE operation, and let the UPDATE or DELETE statement atomically filter and lock the target rows (including any state and optimistic row-version checks in its WHERE clause). If pre-locking multiple rows is strictly required to prevent deadlocks across concurrent batch operations, include an explicit ORDER BY clause on the primary key in the SELECT/PERFORM ... FOR UPDATE statement.

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.

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.
User-defined routinesQueries of this category provide information about the user-defined routines