Seq nr | Name | Goal▲ | Type | Data source | Last update | License | ... |
---|---|---|---|---|---|---|---|
761 | Invocation of a system-defined routine without providing any arguments | Find user-defined routines that contain an invocation of a system-defined function without providing any argument. The query considers all aggregate functions as well as some popular scalar functions. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-12-22 13:00 | MIT License | |
762 | Unnamed columns in routines | Find user-defined routines that contain a SQL statement that does not give the name to a column in a SQL statement. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-12-22 00:04 | MIT License | |
763 | 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 | 2023-12-21 15:00 | MIT License | |
764 | Updating or deleting data in a routine without restricting rows | Find user-defined routines that contain UPDATE or DELETE statement but do not contain any WHERE clause, meaning that at least one UPDATE or DELETE operation influences all the rows of a table. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-11-29 13:21 | MIT License | |
765 | Consistency of comments of routines | Find user-defined routines that have a comment registered by the COMMENT statement and a comment within the routine body. Make sure that there are no inconsistencies between the comments. | General | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 10:56 | MIT License | |
766 | Explicit locking is missing (2) | Find user-defined routines that have a subquery in a DELETE or UPDATE statement without the FOR UPDATE clause. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-12-06 12:15 | MIT License | |
767 | Are there enough routines that implement database operations? | Find user-defined routines that implement database operations (comment refers to an operation) but show these only if there are at least eight such routines. Contracts of database operations are specified in the system analysis documentation. The contracts apply the idea of design by contract in the field of databases. | General | INFORMATION_SCHEMA+system catalog base tables | 2021-11-03 20:07 | MIT License | |
768 | Incorrect reference to a system-defined function in the routine body | Find user-defined routines that possibly use incorrect name of a system-defined function (currenttimestamp (correct is current_timestamp), currentdate (correct is current_date), currenttime (correct is current_time), local_time (correct is localtime), local_timestamp (correct is localtimestamp),localdate (there is no such function),local_date (there is no such function), sessionuser (correct is session_user), ucase (correct is upper), lcase (correct is lower)). The problem can arise only if the routine uses dynamic SQL. In case of static SQL the DBMS checks the SQL statemen at the creation time and finds out that for instance, SELECT Count(*) AS cnt FROM Emp WHERE hiredate<=currentdate; is incorrect statement because currentdate is not a function name and there is no column currentdate in the table Emp. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-10-25 16:48 | MIT License | |
769 | User-defined routines that use dynamic SQL to execute data manipulation statements | Find user-defined routines that use dynamic SQL to execute data manipulation statements (SELECT, INSERT, UPDATE, DELETE). | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 12:39 | MIT License | |
770 | User-defined routines that use keyword DECLARE but do not declare anything | Find user-defined routines that use keyword DECLARE but do not declare anything. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-05 14:47 | MIT License | |
771 | User-defined routines that use md5 hash for other purposes than generating test data | Find user-defined routines that use md5 hashes for the security purposes. Nowadays such hashes can be calculated too quickly and its use should be avoided at least for hashing passwords. Exclude routines that invoke both md5 function and generate_series function and are thus probably used to generate test data. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-10-31 15:23 | MIT License | |
772 | Not equals check in unstandardized way | Find user-defined routines that use != operator to test as to whether two values are not equal. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-12-13 14:16 | MIT License | |
773 | Too few rounds in case of calculating the hash | Find user-defined routines where gen_salt function is used with the number of rounds that is smaller than 12. The number should be adjusted based on the hardware where the system resides. Password hashing should take at least 250 ms. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-10-25 17:21 | MIT License | |
774 | Duplicate user-defined routines | Find user-defined routines with the exact duplicate body and parameters. There should not be multiple routines with exactly the same body and parameters (name, type). Having such duplicates is redundancy. Do remember that the same task can usually be solved in multiple different ways. Thus, the exact copies of routine bodies are not the only possible duplication. Moreover, it could be that different routines that solve the same task have different parameter names (but the parameters have the same types, ordinal positions, default values or the routines have different order of parameters). Thus, the query does not find all the duplications. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-05 11:59 | MIT License | |
775 | 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 | 2021-10-25 16:11 | MIT License | |
776 | All rules | Find user-defined rules for rewriting data manipulation language statements. Rules should be used only for the tasks that cannot be achieved in a declarative manner, i.e., for example, by declaring a constraint. | General | system catalog base tables only | 2023-12-22 12:30 | MIT License | |
777 | Is does not return a boolean | Find user-defined SQL and PL/pgSQL non-trigger routines that do not return a truth value (for instance, returns an integer or does not return a value at all) although the name suggest that it should return a truth value (TRUE or FALSE). | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-01-06 14:25 | MIT License | |
778 | Transform method does not return | Find user-defined SQL and PL/pgSQL routines that do not return a value although the name suggest that it should return a value (contains "_to_"). | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-01-14 19:37 | MIT License | |
779 | A getter does not return a value | Find user-defined SQL and PL/pgSQL routines that do not return a value although the name suggest that it should return a value (starts with "get"). | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-01-06 14:24 | MIT License | |
780 | Validation method does not confirm | Find user-defined SQL and PL/pgSQL routines that do not return a value although the name suggest that it should return a value (starts with "validate" or "check"). | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-01-06 14:25 | MIT License |