Re: Clarification of triggers with 'OF column_name' in 'Overview of Trigger Behavior'

On Mon, May 2, 2022 at 4:42 PM Josh Silver <josh(dot)ag(at)paladin(dot)insure> wrote:

> On Mon, May 2, 2022 at 3:22 PM David G. Johnston <
> david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
>> On Mon, May 2, 2022 at 11:14 AM PG Doc comments form <
>> noreply(at)postgresql(dot)org> wrote:
>>
>>> The following documentation comment has been logged on the website:
>>>
>>> Page: https://www.postgresql.org/docs/13/trigger-datachanges.html
>>> Description:
>>>
>>> UPDATE test SET alpha = 5;
>>>
>>> only trigger b2_ab will fire, even though it will update column beta.
>>
>>
>> Column beta eventually changed but you never issued an UPDATE command
>> with beta in the SET clause. The trigger only care about the later.
>>
>> And in fact triggers on a table should never re-issue an actual command
>> against the same table or you end up with infinite triggers.
>>
>
> I now understand that column specific triggers only consider the set
> clause, but that is not documented on "Overview of Trigger Behavior" and is
> only documented in the notes of the "CREATE TRIGGER" page. It seems like
> useful information that the WHEN clause of a TRIGGER evaluates the return
> value of the previously executed trigger function but the column specifier
> only considers the SET clause.
>
>
>>
>> If i
>>> change the declaration of b3_bc by removing the column list or including
>>> column alpha,
>>
>> things work as I expected and b2_ab cascades to b3_bc.
>>>
>>
>> But that isn't how this works. There is no cascading. As soon as the
>> UPDATE query is planned the set of triggers it is going to trigger is
>> basically known and nothing those triggers do individually will change that
>> (aside from raising an error). All you did by changing b3_bc is get it
>> included in the ordered list of triggers that will be executed each time,
>> and only when, an UPDATE command is executed against the named table.
>>
>
> Cascades was a bad choice of words on my part. Unlike the WHEN clause
> which is checked right before the function executes and which evaluates
> against the return value of the previous trigger function,
>

I hadn't considered that aspect but it makes sense.

> the column specific trigger is only checked against the original NEW row.
>

Again, that isn't how this works. The column specific trigger is only
"checked against" the SQL Command "UPDATE tbl SET col" - if col is listed
the trigger is going to be executed and, when its turn comes, the when
condition, if matched, simply causes a no-op execution path, otherwise the
actual function is executed.

>
> I'm proposing that the "Overview of Trigger Behavior" page include
> information about column specific triggers as well, because they have
> different behavior from how the return value from one BEFORE trigger is the
> input to the next BEFORE trigger and from how the WHEN clause gets checked
> right before statement execution. Both those "see" the effects of
> previously executed BEFORE triggers but column specific triggers don't.
>
>
Maybe, but that isn't technically how it works and you are the first person
I know of that has framed, from a user perspective, trigger execution in
this manner. Introducing such a concept to the documentation doesn't seem
like a good solution. Whether some other rewording or framing is desirable
I have yet to research and form an opinion on.

David J.