From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Josh Silver <josh(dot)ag(at)paladin(dot)insure> |
Cc: | Pg Docs <pgsql-docs(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Clarification of triggers with 'OF column_name' in 'Overview of Trigger Behavior' |
Date: | 2022-05-02 23:53:08 |
Message-ID: | CAKFQuwYYS_yKFFgcZe18UBc6VPVJuVmLbTR20Vb0xCL+N5MCKw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-docs |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Silver | 2022-05-03 01:32:58 | Re: Clarification of triggers with 'OF column_name' in 'Overview of Trigger Behavior' |
Previous Message | Josh Silver | 2022-05-02 23:42:20 | Re: Clarification of triggers with 'OF column_name' in 'Overview of Trigger Behavior' |