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

From: PG Doc comments form <noreply(at)postgresql(dot)org>
To: pgsql-docs(at)lists(dot)postgresql(dot)org
Cc: josh(dot)ag(at)paladin(dot)insure
Subject: Clarification of triggers with 'OF column_name' in 'Overview of Trigger Behavior'
Date: 2022-05-02 17:40:18
Message-ID: 165151321896.685.16545084197561479198@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/13/trigger-datachanges.html
Description:

This caught me out because I was looking at the wrong page, so I figured it
might be a good addition. I'm running version 'PostgreSQL 13.2 (Debian
13.2-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6)
8.3.0, 64-bit'

If I have

CREATE TABLE test ( alpha integer, beta integer, charlie integer);
CREATE FUNCTION ab() RETURNS TRIGGER
LANGUAGE plpgsql
AS $_$
BEGIN
RAISE INFO '%: ab firing', clock_timestamp();
IF NEW.alpha - OLD.alpha >= 5 THEN
NEW.beta = NEW.beta + 1;
END IF;
RETURN NEW;
END;
$_$;
CREATE FUNCTION bc() RETURNS TRIGGER
LANGUAGE plpgsql
AS $_$
BEGIN
RAISE INFO '%: bc firing', clock_timestamp();
IF NEW.beta IS DISTINCT FROM OLD.beta THEN
NEW.charlie = NEW.charlie + 1;
END IF;
RETURN NEW;
END;
$_$;
CREATE TRIGGER b2_ab BEFORE UPDATE OF alpha ON test FOR EACH ROW EXECUTE
FUNCTION ab();
CREATE TRIGGER b3_bc BEFORE UPDATE OF beta ON test FOR EACH ROW EXECUTE
FUNCTION bc();

INSERT INTO test (alpha, beta, charlie) VALUES (0,0,0);

then when running

UPDATE test SET alpha = 5;

only trigger b2_ab will fire, even though it will update column beta. 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.

This specific behavior is mentioned in the sql documentation reference for
CREATE TRIGGER, but I think at least a mention of this would be useful on
this page as well similar to how WHEN conditions are mentioned on both
pages.

Responses

Browse pgsql-docs by date

  From Date Subject
Next Message David G. Johnston 2022-05-02 22:21:41 Re: Clarification of triggers with 'OF column_name' in 'Overview of Trigger Behavior'
Previous Message David G. Johnston 2022-04-29 15:05:36 Re: Documentation Suggestion