Filter Queries

Found 1077 queries.

Page 35 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.

#681. Redundant explicit locking in initially deferred constraint triggers

INFORMATION_SCHEMA+system catalog base tables

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

#682. Redundant leading or trailing wildcards in regular expressions

INFORMATION_SCHEMA+system catalog base tables

This query identifies regular expressions that contain superfluous .* constructs at the beginning or end of the pattern. In standard unanchored regular expression matching (as performed by PostgreSQL's ~ operator), the engine checks for the pattern's existence anywhere within the string by default. Therefore, a leading or trailing .* is typically redundant, as it explicitly instructs the engine to do what it would already be doing implicitly. Removing these unnecessary wildcards improves the readability and maintainability of the expression and can, in some cases, lead to better performance by reducing unnecessary backtracking.

Problem detection License: MIT (opens in new tab)

#683. Redundant sequence usage privileges (missing table insert rights)

INFORMATION_SCHEMA+system catalog base tables

This query identifies a logical mismatch in access control lists (ACLs). It flags cases where a role is granted USAGE privilege on a sequence (allowing the generation of values via nextval) but lacks the INSERT privilege on the table associated with that sequence. Since the primary purpose of such a sequence is to generate surrogate keys for new rows, possessing the right to generate IDs without the right to insert rows renders the sequence privilege functionally useless. This violates the principle of least privilege and should be revoked to minimize the attack surface.

Problem detection License: MIT (opens in new tab)

#684. Regular expression with possibly a LIKE pattern

INFORMATION_SCHEMA+system catalog base tables

Find expressions that use a regular expression with a like predicate pattern.

Problem detection License: MIT (opens in new tab)

#685. Routine body has keywords that are not in uppercase

INFORMATION_SCHEMA+system catalog base tables

Keywords in uppercase improve readability.

Problem detection License: MIT (opens in new tab)

#686. Routine body only in uppercase

INFORMATION_SCHEMA+system catalog base tables

Uppercase means screaming and having code entirely in uppercase makes its reading more difficult. On the other hand, it would be a good idea to have keywords in uppercase. Find routines that body contains a SQL data manipulation statement (which shouldn't be entirely in uppercase) but still the body is completely in uppercase.

Problem detection License: MIT (opens in new tab)

#687. Routine body with ordering the query result based on positional references

INFORMATION_SCHEMA+system catalog base tables

Find routines where the query result is sorted based on the column number in the SELECT clause. Such query is sensitive towards changing the order of columns in the SELECT clause, i.e., if one changes the order of columns in the SELECT clause, then one must change the numbers in the ORDER BY clause as well, otherwise the query will produce undesired order of rows.

Problem detection License: MIT (opens in new tab)

#688. Routine for reading data uses another routine to read some data

INFORMATION_SCHEMA+system catalog base tables

This query analyzes the call graph of user-defined routines to identify nested data retrieval patterns. It flags routines that are operationally read-only (performing no DML) but rely on invoking other routines to access additional data. This indicates a layered architecture where data access logic is encapsulated and chained. Identifying these routines is essential for performance profiling, as the total cost of execution is distributed across the call stack rather than being contained within a single procedure body.

Problem detection License: MIT (opens in new tab)

#689. Routines lacking explicit locking (MVCC risks)

INFORMATION_SCHEMA+system catalog base tables

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

#690. Routines that can be invoked with a variable number of arguments

INFORMATION_SCHEMA+system catalog base tables

Find routines with a VARIADIC parameter. These are routines that take as input an undefined number of arguments where the argument that is an undefined number are all of the same type and are the last input arguments.

General License: MIT (opens in new tab)

#691. Routines that use old syntax for limiting rows

INFORMATION_SCHEMA+system catalog base tables

This query identifies PL/pgSQL and SQL routines with no SQL-standard bodies that use the non-standard LIMIT clause for row limitation. It flags these routines because the official, cross-platform SQL standard specifies FETCH FIRST n ROWS ONLY for this purpose. Adhering to the standard improves code portability and maintainability. To ensure relevance, the query intelligently excludes routines that are part of installed extensions, focusing only on user-defined code.

Problem detection License: MIT (opens in new tab)

#692. Routines with BOOLEAN return type that do not have a good name

INFORMATION_SCHEMA+system catalog base tables

This query audits the naming conventions of routines that return a BOOLEAN data type. It identifies routines that do not adhere to the recommended semantic prefix convention (i.e., starting with is_, has_, can_, or on_). A function name should represent a state or a question (e.g., has_rights) rather than an action (e.g., check_rights). Enforcing this standard makes the routine's purpose and return value immediately obvious from its name.

Problem detection License: MIT (opens in new tab)

#693. Routines with INSERT statements that are sensitive towards the order of columns

INFORMATION_SCHEMA+system catalog base tables

INSERT statements shouldn't be sensitive towards the order of columns. If one changes the order of columns in a table then these statements must be rewritten. Otherwise the code will not work or works incorrectly.

Problem detection License: MIT (opens in new tab)

#694. Routines with non-deterministic side effects and static return values

INFORMATION_SCHEMA+system catalog base tables

This query identifies SQL routines that exhibit a dangerous combination of state-changing side effects (DML) and a static return value (either a constant literal or an unmodified input parameter). The function's name and signature often imply that the return value is the result of its operations (e.g., a new balance, a generated ID). However, the static return value contradicts this, creating a semantic disconnect between the routine's name and its contract. This is a significant design flaw that can lead to subtle but critical bugs, as the calling code may act on a return value that does not accurately reflect the database state after the routine's execution.

Problem detection License: MIT (opens in new tab)

#695. Routines without an action

INFORMATION_SCHEMA+system catalog base tables

Find routines that body does not contain any action.

Problem detection License: MIT (opens in new tab)

#696. Routines with the same name and parameters in different schemas

INFORMATION_SCHEMA+system catalog base tables

Find user-defined routines with the same name and parameters (including the order of parameters) in different schemas.

General License: MIT (opens in new tab)

#697. Routines with type casting

INFORMATION_SCHEMA+system catalog base tables

Make sure that your parameters have appropriate types in order to avoid unnecessary type casting.

General License: MIT (opens in new tab)

#698. ROW level BEFORE DELETE and INSTEAD OF DELETE triggers that procedures refer to the row variable NEW

INFORMATION_SCHEMA+system catalog base tables

Do not write incorrect code. Variable NEW: "Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is null in statement-level triggers and for DELETE operations." (PostgreSQL documentation)

Problem detection License: MIT (opens in new tab)

#699. ROW level BEFORE INSERT and INSTEAD OF INSERT triggers that procedures refer to the row variable OLD

INFORMATION_SCHEMA+system catalog base tables

Do not write incorrect code. Variable OLD: "Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is null in statement-level triggers and for INSERT operations." (PostgreSQL documentation)

Problem detection License: MIT (opens in new tab)

#700. Row-level BEFORE triggers on base tables with RETURN NULL cancellation logic

INFORMATION_SCHEMA+system catalog base tables

This query identifies row-level BEFORE triggers on base tables that execute a RETURN NULL statement without raising a corresponding exception. In PostgreSQL, returning NULL from a BEFORE trigger silently aborts the pending INSERT, UPDATE, or DELETE operation for the current row. Unlike an exception, which alerts the calling application to the failure, a silent cancellation allows the transaction to proceed as if successful, but with the data modification discarded. This behavior is often unintentional (e.g., a forgotten RETURN NEW) and poses a significant risk of data loss and difficult-to-debug application logic errors.

Problem detection License: MIT (opens in new tab)
# Name(activate to sort ascending) Goal(activate to sort ascending) Type(activate to sort ascending) Data source(sorted ascending, activate to sort descending) Last update(activate to sort ascending) License Actions
681 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 MIT (opens in new tab) View
682 Redundant leading or trailing wildcards in regular expressions This query identifies regular expressions that contain superfluous .* constructs at the beginning or end of the pattern. In standard unanchored regular expression matching (as performed by PostgreSQL's ~ operator), the engine checks for the pattern's existence anywhere within the string by default. Therefore, a leading or trailing .* is typically redundant, as it explicitly instructs the engine to do what it would already be doing implicitly. Removing these unnecessary wildcards improves the readability and maintainability of the expression and can, in some cases, lead to better performance by reducing unnecessary backtracking. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
683 Redundant sequence usage privileges (missing table insert rights) This query identifies a logical mismatch in access control lists (ACLs). It flags cases where a role is granted USAGE privilege on a sequence (allowing the generation of values via nextval) but lacks the INSERT privilege on the table associated with that sequence. Since the primary purpose of such a sequence is to generate surrogate keys for new rows, possessing the right to generate IDs without the right to insert rows renders the sequence privilege functionally useless. This violates the principle of least privilege and should be revoked to minimize the attack surface. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
684 Regular expression with possibly a LIKE pattern Find expressions that use a regular expression with a like predicate pattern. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
685 Routine body has keywords that are not in uppercase Keywords in uppercase improve readability. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
686 Routine body only in uppercase Uppercase means screaming and having code entirely in uppercase makes its reading more difficult. On the other hand, it would be a good idea to have keywords in uppercase. Find routines that body contains a SQL data manipulation statement (which shouldn't be entirely in uppercase) but still the body is completely in uppercase. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
687 Routine body with ordering the query result based on positional references Find routines where the query result is sorted based on the column number in the SELECT clause. Such query is sensitive towards changing the order of columns in the SELECT clause, i.e., if one changes the order of columns in the SELECT clause, then one must change the numbers in the ORDER BY clause as well, otherwise the query will produce undesired order of rows. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
688 Routine for reading data uses another routine to read some data This query analyzes the call graph of user-defined routines to identify nested data retrieval patterns. It flags routines that are operationally read-only (performing no DML) but rely on invoking other routines to access additional data. This indicates a layered architecture where data access logic is encapsulated and chained. Identifying these routines is essential for performance profiling, as the total cost of execution is distributed across the call stack rather than being contained within a single procedure body. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
689 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 MIT (opens in new tab) View
690 Routines that can be invoked with a variable number of arguments Find routines with a VARIADIC parameter. These are routines that take as input an undefined number of arguments where the argument that is an undefined number are all of the same type and are the last input arguments. General INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
691 Routines that use old syntax for limiting rows This query identifies PL/pgSQL and SQL routines with no SQL-standard bodies that use the non-standard LIMIT clause for row limitation. It flags these routines because the official, cross-platform SQL standard specifies FETCH FIRST n ROWS ONLY for this purpose. Adhering to the standard improves code portability and maintainability. To ensure relevance, the query intelligently excludes routines that are part of installed extensions, focusing only on user-defined code. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
692 Routines with BOOLEAN return type that do not have a good name This query audits the naming conventions of routines that return a BOOLEAN data type. It identifies routines that do not adhere to the recommended semantic prefix convention (i.e., starting with is_, has_, can_, or on_). A function name should represent a state or a question (e.g., has_rights) rather than an action (e.g., check_rights). Enforcing this standard makes the routine's purpose and return value immediately obvious from its name. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
693 Routines with INSERT statements that are sensitive towards the order of columns INSERT statements shouldn't be sensitive towards the order of columns. If one changes the order of columns in a table then these statements must be rewritten. Otherwise the code will not work or works incorrectly. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
694 Routines with non-deterministic side effects and static return values This query identifies SQL routines that exhibit a dangerous combination of state-changing side effects (DML) and a static return value (either a constant literal or an unmodified input parameter). The function's name and signature often imply that the return value is the result of its operations (e.g., a new balance, a generated ID). However, the static return value contradicts this, creating a semantic disconnect between the routine's name and its contract. This is a significant design flaw that can lead to subtle but critical bugs, as the calling code may act on a return value that does not accurately reflect the database state after the routine's execution. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
695 Routines without an action Find routines that body does not contain any action. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
696 Routines with the same name and parameters in different schemas Find user-defined routines with the same name and parameters (including the order of parameters) in different schemas. General INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
697 Routines with type casting Make sure that your parameters have appropriate types in order to avoid unnecessary type casting. General INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
698 ROW level BEFORE DELETE and INSTEAD OF DELETE triggers that procedures refer to the row variable NEW Do not write incorrect code. Variable NEW: "Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is null in statement-level triggers and for DELETE operations." (PostgreSQL documentation) Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
699 ROW level BEFORE INSERT and INSTEAD OF INSERT triggers that procedures refer to the row variable OLD Do not write incorrect code. Variable OLD: "Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is null in statement-level triggers and for INSERT operations." (PostgreSQL documentation) Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
700 Row-level BEFORE triggers on base tables with RETURN NULL cancellation logic This query identifies row-level BEFORE triggers on base tables that execute a RETURN NULL statement without raising a corresponding exception. In PostgreSQL, returning NULL from a BEFORE trigger silently aborts the pending INSERT, UPDATE, or DELETE operation for the current row. Unlike an exception, which alerts the calling application to the failure, a silent cancellation allows the transaction to proceed as if successful, but with the data modification discarded. This behavior is often unintentional (e.g., a forgotten RETURN NEW) and poses a significant risk of data loss and difficult-to-debug application logic errors. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View