Filter Queries

Found 1073 queries.

Page 17 of 54
  • 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 the relatively small amount of data in the system catalog of a database.

#321. Duplication of case insensitivity specification in a regular expression

INFORMATION_SCHEMA+system catalog base tables

Find regular expressions that use both case insensitive search operator ~* and case insensitivity modifier (?i).

Problem detection License: MIT (opens in new tab)

#322. Duplication of parent table CHECK constraints on the foreign key columns

INFORMATION_SCHEMA+system catalog base tables

Find duplicate constraints, which make it more difficult to maintain the constraints. Do remember that the same task can be solved in SQL usually in multiple different ways. Thus, the exact copies are not the only possible duplication.

Problem detection License: MIT (opens in new tab)

#323. Duplication of simple CHECK constraints on the same column

INFORMATION_SCHEMA+system catalog base tables

Find duplication of simple CHECK constraints on the same base table or foreign table column. Duplication of the same constraint means that if one starts to manage the code, then changes have to be made in multiple places. The problem is essentially similar with the data redundancy problem that database normalization tries to reduce. Do remember that the same task can be solved in SQL usually in multiple different ways. Thus, the exact copies are not the only possible duplication.

Problem detection License: MIT (opens in new tab)

#324. Dynamic values in primary error messages

INFORMATION_SCHEMA+system catalog base tables

This query identifies PL/pgSQL routines that invoke RAISE EXCEPTION and embed dynamic values directly into the primary error message string. This is considered an anti-pattern because it generates a unique error string for every failure, which complicates log aggregation and client-side error parsing. The primary error message should always be static and predictable.

Problem detection License: MIT (opens in new tab)

#325. Each table is both referencing and referenced table (perhaps there is a cycle in relationships)

INFORMATION_SCHEMA+system catalog base tables

Find as to whether the relationships between tables form a complete bidirected graph. Tables are vertices in the graph. There is a directed edge between two vertices if one of the tables refers to another through foreign key relationship on mandatory columns.

Problem detection License: MIT (opens in new tab)

#326. Empty columns

INFORMATION_SCHEMA+system catalog base tables

Find columns in non-empty tables that do not contain any values. If there are no values in a columns, then it may mean that one hasn't tested constraints that have been declared to the column or implemented by using triggers. It could also mean that such columns are not needed at all.

Problem detection License: MIT (opens in new tab)

#327. Empty schemas

INFORMATION_SCHEMA+system catalog base tables

This query identifies empty schemas within the database. A schema is considered empty if it exists as a namespace but contains no database objects, such as tables, views, functions, or types. The presence of such schemas often indicates artifacts from failed or incomplete migrations, obsolete application components, or setup errors, and they can be safely removed to reduce schema clutter.

Problem detection License: MIT (opens in new tab)

#328. Empty tables

system catalog base tables only

Find base tables where the number of rows is zero. If there are no rows in a table, then it may mean that one hasn't tested constraints that have been declared to the table or implemented by using triggers. It could also mean that the table is not needed because there is no data that should be registered in the table.

Problem detection License: MIT (opens in new tab)

#329. Enumerated or range types with the same name in different schemas

INFORMATION_SCHEMA+system catalog base tables

This query enforces the Don't Repeat Yorself principle across the database's type system. It identifies ENUM and RANGE types that share the same name but exist in different schemas. This indicates that a conceptual data type has been defined multiple times instead of having a single, canonical definition in a shared schema. Such duplication leads to maintenance overhead and the risk of semantic divergence over time.

Problem detection License: MIT (opens in new tab)

#330. Enumerated types with zero or one value

INFORMATION_SCHEMA+system catalog base tables

Fidn enumerated types with zero or one value. Type is a named finite set of values. The empty set is a set. A set with one value is a set. Thus, types with zero or one value are legal. In practical terms each type, usually, should contain at least two values.

Problem detection License: MIT (opens in new tab)

#331. Excessive classifier name length

INFORMATION_SCHEMA+system catalog base tables

This query identifies classifier tables where the name column allows strings longer than 100 symbols. Such long names are difficult to read and can cause layout issues in the user interface.

Problem detection License: MIT (opens in new tab)

#332. Excessive data types for classifier codes

INFORMATION_SCHEMA only

This query identifies state, type or category code columns that use unnecessarily large numeric data types, specifically integer or bigint. It locates these columns by matching specific naming patterns in English and Estonian. Since reference tables typically contain a limited number of rows, these code columns should ideally be defined as smallint to optimize storage space and improve performance.

Problem detection License: MIT (opens in new tab)

#333. Excessive locking with FOR UPDATE in subqueries

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#334. Excessive privileges on databases, schemas, domains, types, languages, foreign data wrappers, and foreign servers

system catalog base tables only

Find excessive privileges on databases, schemas, domains, collations, sequences, foreign data wrappers, and foreign servers that are probably not needed by a typical application.

Problem detection License: MIT (opens in new tab)

#335. Exclude constraint instead of simple UNIQUE

system catalog base tables only

Find exclude constraints that implement a simple UNIQUE constraint. The checking might be slower compared to UNIQUE constraint.

Problem detection License: MIT (opens in new tab)

#336. Exclude constraint to prevent overlapping time periods

INFORMATION_SCHEMA+system catalog base tables

Find exclude constraints on base tables with multiple date/timestamp columns that prevent overlapping time periods.

General License: MIT (opens in new tab)

#337. Exclusion constraints using upper() instead of lower()

system catalog base tables only

This query identifies exclusion constraints that apply the upper() function to a column. In PostgreSQL, it is a strong community convention to standardize on the lower() function for case-insensitive constraints, indexing, and comparisons. Enforcing this convention ensures consistency across the schema and prevents logical or performance issues (for example, if a developer queries or validates data using lower(col) but the underlying exclusion constraint's index is built on upper(col)).

Problem detection License: MIT (opens in new tab)

#338. Explicit execute grants for application roles are missing

INFORMATION_SCHEMA+system catalog base tables

After revoking default PUBLIC execution rights, you must explicitly grant the EXECUTE privilege on routines only to the specific users or roles that correspond to your applications. This enforces the principle of least privilege by ensuring that database functions and procedures are accessible exclusively by dedicated service accounts, rather than being open to all database users.

Problem detection License: MIT (opens in new tab)

#339. Explicit locking

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#340. Explicit locking is missing

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)
# Name(sorted ascending, activate to sort descending) Goal(activate to sort ascending) Type(activate to sort ascending) Data source(activate to sort ascending) Last update(activate to sort ascending) License Actions
321 Duplication of case insensitivity specification in a regular expression Find regular expressions that use both case insensitive search operator ~* and case insensitivity modifier (?i). Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
322 Duplication of parent table CHECK constraints on the foreign key columns Find duplicate constraints, which make it more difficult to maintain the constraints. Do remember that the same task can be solved in SQL usually in multiple different ways. Thus, the exact copies are not the only possible duplication. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
323 Duplication of simple CHECK constraints on the same column Find duplication of simple CHECK constraints on the same base table or foreign table column. Duplication of the same constraint means that if one starts to manage the code, then changes have to be made in multiple places. The problem is essentially similar with the data redundancy problem that database normalization tries to reduce. Do remember that the same task can be solved in SQL usually in multiple different ways. Thus, the exact copies are not the only possible duplication. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
324 Dynamic values in primary error messages This query identifies PL/pgSQL routines that invoke RAISE EXCEPTION and embed dynamic values directly into the primary error message string. This is considered an anti-pattern because it generates a unique error string for every failure, which complicates log aggregation and client-side error parsing. The primary error message should always be static and predictable. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
325 Each table is both referencing and referenced table (perhaps there is a cycle in relationships) Find as to whether the relationships between tables form a complete bidirected graph. Tables are vertices in the graph. There is a directed edge between two vertices if one of the tables refers to another through foreign key relationship on mandatory columns. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
326 Empty columns Find columns in non-empty tables that do not contain any values. If there are no values in a columns, then it may mean that one hasn't tested constraints that have been declared to the column or implemented by using triggers. It could also mean that such columns are not needed at all. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
327 Empty schemas This query identifies empty schemas within the database. A schema is considered empty if it exists as a namespace but contains no database objects, such as tables, views, functions, or types. The presence of such schemas often indicates artifacts from failed or incomplete migrations, obsolete application components, or setup errors, and they can be safely removed to reduce schema clutter. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
328 Empty tables Find base tables where the number of rows is zero. If there are no rows in a table, then it may mean that one hasn't tested constraints that have been declared to the table or implemented by using triggers. It could also mean that the table is not needed because there is no data that should be registered in the table. Problem detection system catalog base tables only MIT (opens in new tab) View
329 Enumerated or range types with the same name in different schemas This query enforces the Don't Repeat Yorself principle across the database's type system. It identifies ENUM and RANGE types that share the same name but exist in different schemas. This indicates that a conceptual data type has been defined multiple times instead of having a single, canonical definition in a shared schema. Such duplication leads to maintenance overhead and the risk of semantic divergence over time. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
330 Enumerated types with zero or one value Fidn enumerated types with zero or one value. Type is a named finite set of values. The empty set is a set. A set with one value is a set. Thus, types with zero or one value are legal. In practical terms each type, usually, should contain at least two values. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
331 Excessive classifier name length This query identifies classifier tables where the name column allows strings longer than 100 symbols. Such long names are difficult to read and can cause layout issues in the user interface. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
332 Excessive data types for classifier codes This query identifies state, type or category code columns that use unnecessarily large numeric data types, specifically integer or bigint. It locates these columns by matching specific naming patterns in English and Estonian. Since reference tables typically contain a limited number of rows, these code columns should ideally be defined as smallint to optimize storage space and improve performance. Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View
333 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 MIT (opens in new tab) View
334 Excessive privileges on databases, schemas, domains, types, languages, foreign data wrappers, and foreign servers Find excessive privileges on databases, schemas, domains, collations, sequences, foreign data wrappers, and foreign servers that are probably not needed by a typical application. Problem detection system catalog base tables only MIT (opens in new tab) View
335 Exclude constraint instead of simple UNIQUE Find exclude constraints that implement a simple UNIQUE constraint. The checking might be slower compared to UNIQUE constraint. Problem detection system catalog base tables only MIT (opens in new tab) View
336 Exclude constraint to prevent overlapping time periods Find exclude constraints on base tables with multiple date/timestamp columns that prevent overlapping time periods. General INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
337 Exclusion constraints using upper() instead of lower() This query identifies exclusion constraints that apply the upper() function to a column. In PostgreSQL, it is a strong community convention to standardize on the lower() function for case-insensitive constraints, indexing, and comparisons. Enforcing this convention ensures consistency across the schema and prevents logical or performance issues (for example, if a developer queries or validates data using lower(col) but the underlying exclusion constraint's index is built on upper(col)). Problem detection system catalog base tables only MIT (opens in new tab) View
338 Explicit execute grants for application roles are missing After revoking default PUBLIC execution rights, you must explicitly grant the EXECUTE privilege on routines only to the specific users or roles that correspond to your applications. This enforces the principle of least privilege by ensuring that database functions and procedures are accessible exclusively by dedicated service accounts, rather than being open to all database users. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
339 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 MIT (opens in new tab) View
340 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 MIT (opens in new tab) View