| Goal | 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. |
|---|---|
| Type | Problem detection Each row in the result could represent a flaw in the design |
| Reliability | High Few or no false-positive results |
| License | MIT (opens in new tab) |
| Data Source | INFORMATION_SCHEMA+system catalog |
| SQL Query |
|
Fixing Suggestion
To resolve this issue, refactor the RAISE EXCEPTION statement. Keep the primary error message as a static, predictable string, and move all dynamic variables (such as the specific problematic data) into the DETAIL clause. For example, instead of RAISE EXCEPTION 'Invalid code %', NEW.course_code; in a trigger routine, use RAISE EXCEPTION 'Invalid code provided' USING DETAIL = format('Problematic code: %s', NEW.course_code);
Collections
This query belongs to the following collections:
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 .
| Name | 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 . |
Categories
This query is classified under the following categories:
Triggers and rules
Queries of this category provide information about triggers and rules in a database.
User-defined routines
Queries of this category provide information about the user-defined routines
| Name | Description |
|---|---|
| Triggers and rules | Queries of this category provide information about triggers and rules in a database. |
| User-defined routines | Queries of this category provide information about the user-defined routines |