Re: Clarification of triggers with 'OF column_name' in 'Overview of Trigger Behavior' - Mailing list pgsql-docs

From Josh Silver
Subject Re: Clarification of triggers with 'OF column_name' in 'Overview of Trigger Behavior'
Date
Msg-id CADRdQa0ow74BwKDcpU-MCYcRJfQER5mZ+phL4iJWABRHgkXraw@mail.gmail.com
Whole thread Raw
In response to Re: Clarification of triggers with 'OF column_name' in 'Overview of Trigger Behavior'  ("David G. Johnston" <david.g.johnston@gmail.com>)
List pgsql-docs


On Mon, May 2, 2022 at 4:53 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Mon, May 2, 2022 at 4:42 PM Josh Silver <josh.ag@paladin.insure> wrote:
On Mon, May 2, 2022 at 3:22 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Mon, May 2, 2022 at 11:14 AM PG Doc comments form <noreply@postgresql.org> wrote:
Again, that isn't how this works.  The column specific trigger is only "checked against" the SQL Command "UPDATE tbl SET col" ...
 
I feel like I'm completely not getting the point you are trying to make here because if I edit my statement to be more technically correct,
Unlike the WHEN clause which is checked right before the function executes and which evaluates against the return value of the previous trigger function, the column specific trigger is only checked against the "checked against" the SQL Command "UPDATE tbl SET col".
I don't see how that alters my point, which is to document this difference since a column specific trigger and a when clause that checks if a column has changed can have very different behavior.
 
Introducing such a concept to the documentation doesn't seem like a good solution.

I'm not suggesting to introduce it as I've described. I'm suggesting documenting the behavior already described in the Notes section of the CREATE TRIGGER page on this page as well, just like how the WHEN CLAUSE's behavior is already documented on both pages.

The CREATE trigger page NOTES says of before triggers

In a BEFORE trigger, the WHEN condition is evaluated just before the function is or would be executed, so using WHEN is not materially different from testing the same condition at the beginning of the trigger function. Note in particular that the NEW row seen by the condition is the current value, as possibly modified by earlier triggers. Also, a BEFORE trigger's WHEN condition is not allowed to examine the system columns of the NEW row (such as ctid), because those won't have been set yet.

 and of column specific triggers
 A column-specific trigger (one defined using the UPDATE OF column_name syntax) will fire when any of its columns are listed as targets in the UPDATE command's SET list. It is possible for a column's value to change even when the trigger is not fired, because changes made to the row's contents by BEFORE UPDATE triggers are not considered. Conversely, a command such as UPDATE ... SET x = x ... will fire a trigger on column x, even though the column's value did not change.
 
and the  'Overview of Trigger Behavior pages says of before triggers
In row-level triggers the WHEN condition can examine the old and/or new values of columns of the row. (Statement-level triggers can also have WHEN conditions, although the feature is not so useful for them.) In a BEFORE trigger, the WHEN condition is evaluated just before the function is or would be executed, so using WHEN is not materially different from testing the same condition at the beginning of the trigger function.
 
but does not even mention column specific triggers by name.
 
Josh

pgsql-docs by date:

Previous
From: "David G. Johnston"
Date:
Subject: Re: Clarification of triggers with 'OF column_name' in 'Overview of Trigger Behavior'
Next
From: "David G. Johnston"
Date:
Subject: Re: Clarification of triggers with 'OF column_name' in 'Overview of Trigger Behavior'