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 1nlmgA-0003fn-LG for pgsql-docs@arkaria.postgresql.org; Tue, 03 May 2022 07:10:38 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nlmg8-0003er-U8 for pgsql-docs@arkaria.postgresql.org; Tue, 03 May 2022 07:10:36 +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 1nlfgb-0005qx-3P for pgsql-docs@lists.postgresql.org; Mon, 02 May 2022 23:42:37 +0000 Received: from mail-io1-xd2c.google.com ([2607:f8b0:4864:20::d2c]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nlfgY-00073I-36 for pgsql-docs@lists.postgresql.org; Mon, 02 May 2022 23:42:36 +0000 Received: by mail-io1-xd2c.google.com with SMTP id f4so17579390iov.2 for ; Mon, 02 May 2022 16:42:33 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=paladin.insure; s=google; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=hMRl9josfuzqe4ZgKXWnQMAGO7tWfKkzVThsHWtPcIs=; b=C4lxcrakkd3w2s7/xfLFIziDhsHAfjHPYVhkPvxVT1R7Tk6E7jr5SuEFMSnRir8Da7 ILUX3GR1diY9KF4sulFwFEdp5DYDSDyqUpUWFwMK0oIKS6S66Ic3fe569tKte0p93dao slcyhJrXRn0OeRROR+K/GRJxFsRjP+XGHmpAk= 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=hMRl9josfuzqe4ZgKXWnQMAGO7tWfKkzVThsHWtPcIs=; b=zwsUR5h04RWlpvoQpub229KZz6PIjMudRJe49ORwFcnMyyKvHY/yPxhv5BwKzhv/dE zyQC3g74ua3a9Vxn1iFBYuV7HczBN/N0b//BX//twHhnT4xHfnppHZbDc4h4l4ilmXE6 nJvtH2SVB5ToprOPsL6KQYR79+Q52CJOgLOrOYekgQ+2dRA8LMmwqPsTqogUnQl0t7JQ l8T+/PsVyeIBymf2xNvvWiSsgenyaNCdqtRhMcvDENQIb9adHwb/bF7pd8k50KGjbD8v jxn/bnWZUO8DukMWaZjDSEw/hx8aXmvjN0Hpb0C5cbn1NS9y5BnPPwMv+L+yN6b5Bb8V C3KQ== X-Gm-Message-State: AOAM531WcwoCOWPel1GTBG4GIUcrp65VnonATBECQ57DQylK9YbaGqCP wsKxzBV0JJouQdjkap0ETPWdSTMOxyOYzkCXQK9U6w== X-Google-Smtp-Source: ABdhPJwdgbZ0ujZCp9TY4cajXHHmqb/14v1SLOc+fgQATvpT/AerN83zudfAvpVj4m2RRJ+DI2xZ0xZpMvPy1Tl0CsQ= X-Received: by 2002:a02:62c6:0:b0:32a:b876:218f with SMTP id d189-20020a0262c6000000b0032ab876218fmr6100873jac.240.1651534951609; Mon, 02 May 2022 16:42:31 -0700 (PDT) MIME-Version: 1.0 References: <165151321896.685.16545084197561479198@wrigleys.postgresql.org> In-Reply-To: From: Josh Silver Date: Mon, 2 May 2022 16:42:20 -0700 Message-ID: Subject: Re: Clarification of triggers with 'OF column_name' in 'Overview of Trigger Behavior' To: "David G. Johnston" Cc: Pg Docs Content-Type: multipart/alternative; boundary="000000000000200a8005de0ff215" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000200a8005de0ff215 Content-Type: text/plain; charset="UTF-8" On Mon, May 2, 2022 at 3:22 PM David G. Johnston 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, the column specific trigger is only checked against the original NEW row. I think that the behavior of column-specific triggers, especially their difference in when/what they check against from triggers with a WHEN CLAUSE would be useful to document on this page. > > If you still believe a meaningful change can be had here a concrete > suggestion would be helpful. Otherwise maybe someone takes an interest at > some point - but this particular confusion seems extremely rare. > 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. Josh --000000000000200a8005de0ff215 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Mon, May 2, 2022 at 3:22 PM David G. Johns= ton <david.g.johnston@gmai= l.com> wrote:
On= Mon, May 2, 2022 at 11:14 AM PG Doc comments form <noreply@postgresql.org> wrot= e:
The following documentation comment has been log= ged 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, the column specific trigger is only checked a= gainst the original NEW row. I think that the behavior of column-specific t= riggers, especially their difference in when/what they check against from t= riggers with a WHEN CLAUSE would be useful to document on this page.
<= div>=C2=A0

If you still believe a meaningful change can be had here a concrete sug= gestion would be helpful.=C2=A0 Otherwise maybe someone takes an=C2=A0inter= est at some point - but this particular confusion seems extremely rare.

I'm proposing that the &= quot;Overview of Trigger Behavior" page include information about colu= mn 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 t= rigger and from how the WHEN clause gets checked right before statement exe= cution. Both those "see" the effects of previously executed BEFOR= E triggers but column specific triggers don't.

Josh --000000000000200a8005de0ff215--