Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nlfrA-0003s3-Fa for pgsql-docs@arkaria.postgresql.org; Mon, 02 May 2022 23:53:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nlfr9-0004Ip-8v for pgsql-docs@arkaria.postgresql.org; Mon, 02 May 2022 23:53:31 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nlfr8-0004Gx-Tu for pgsql-docs@lists.postgresql.org; Mon, 02 May 2022 23:53:31 +0000 Received: from mail-ed1-x530.google.com ([2a00:1450:4864:20::530]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nlfr6-0007AE-9Z for pgsql-docs@lists.postgresql.org; Mon, 02 May 2022 23:53:30 +0000 Received: by mail-ed1-x530.google.com with SMTP id be20so18234548edb.12 for ; Mon, 02 May 2022 16:53:27 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=Mg9pqMntulsVR+ghWmkKxMWA1VsHRZwagmMGIUHYOOg=; b=QTQ1qU5NgJXpb17gUj2pzokt9E8FZVxud13L2S7mkwx0rPmvbVhDpyLmb/fv/3sVcc ZYqN8GbMPE+R3iJKPj439GTcXiTP3R32G54WwjY5NiRSXlySvH1PN5BerY+J7PBK3ysO sZ/3tBj3mbPRMj7q2KRQkumxnLzNijibZ4FNUcO/aROtD1+lcTNhHmdGM7/Ya3Jjuzhz sMkER5DnSDLiwKfyn8RWgSMXbpGw0IXc0W6kt9mFCeh+ST4x6btz/gXNUsP4R/O433oc qaJPvbmiQ/9NwFN6c5UJU+XKf2JlW7tqqsXADBMj1cix0nke3SCiaN/BsfBWO6atNYcu KWeA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=Mg9pqMntulsVR+ghWmkKxMWA1VsHRZwagmMGIUHYOOg=; b=p8TOs9nl1LD2Z+uXyl+P9+q8fnsynTpvGY6+8V9Ox7r2MtNGV7D/a47nZGlQ11OW5S AySyhmsK86mhj0nxVaeYIqaWBBcFaGMZwSOzT60RnRtpRnuThf0xUO24cx4jnkARM3RP xJEeuvNikWs7ChqkbTQy0rPq90rign2nlH/VOsMLn9UhiltcavFiuJkdES2eyS5cbMK7 YnhOp0kZAaeOseYhSbQZC6JXZX5RfZtlTFMlKStAkBY0Fc1/ixOHJ2xoArbSOz26eiKU ZwNzYRFUX/rKCheLVxHwtQ036gHQ1KCvq7e9GMhvj/NtZSpsfczFHLhbZnPCSnb9Me0B 9pSA== X-Gm-Message-State: AOAM533xRD5SVaSvBeQ7XnwYyOCrvmUGNjQJWFJuqaKcqR548ZcVzeY6 UnlmBAQAtuVDbWCz5JjJ+nlvxW/S5ZoCehhsXELKHUnf X-Google-Smtp-Source: ABdhPJw8U9HU6hl39tc5lzEJGZvWMLeZuYEPfFOcl/3Ik1iQtKuoZolAVsjiSPbGIAkhFt1RSBPzW566B/vL+0s9z50= X-Received: by 2002:a05:6402:3508:b0:427:b100:b825 with SMTP id b8-20020a056402350800b00427b100b825mr12480252edd.268.1651535606393; Mon, 02 May 2022 16:53:26 -0700 (PDT) MIME-Version: 1.0 References: <165151321896.685.16545084197561479198@wrigleys.postgresql.org> In-Reply-To: From: "David G. Johnston" Date: Mon, 2 May 2022 16:53:08 -0700 Message-ID: Subject: Re: Clarification of triggers with 'OF column_name' in 'Overview of Trigger Behavior' To: Josh Silver Cc: Pg Docs Content-Type: multipart/alternative; boundary="000000000000272bc305de1019fe" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000272bc305de1019fe Content-Type: text/plain; charset="UTF-8" On Mon, May 2, 2022 at 4:42 PM Josh Silver 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: >> >>> 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. --000000000000272bc305de1019fe Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Mon, May 2, 2022 at 4:42 PM Josh Silver <josh.ag@pal= adin.insure> wrote:
On Mon, May 2, 2022 at 3:22 PM David G. Johnston <david.g.johnston@gmail.com&= gt; wrote:
On Mon, May = 2, 2022 at 11:14 AM PG Doc comments form <noreply@postgresql.org> wrote:<= br>
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 =3D 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.=C2=A0 The trigger only care about the later.<= /div>

=
And in fact triggers = on a table should never re-issue an actual command against the same table o= r you end up with infinite triggers.
I now understand that column specific triggers only consid= er the set clause, but that is not documented on "Overview of Trigger = Behavior" and is only documented in the notes of the "CREATE TRIG= GER" page. It seems like useful information that the WHEN clause of a = TRIGGER evaluates the return value of the previously executed trigger funct= ion but the column specifier only considers the SET clause.
=C2=A0

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.=C2=A0 There is no cascading.=C2=A0 As soon a= s 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 tha= t (aside from raising an error).=C2=A0 All you did by changing b3_bc is get= it included in the ordered list of triggers that will be executed each tim= e, and only when, an UPDATE command is executed against the named table.

Cascades was a bad ch= oice of words on my part. Unlike the WHEN clause which is checked right bef= ore the function executes and which evaluates against the return value of t= he previous trigger function,

=
I hadn't considered that aspect but it makes sense.
<= blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l= eft:1px solid rgb(204,204,204);padding-left:1ex">
the column specific trigger is only checked against= the original NEW row.

Again, that isn't how this works.=C2=A0 The column specific trigger is= only "checked against" the SQL Command "UPDATE tbl SET col&= quot; - 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 t= riggers as well, because they have different behavior from how the return v= alue from one BEFORE trigger is the input to the next BEFORE trigger and fr= om how the WHEN clause gets checked right before statement execution. Both = those "see" the effects of previously executed BEFORE triggers bu= t column specific triggers don't.


Maybe, but that isn't technically how it work= s and you are the first person I know of that has framed, from a user persp= ective, trigger execution in this manner.=C2=A0 Introducing such a concept = to the documentation doesn't seem like a good solution.=C2=A0 Whether s= ome other rewording or framing is desirable I have yet to research and form= an opinion on.

David J.

--000000000000272bc305de1019fe--