Goal This query identifies non-trigger user-defined routines (functions and procedures) that execute UPDATE or DELETE statements without implementing Optimistic Concurrency Control (OCC) via the PostgreSQL xmin system column or a dedicated row-version column (such as rea_versioon, versioon, version, or row_version).

In interactive multi-user applications, a user typically reads a record onto a screen, reviews or edits it (user think-time), and then invokes a database routine to save or delete the record in a new database transaction. Because database locks cannot be held across user think-time, executing an UPDATE or DELETE statement that filters solely by the entity's identifier without verifying that the row's version matches the version originally read by the user exposes the system to the Lost Update anomaly: if another user modifies the record in the meantime, the second transaction will blindly overwrite or delete those intermediate changes without warning.

Notes Queries the PostgreSQL system catalogs (pg_proc, pg_namespace, pg_type) to inspect the decompiled source code (pg_get_functiondef) of user-defined functions and procedures (prokind IN ('f', 'p')), excluding trigger functions (typname <> 'trigger') and system schemas. The query uses case-insensitive regular expressions to find routines that contain an UPDATE <table> SET or DELETE FROM <table> statement, but do not reference xmin or a version identifier (rea_versioon, versioon, version, row_version) anywhere in their definition. For better readability in a web browser, < characters are escaped and newline characters are replaced with HTML line break (<br>) tags. Routine parameters are included alongside the schema and routine name to uniquely identify overloaded routines.
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 
    n.nspname AS routine_schema, 
    p.proname AS routine_name, 
    pg_get_function_identity_arguments(p.oid) AS parameters,
    CASE WHEN p.prokind = 'f' THEN 'FUNCTION' ELSE 'PROCEDURE' END AS routine_type,
    CASE WHEN p.prokind <> 'a' THEN pg_get_functiondef(p.oid) ELSE NULL END AS raw_src
  FROM pg_catalog.pg_proc p
  INNER JOIN pg_catalog.pg_namespace n ON p.pronamespace = n.oid
  INNER JOIN pg_catalog.pg_type t ON t.oid = p.prorettype
  WHERE p.prokind IN ('f', 'p')
    AND t.typname <> 'trigger'
    AND n.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(raw_src, '<', '&lt', 'g'), '[\r\n]', '<br>', 'g') AS routine_src
FROM routines_src
WHERE   
  raw_src ~* '\y(UPDATE[[:space:]]+[[:alnum:]_."]+[[:space:]]+SET|DELETE[[:space:]]+FROM[[:space:]]+[[:alnum:]_."]+)\y'  
  AND raw_src !~* '\y(xmin|rea_versioon|versioon|version|row_version)\y'
ORDER BY routine_schema, routine_name, parameters;

Fixing Suggestion

Implement Optimistic Concurrency Control in routines that modify or delete existing business entities:

  • Pass the expected row version (either the xmin system column value or a dedicated integer/bigint version column such as p_rea_versioon / p_version) as an input parameter to the routine.

  • Include the version check in the WHERE clause of the UPDATE or DELETE statement (e.g., WHERE id = p_id AND rea_versioon = p_rea_versioon), and increment the version column during updates (SET rea_versioon = rea_versioon + 1). If zero rows are affected, raise an exception or return an indicator of a concurrent modification conflict.

Categories

This query is classified under the following categories:

User-defined routines

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

NameDescription
User-defined routinesQueries of this category provide information about the user-defined routines