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-0003fo-Kv 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-0003gV-UH 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 1nlhPd-0000ES-ST for pgsql-docs@lists.postgresql.org; Tue, 03 May 2022 01:33:14 +0000 Received: from mail-io1-xd2f.google.com ([2607:f8b0:4864:20::d2f]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nlhPb-00080W-Az for pgsql-docs@lists.postgresql.org; Tue, 03 May 2022 01:33:12 +0000 Received: by mail-io1-xd2f.google.com with SMTP id e194so17684581iof.11 for ; Mon, 02 May 2022 18:33:10 -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=nHp5Xaq6Jwdp5BTOlPYVVUeuKfXIg2GsPBdEZ93Y0Hk=; b=Efwsl9XdZk4YJCOvFgm3U4YSu4rKFYN/XkUFOAuLtiTprPVFgWai79g2Twa1+dN3cG 6J2tPSgKDhjLgH4mv+NAvjslrbqNd7j5CUtvPosIPLcyUJ2CPt9/sTqpCrV0WLLR/hwg Clvudc2qH9cG/EV73cHEpmq9BK4JtHMg//3+Q= 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=nHp5Xaq6Jwdp5BTOlPYVVUeuKfXIg2GsPBdEZ93Y0Hk=; b=ce86jOwkfYBo2lE22gQqGacoHe2HPWcEz1Yn/waRz54Q5AkpEIIyfnWHNxecEg2QQy yu0sEHTjroMlw2OQanp1ufx0my//jRIZz3JCLvYVQkc0I1aV4AGjoonyP2+2xcTzMqsR OV2LiCEljp0j1ors6T+t73RBDeFhwZ6R9vqcUu3GqnVmGGweig2gVGtsi2yHvca6HReu eS5cAx9/iAkkR9lnoyVtzJ40mVPqxy9xvnf9lKbDYKXSc4g81VfA8Mr3uaAZjWTtS7tI a7mGWz6m0jfoZsrx4RZ3SO3wM/yZHSh6MmyL1GdiWeRk5pM2NiQt77Y8wDT0TQ8TT4Ds ez2g== X-Gm-Message-State: AOAM532vk9Fmu7K1LBw9d+7i6u/d2FL3LgGYHbCvNtncRBpZx6C2ZpOa o6CXi4EwPx5OYr5gpMLY9F7BDm2eKq1AuQX8MyEoHg== X-Google-Smtp-Source: ABdhPJw310oU/xobDKtPzsklOpngn75ST8f5lAkl2GgVp+KNFdedTRsEEuQZ8v8cqFGNRUXYfnqQtWgiCpk7dzNowUc= X-Received: by 2002:a05:6602:3c7:b0:64f:cb21:a8a0 with SMTP id g7-20020a05660203c700b0064fcb21a8a0mr5255091iov.28.1651541589484; Mon, 02 May 2022 18:33:09 -0700 (PDT) MIME-Version: 1.0 References: <165151321896.685.16545084197561479198@wrigleys.postgresql.org> In-Reply-To: From: Josh Silver Date: Mon, 2 May 2022 18:32:58 -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="000000000000c600c605de117d66" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000c600c605de117d66 Content-Type: text/plain; charset="UTF-8" On Mon, May 2, 2022 at 4:53 PM David G. Johnston wrote: > 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: >>> >> 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 --000000000000c600c605de117d66 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
<= br>

On Mon, May 2, 202= 2 at 4:53 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Mon, May 2, 2022 at 4:42 PM Josh Silver <jo= sh.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 t= his works.=C2=A0 The column specific trigger is only "checked against&= quot; the SQL Command "UPDATE tbl SET col" ...
<= /div>
=C2=A0
I feel like I'm completely not = getting the point you are trying to make here because if I edit my statemen= t 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 trig= ger is only checked against the=C2=A0"checked against" the SQL Command "UPDATE t= bl SET col".
I don't see how that alters m= y point, which is to document this difference since a column specific trigg= er and a when clause that checks if a column has changed can have very diff= erent behavior.
=C2=A0
Intr= oducing such a concept to the documentation doesn't seem like a good so= lution.

I'm not sugg= esting to introduce it as I've described. I'm suggesting documentin= g 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 trig= ger page NOTES says of before triggers

In a=C2=A0BEFORE=C2=A0trigger, the=C2=A0WHEN=C2=A0condition is evaluated just before the function= is or would be executed, so using=C2=A0WHEN=C2=A0is not materially different from testing the sa= me condition at the beginning of the trigger function. Note in particular t= hat the=C2=A0NEW=C2=A0= row seen by the condition is the current value, as possibly modified by ear= lier triggers. Also, a=C2=A0B= EFORE=C2=A0trigger's=C2=A0WHEN=C2=A0condition is not allowed to examine the system col= umns of the=C2=A0NEW= =C2=A0row (such as=C2=A0ctid<= /code>), because those won't have been set yet.

= =C2=A0and of column specific triggers
=C2=A0A column-specific trigger (o= ne defined using the=C2=A0UPDATE OF=C2=A0column_name= =C2=A0syntax) will fire when any of its columns are listed as targets in th= e=C2=A0UPDATE= =C2=A0command's=C2=A0SETBEFORE = UPDATE=C2=A0triggers are not considered. Conv= ersely, a command such as=C2=A0UPDATE ... SET x =3D x ...=C2=A0will fire a trigger on column=C2=A0x, even though t= he column's value did not change.
=C2=A0
<= div>and the=C2=A0 'Overview of Trigger Behavior pages says of before tr= iggers
In row-level triggers the=C2=A0WHEN=C2=A0condi= tion can examine the old and/or new values of columns of the row. (Statemen= t-level triggers can also have=C2=A0WHEN=C2=A0conditions, = although the feature is not so useful for them.) In a=C2=A0BEFORE=C2=A0trigger, the=C2=A0WHEN=C2=A0condition is ev= aluated just before the function is or would be executed, so using=C2=A0WHEN=C2=A0is not materially different from testing the same c= ondition at the beginning of the trigger function.
= =C2=A0
but does not even mention=C2=A0column specific triggers by= name.
=C2=A0
Josh
--000000000000c600c605de117d66--