public inbox for [email protected]
help / color / mirror / Atom feedClarification of triggers with 'OF column_name' in 'Overview of Trigger Behavior'
8+ messages / 3 participants
[nested] [flat]
* Clarification of triggers with 'OF column_name' in 'Overview of Trigger Behavior'
@ 2022-05-02 17:40 PG Doc comments form <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: PG Doc comments form @ 2022-05-02 17:40 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Clarification of triggers with 'OF column_name' in 'Overview of Trigger Behavior'
@ 2022-05-02 22:21 David G. Johnston <[email protected]>
parent: PG Doc comments form <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: David G. Johnston @ 2022-05-02 22:21 UTC (permalink / raw)
To: [email protected]; Pg Docs <[email protected]>
On Mon, May 2, 2022 at 11:14 AM PG Doc comments form <[email protected]>
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.
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.
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.
David J.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Clarification of triggers with 'OF column_name' in 'Overview of Trigger Behavior'
@ 2022-05-02 23:42 Josh Silver <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Josh Silver @ 2022-05-02 23:42 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Pg Docs <[email protected]>
On Mon, May 2, 2022 at 3:22 PM David G. Johnston <[email protected]>
wrote:
> On Mon, May 2, 2022 at 11:14 AM PG Doc comments form <
> [email protected]> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Clarification of triggers with 'OF column_name' in 'Overview of Trigger Behavior'
@ 2022-05-02 23:53 David G. Johnston <[email protected]>
parent: Josh Silver <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: David G. Johnston @ 2022-05-02 23:53 UTC (permalink / raw)
To: Josh Silver <[email protected]>; +Cc: Pg Docs <[email protected]>
On Mon, May 2, 2022 at 4:42 PM Josh Silver <[email protected]> wrote:
> On Mon, May 2, 2022 at 3:22 PM David G. Johnston <
> [email protected]> wrote:
>
>> On Mon, May 2, 2022 at 11:14 AM PG Doc comments form <
>> [email protected]> 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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Clarification of triggers with 'OF column_name' in 'Overview of Trigger Behavior'
@ 2022-05-03 01:32 Josh Silver <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Josh Silver @ 2022-05-03 01:32 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Pg Docs <[email protected]>
On Mon, May 2, 2022 at 4:53 PM David G. Johnston <[email protected]>
wrote:
> On Mon, May 2, 2022 at 4:42 PM Josh Silver <[email protected]> wrote:
>
>> On Mon, May 2, 2022 at 3:22 PM David G. Johnston <
>> [email protected]> wrote:
>>
>>> On Mon, May 2, 2022 at 11:14 AM PG Doc comments form <
>>> [email protected]> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Clarification of triggers with 'OF column_name' in 'Overview of Trigger Behavior'
@ 2022-05-03 01:55 David G. Johnston <[email protected]>
parent: Josh Silver <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: David G. Johnston @ 2022-05-03 01:55 UTC (permalink / raw)
To: Josh Silver <[email protected]>; +Cc: Pg Docs <[email protected]>
On Mon, May 2, 2022 at 6:33 PM Josh Silver <[email protected]> wrote:
>
> 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.
>
>
The previous paragraph reads in part:
"If more than one trigger is defined for the same event on the same
relation, the triggers will be fired in alphabetical order by trigger name."
I'll say that we don't provide a granular definition of what an "event" is
here - namely the "INSERT/UPDATE/DELETE/SELECT + any column limitation".
So, it is covered if you understand the column aspect is rolled into
"trigger event".
David J.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Clarification of triggers with 'OF column_name' in 'Overview of Trigger Behavior'
@ 2022-05-03 02:10 David G. Johnston <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: David G. Johnston @ 2022-05-03 02:10 UTC (permalink / raw)
To: Josh Silver <[email protected]>; +Cc: Pg Docs <[email protected]>
On Mon, May 2, 2022 at 6:55 PM David G. Johnston <[email protected]>
wrote:
> On Mon, May 2, 2022 at 6:33 PM Josh Silver <[email protected]> wrote:
>
>>
>> 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.
>>
>>
> The previous paragraph reads in part:
>
> "If more than one trigger is defined for the same event on the same
> relation, the triggers will be fired in alphabetical order by trigger name."
>
> I'll say that we don't provide a granular definition of what an "event" is
> here - namely the "INSERT/UPDATE/DELETE/SELECT + any column limitation".
>
> So, it is covered if you understand the column aspect is rolled into
> "trigger event".
>
>
And, given the existing length and depth of content of that page already
I'm doubtful that introducing the corner-case of UPDATE OF (col) into that
section, in the level of detail provided in CREATE TRIGGER, is a net
positive. I'd probably leave the detail where it is, note that "UPDATE OF
(col)" exists as a distinct event type that modifies a plain UPDATE
TRIGGER's event check, and direct the reader to the specifics in CREATE
TRIGGER should they feel the need to use the more specific form. Nothing
in the "Overview" page is negated just because a column is specified on the
UPDATE trigger.
David J.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Clarification of triggers with 'OF column_name' in 'Overview of Trigger Behavior'
@ 2022-05-03 03:33 Josh Silver <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Josh Silver @ 2022-05-03 03:33 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Pg Docs <[email protected]>
On Mon, May 2, 2022 at 7:11 PM David G. Johnston <[email protected]>
wrote:
> On Mon, May 2, 2022 at 6:55 PM David G. Johnston <
> [email protected]> wrote:
>
>> On Mon, May 2, 2022 at 6:33 PM Josh Silver <[email protected]>
>> wrote:
>>
> And, given the existing length and depth of content of that page already
> I'm doubtful that introducing the corner-case of UPDATE OF (col) into that
> section, in the level of detail provided in CREATE TRIGGER, is a net
> positive.
>
I'd definitely lean towards complete over terse, especially since I don't
think it's any more detailed or lengthy than the WHEN CLAUSE that's already
there, but given those concerns I think at a minimum mentioning column
specific triggers by name and that changes made to the row's contents by
BEFORE UPDATE triggers are not considered.
I'd probably leave the detail where it is, note that "UPDATE OF (col)"
> exists as a distinct event type that modifies a plain UPDATE TRIGGER's
> event check, and direct the reader to the specifics in CREATE TRIGGER
> should they feel the need to use the more specific form.
>
That sounds reasonable to me, What about splitting column
specific triggers up across the page, so that less space is given to them?
Something like
> These two types of triggers are sometimes called *row-level* triggers and
> *statement-level* triggers, respectively. <NEW>A row-level trigger that specifies
> a list of columns is a column-specific trigger.</NEW> Triggers on TRUNCATE may
> only be defined at statement level, not per-row.
If more than one trigger is defined for the same event on the same
> relation, the triggers will be fired in alphabetical order by trigger name.
> In the case of BEFORE and INSTEAD OF triggers, the possibly-modified row
> returned by each trigger becomes the input to the next trigger. <NEW>Changes
> made to the row's contents by BEFORE UPDATE triggers do not change which
> column specific triggers will be fired, because they only consider
> whether any of its columns are listed as targets in the UPDATE command's
> SET list.</NEW> If any BEFORE or INSTEAD OF trigger returns NULL, the
> operation is abandoned for that row and subsequent triggers are not fired
> (for that row).
It saves the syntax for the SQL command page, and it trades directing the
users to the SQL page for describing the behavior. The overview page
already has a link to the SQL command page, so it saves having to
re-mention it.
Josh
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2022-05-03 03:33 UTC | newest]
Thread overview: 8+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-05-02 17:40 Clarification of triggers with 'OF column_name' in 'Overview of Trigger Behavior' PG Doc comments form <[email protected]>
2022-05-02 22:21 ` David G. Johnston <[email protected]>
2022-05-02 23:42 ` Josh Silver <[email protected]>
2022-05-02 23:53 ` David G. Johnston <[email protected]>
2022-05-03 01:32 ` Josh Silver <[email protected]>
2022-05-03 01:55 ` David G. Johnston <[email protected]>
2022-05-03 02:10 ` David G. Johnston <[email protected]>
2022-05-03 03:33 ` Josh Silver <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox