Query goal: | Avoid using IS NOT DISTINCT FROM because it makes the query planner to avoid using an index. |
Query type: | Problem detection (Each row in the result could represent a flaw in the design) |
Query reliability: | High (Few or no false-positive results) |
Query license: | MIT License |
Data source: | INFORMATION_SCHEMA+system catalog |
SQL query: | Click on query to copy it
SELECT pg_namespace.nspname AS routine_schema, pg_proc.proname AS routine_name, pg_get_function_identity_arguments(pg_proc.oid) AS parameters, CASE WHEN pg_proc.prokind='f' THEN 'FUNCTION' WHEN pg_proc.prokind='p' THEN 'PROCEDURE' WHEN pg_proc.prokind='w' THEN 'WINDOW FUNCTION' END AS routine_type, regexp_replace(pg_get_functiondef(pg_proc.oid),'[\r\n]','<br>','g') AS routine_src FROM pg_catalog.pg_proc, pg_catalog.pg_namespace WHERE pg_proc.pronamespace = pg_namespace.oid AND pg_proc.prokind<>'a' AND pg_proc.proname NOT IN ('f_assume_you_must_use_files', 'f_check_format_comma_separated_list', 'f_check_password', 'f_default_value_with_no_match') AND pg_get_functiondef(pg_proc.oid)~*'^.*is[[:space:]]+not[[:space:]]+distinct[[:space:]]+from.*$' AND pg_namespace.nspname NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL) ORDER BY routine_schema, routine_name; |
Collection name | Collection description |
---|---|
Find problems automatically | Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not . |
Find problems by overview | Queries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not . |
Category name | Category description |
---|---|
Performance | Queries of this category provide information about indexes in a database. |
User-defined routines | Queries of this category provide information about the user-defined routines |
Reference |
---|
https://philbooth.me/blog/nine-ways-to-shoot-yourself-in-the-foot-with-postgresql (problem 9) |
https://modern-sql.com/caniuse/is-distinct-from |