| 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 |
|
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 .
| Name | Description |
|---|---|
| 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 . |
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
| Name | Description |
|---|---|
| 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 |
Further reading and related materials: