| 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 |
|---|---|
| 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 |
|
Fixing Suggestion
- Pass the expected row version (either the
xminsystem column value or a dedicated integer/bigint version column such asp_rea_versioon/p_version) as an input parameter to the routine. - Include the version check in the
WHEREclause of theUPDATEorDELETEstatement (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
| Name | Description |
|---|---|
| User-defined routines | Queries of this category provide information about the user-defined routines |