| Goal | This query identifies row-level BEFORE UPDATE triggers that update a row's modification timestamp column (e.g., NEW.viimase_muutm_aeg := CURRENT_TIMESTAMP(0);), but lack a WHEN (OLD.* IS DISTINCT FROM NEW.*) condition to verify that the row data actually changed. Without this condition, executing a no-op UPDATE statement (where the new column values are identical to the existing values) still fires the trigger and overwrites the modification timestamp. This corrupts audit accuracy by recording a false modification time and forces PostgreSQL to perform unnecessary physical tuple writes, WAL logging, and index maintenance for a row whose business data did not change. |
|---|---|
| Notes | Queries the PostgreSQL system catalogs (pg_trigger, pg_proc, pg_class, pg_namespace) and information_schema views to inspect row-level BEFORE UPDATE triggers. The query looks for trigger routines that assign a value to a modification-time attribute on the NEW record (matching muutm, update, change, or modif), where neither the trigger's WHEN clause (action_condition) nor the routine body compares OLD.* and NEW.* using IS DISTINCT FROM, <>, or !=. In the returned routine definition, newline characters are replaced with HTML line break (<br>) tags for better readability in a web browser. |
| Type | Problem detection Each row in the result could represent a flaw in the design |
| Reliability | Medium Medium number of false-positive results |
| License | MIT (opens in new tab) |
| Data Source | INFORMATION_SCHEMA+system catalog |
| SQL Query |
|
Fixing Suggestion
Add a
WHEN (OLD.* IS DISTINCT FROM NEW.*) clause to the CREATE TRIGGER definition so that the trigger function is invoked and the modification timestamp is updated only when at least one column value in the row has genuinely changed. 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:
Result quality depends on names
Queries of this category use names (for instance, column names) to try to guess the meaning of a database object. Thus, the goodness of names determines the number of false positive and false negative results.
Triggers and rules
Queries of this category provide information about triggers and rules in a database.
| Name | Description |
|---|---|
| Result quality depends on names | Queries of this category use names (for instance, column names) to try to guess the meaning of a database object. Thus, the goodness of names determines the number of false positive and false negative results. |
| Triggers and rules | Queries of this category provide information about triggers and rules in a database. |