| 1 |
Derived tables that have a column with the xid type |
Find the derived tables (views and materialized views) that have a column with the xid type, i.e., these use the data from the hidden xmin column of a base table. If one uses optimistic approach for dealing with the concurrent data modifications, then xmin values should be presented by views and used in routines that modify or delete rows. |
General |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 2 |
Derived tables with embedded row locking |
This query identifies derived tables (views and materialized views) whose defining subqueries utilize explicit row locking clauses (e.g., FOR UPDATE, FOR SHARE). Embedding locking semantics within a view definition is considered an architectural anti-pattern. It couples data projection with transaction control, causing simple read operations against the view to unexpectedly acquire locks. This behavior degrades concurrency by blocking other readers and violates the principle that reading a data element should not implicitly block simultaneous access. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-12-10 12:58 |
MIT License |
View |
| 3 |
Excessive locking with FOR UPDATE in subqueries |
This query identifies performance and concurrency bottlenecks caused by excessive locking. It flags INSERT, UPDATE, or DELETE statements that utilize subqueries containing the FOR UPDATE clause. Using FOR UPDATE acquires an exclusive lock, which is semantically inappropriate if the rows in the subquery are merely being read for reference or validation rather than being modified. This practice degrades system concurrency by unnecessarily blocking other transactions. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-12-15 11:26 |
MIT License |
View |
| 4 |
Explicit locking |
This query identifies user-defined routines that employ explicit locking mechanisms to supplement PostgreSQL's default Multi-Version Concurrency Control (MVCC). It detects the presence of table-level locking (LOCK TABLE) or explicit row-level locking clauses (e.g., SELECT ... FOR UPDATE, FOR SHARE). While MVCC generally provides sufficient isolation for concurrent transactions, explicit locking is necessary in specific race-condition scenarios. This inventory assists in auditing concurrency control strategies and detecting potential sources of deadlocks or serialization bottlenecks. |
General |
INFORMATION_SCHEMA+system catalog base tables |
2025-12-10 13:13 |
MIT License |
View |
| 5 |
Explicit locking is missing |
This query identifies concurrency risks in user-defined routines by flagging INSERT, UPDATE, or DELETE statements that utilize subqueries without a FOR SHARE locking clause. Failure to acquire a shared lock on source rows allows concurrent transactions to modify or delete them between the subquery's execution and the outer operation, potentially leading to data inconsistencies. Routines utilizing the xmin system column are excluded, operating on the assumption that they implement Optimistic Concurrency Control (version checking) and therefore do not require pessimistic locking. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-12-15 12:45 |
MIT License |
View |
| 6 |
Explicit locking is missing (2) (ChatGPT version) |
This query identifies concurrency risks in user-defined routines by flagging INSERT, UPDATE, or DELETE statements that utilize subqueries without a FOR SHARE locking clause. Failure to acquire a shared lock on source rows allows concurrent transactions to modify or delete them between the subquery's execution and the outer operation, potentially leading to data inconsistencies. Routines utilizing the xmin system column are excluded, operating on the assumption that they implement Optimistic Concurrency Control (version checking) and therefore do not require pessimistic locking. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-12-15 12:41 |
MIT License |
View |
| 7 |
Invalid explicit locking with aggregate functions |
This query identifies SQL statements that attempt to apply explicit row locking (e.g., FOR SHARE, FOR UPDATE) to the result of an aggregate function (e.g., COUNT(*)). This is a semantic error because locking clauses operate on specific physical rows, whereas aggregate functions return a derived scalar value that is decoupled from the underlying row versions. To correctly enforce a lock, the query must select the specific columns (typically the primary key) of the target rows, rather than a computed aggregate. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-12-10 12:59 |
MIT License |
View |
| 8 |
Routines lacking explicit locking (MVCC risks) |
This query identifies user-defined routines that may be susceptible to concurrency anomalies due to a lack of explicit locking. PostgreSQL utilizes Multi-Version Concurrency Control (MVCC), where SELECT statements do not block data modification operations. Consequently, routines that read data to inform subsequent modifications without acquiring row-level locks (e.g., FOR UPDATE, FOR SHARE) or using isolation levels higher than READ COMMITTED are prone to race conditions. This query flags such routines for review to ensure transactional integrity is maintained. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-12-14 14:44 |
MIT License |
View |
| 9 |
The use of xmin hidden column in views and routines |
Find the number of views and materialized views that have a column with the xid type and the number of routines that contain a UPDATE or a DELETE statement that search condition refers to the xmin column. If one uses optimistic approach for dealing with the concurrent modifications of data, then xmin values should be presented by views and used in routines that modify or delete rows. |
Sofware measure |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 10 |
Trying to lock a value instead of a row |
This query identifies SQL routines that utilize explicit row locking clauses (e.g., FOR UPDATE, FOR SHARE) in queries that do not target a specific base table or relation. For instance, a statement like SELECT 'text' AS v FOR UPDATE attempts to apply a lock to a scalar constant. Since row-level locks in PostgreSQL require a physical row version (tuple) within a table to be effective, such statements are semantically void. They indicate a fundamental misunderstanding of the concurrency control mechanism and should be corrected to target actual table rows. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-12-10 13:00 |
MIT License |
View |
| 11 |
Unnecessary explicit locking in read-only routines |
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 vice versa) for standard query operations. Therefore, routines that perform no data modification (DML) and do not raise exceptions have no functional need to acquire table-level (LOCK TABLE) or row-level (FOR SHARE/UPDATE) locks. Using them in this context provides no benefit while actively degrading system concurrency by blocking other transactions. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-12-10 13:23 |
MIT License |
View |
| 12 |
User-defined routines that use xmin hidden column |
Find routines that contain a UPDATE or a DELETE statement that search condition refers to the xmin column. If one uses optimistic approach for dealing with the concurrent modifications of data, then xmin values should be presented by views and used in routines that modify or delete rows. |
General |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |