PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 16 queries.

  • All the queries about database objects contain a subcondition to exclude from the result information about the system catalog.
  • Although the statements use SQL constructs (common table expressions; NOT in subqueries) that could cause performance problems in case of large datasets it shouldn't be a problem in case of relatively small amount of data, which is in the system catalog of a database.
  • Statistics about the catalog content and project home in GitHub that has additional information.

# Name ▲ Goal Type Data source Last update License
1 Deferrable constraints Find all deferrable constraints. General system catalog base tables only 2025-11-07 10:11 MIT License View
2 Deferrable foreign key constraint with a RESTRICT compensating action Find deferrable foreign key constraint with ON UPDATE RESTRICT or ON DELETE RESTRICT compensating action. Referential actions are carried out before, and are not part of, the checking of a referential constraint. Deferring a referential constraint defers the checking of the of the constraint (a ) but does not defer the referential actions of the referential constraint. In PostgreSQL the essential difference between NO ACTION and RESTRICT is that NO ACTION allows the check to be deferred until later in the transaction, whereas RESTRICT does not. Thus RESTRICT could result with the failure of data modification where in case of NO ACTION the modification would succeed. Problem detection system catalog base tables only 2025-11-07 10:11 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-12 19:00 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-13 11:16 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-13 11:16 MIT License View
7 Functions that have transactional control Find functions that contain transactional control statements (BEGIN, START TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT) in their body. PostgreSQL does not permit transaction control in functions. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
8 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
9 No point to have in a procedure COMMIT without ROLLBACK or vice versa If you end transaction in a procedure, then there should be a possibility to either commit or rollback the transaction based on some condition. Procedures appeared in PostgreSQL 11. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
10 Procedures cannot have START TRANSACTION and SAVEPOINT You cannot use a START TRANSACTION or a SAVEPOINT statement in a procedure. Procedures appeared in PostgreSQL 11. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
11 Redundant explicit locking in initially deferred constraint triggers This query identifies INITIALLY DEFERRED constraint triggers that utilize explicit locking mechanisms (e.g., LOCK TABLE, SELECT ... FOR SHARE). Deferred constraints are evaluated at transaction commit time, by which point the database engine automatically manages the necessary data consistency states. Consequently, acquiring manual locks within these triggers is technically redundant. Furthermore, it introduces performance risks by enforcing serialization at the critical end-stage of the transaction, potentially increasing the likelihood of deadlocks and reducing system throughput. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-10 13:29 MIT License View
12 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-10 13:18 MIT License View
13 SECURITY DEFINER procedures cannot end transactions You cannot use COMMIT and ROLLBACK in a SECURITY DEFINER procedure. Procedures appeared in PostgreSQL 11. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
14 Triggers with SELECT (i.e., probably check data based on another table) If a trigger is used for enforcing a constraint, then it should take into account that due to the implementation of multiversion concurrency control (MVCC) in PostgreSQL, reading data does not block data modification and vice versa. Thus, there may be a need to lock the entire table or some row explicitly. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
15 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
16 User-defined routines that read data Find user-defined routines that contain SELECT … FROM or PERFORM … FROM operations. PostgreSQL uses multiversion concurrency control (MVCC). Therefore, SELECTs do not block modifications and vice versa. One has to take steps to achieve correct behaviour of data access code. In addition, one should not ask data with multiple queries if it is possible to achieve the result with only one query. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View