Обсуждение: Cascade Trigger Not Firing
I have a trigger that updates a target column when some other columns change.
There is another trigger on the target column to update another table (the column can be changed in other ways besides the first trigger).
If I update the target column directly the expected trigger fires.
But if the 1st trigger changes the target column and it wasn't in the list of updated columns, the 2nd trigger doesn't fire.
Is this expected behavior? I thought that ANY change to the column would fire the trigger.
There is another trigger on the target column to update another table (the column can be changed in other ways besides the first trigger).
If I update the target column directly the expected trigger fires.
But if the 1st trigger changes the target column and it wasn't in the list of updated columns, the 2nd trigger doesn't fire.
Is this expected behavior? I thought that ANY change to the column would fire the trigger.
Note that I've got a work-around by making the first trigger an AFTER trigger and calling UPDATE instead of just changing NEW. But it was a while before we caught this and it's worrisome to me that a column can change without a trigger noticing.
Here's about the smallest example I could come up with:
Here's about the smallest example I could come up with:
-----------------------------------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS table1(
id SERIAL,
total INTEGER DEFAULT 0
);
CREATE TABLE IF NOT EXISTS table2(
id SERIAL,
t1_id INTEGER,
col1 INTEGER DEFAULT 0,
col2 INTEGER DEFAULT 0
);
CREATE OR REPLACE FUNCTION update_total()
RETURNS TRIGGER AS $$
DECLARE
BEGIN
RAISE WARNING '### in update_total: %',NEW;
UPDATE table1
SET total = NEW.col2
WHERE id = NEW.t1_id;
RETURN NULL;
END;
$$ LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION update_col2()
RETURNS TRIGGER AS $$
DECLARE
BEGIN
RAISE WARNING '**** in update_col2: %', NEW;
NEW.col2 = NEW.col1 * 3;
RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';
CREATE TRIGGER au_update_total
AFTER UPDATE OF col2
ON table2
FOR EACH ROW
EXECUTE PROCEDURE update_total();
CREATE TRIGGER biu_update_col2
BEFORE INSERT OR UPDATE OF col1
ON table2
FOR EACH ROW
EXECUTE PROCEDURE update_col2();
INSERT INTO table1 VALUES (DEFAULT, -99);
INSERT INTO table2 VALUES (DEFAULT, 1, 10, 10); -- fires col2 trigger
SELECT * FROM table1;
SELECT * FROM table2;
UPDATE table2 SET col2 = 99; -- fires total trigger
SELECT * FROM table1;
SELECT * from table2;
UPDATE table2 SET col1 = 5; -- ** only col2 trigger is fired; expected total trigger to fire ***
SELECT * FROM table1;
SELECT * from table2;
UPDATE table2 SET col1 = 3, col2 = col2; -- fires both triggers
SELECT * FROM table1;
SELECT * from table2;
id SERIAL,
total INTEGER DEFAULT 0
);
CREATE TABLE IF NOT EXISTS table2(
id SERIAL,
t1_id INTEGER,
col1 INTEGER DEFAULT 0,
col2 INTEGER DEFAULT 0
);
CREATE OR REPLACE FUNCTION update_total()
RETURNS TRIGGER AS $$
DECLARE
BEGIN
RAISE WARNING '### in update_total: %',NEW;
UPDATE table1
SET total = NEW.col2
WHERE id = NEW.t1_id;
RETURN NULL;
END;
$$ LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION update_col2()
RETURNS TRIGGER AS $$
DECLARE
BEGIN
RAISE WARNING '**** in update_col2: %', NEW;
NEW.col2 = NEW.col1 * 3;
RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';
CREATE TRIGGER au_update_total
AFTER UPDATE OF col2
ON table2
FOR EACH ROW
EXECUTE PROCEDURE update_total();
CREATE TRIGGER biu_update_col2
BEFORE INSERT OR UPDATE OF col1
ON table2
FOR EACH ROW
EXECUTE PROCEDURE update_col2();
INSERT INTO table1 VALUES (DEFAULT, -99);
INSERT INTO table2 VALUES (DEFAULT, 1, 10, 10); -- fires col2 trigger
SELECT * FROM table1;
SELECT * FROM table2;
UPDATE table2 SET col2 = 99; -- fires total trigger
SELECT * FROM table1;
SELECT * from table2;
UPDATE table2 SET col1 = 5; -- ** only col2 trigger is fired; expected total trigger to fire ***
SELECT * FROM table1;
SELECT * from table2;
UPDATE table2 SET col1 = 3, col2 = col2; -- fires both triggers
SELECT * FROM table1;
SELECT * from table2;
Judy Loomis <hoodie.judy@gmail.com> writes:
> I have a trigger that updates a target column when some other columns
> change.
> There is another trigger on the target column to update another table (the
> column can be changed in other ways besides the first trigger).
> If I update the target column directly the expected trigger fires.
> But if the 1st trigger changes the target column and it wasn't in the list
> of updated columns, the 2nd trigger doesn't fire.
> Is this expected behavior?
Per the manual (NOTES section of the CREATE TRIGGER man page):
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.
It's not really practical for trigger firings to depend on what other
triggers did or might do --- you'd soon end up with circularities.
regards, tom lane
I thought that might be the answer, but it's a pretty big hole when we're using triggers for audit purposes on financial data.
I'm going to have to really look at all my BEFORE UPDATE triggers and make sure we're not missing any more.
And I have to stop telling management that a trigger means we always know when a value changes.
Thanks,
Judy
On Fri, Sep 13, 2019 at 2:08 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Judy Loomis <hoodie.judy@gmail.com> writes:
> I have a trigger that updates a target column when some other columns
> change.
> There is another trigger on the target column to update another table (the
> column can be changed in other ways besides the first trigger).
> If I update the target column directly the expected trigger fires.
> But if the 1st trigger changes the target column and it wasn't in the list
> of updated columns, the 2nd trigger doesn't fire.
> Is this expected behavior?
Per the manual (NOTES section of the CREATE TRIGGER man page):
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.
It's not really practical for trigger firings to depend on what other
triggers did or might do --- you'd soon end up with circularities.
regards, tom lane
Judy Loomis
469.235.5839
Judy Loomis <hoodie.judy@gmail.com> writes:
> I'm going to have to really look at all my BEFORE UPDATE triggers and make
> sure we're not missing any more.
> And I have to stop telling management that a trigger means we always know
> when a value changes.
Well, you can rely on that, just not like this. Use an AFTER trigger
(else, you can't be sure it fires after all the BEFORE triggers)
and instead of triggering it with a column parameter, have it do
something like "if old.col is distinct from new.col".
Yeah, it's a bit slower that way, but there's no free lunch,
especially if you don't trust your other triggers. (Although,
if you have so many triggers that that's a problem, I think you
might have some other design issues.)
regards, tom lane
At the very least that note about this behavior should be highlighted, probably on the Trigger Behavior page and not buried in a bunch of notes on the Create Trigger page.
On Fri, Sep 13, 2019 at 4:03 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Judy Loomis <hoodie.judy@gmail.com> writes:
> I'm going to have to really look at all my BEFORE UPDATE triggers and make
> sure we're not missing any more.
> And I have to stop telling management that a trigger means we always know
> when a value changes.
Well, you can rely on that, just not like this. Use an AFTER trigger
(else, you can't be sure it fires after all the BEFORE triggers)
and instead of triggering it with a column parameter, have it do
something like "if old.col is distinct from new.col".
Yeah, it's a bit slower that way, but there's no free lunch,
especially if you don't trust your other triggers. (Although,
if you have so many triggers that that's a problem, I think you
might have some other design issues.)
regards, tom lane
Judy Loomis
469.235.5839
On 9/13/19 8:07 PM, Judy Loomis wrote: > At the very least that note about this behavior should be highlighted, > probably on the Trigger Behavior page and not buried in a bunch of notes > on the Create Trigger page. I know this after the fact. Still, as a general rule the best place to start when learning about a command is on it's respective page under here: https://www.postgresql.org/docs/11/sql-commands.html Further I usually go to the Notes after reading the synopsis, as Notes is where the exceptions to the rules and gotchas are called out. > > > > On Fri, Sep 13, 2019 at 4:03 PM Tom Lane <tgl@sss.pgh.pa.us > <mailto:tgl@sss.pgh.pa.us>> wrote: > > Judy Loomis <hoodie.judy@gmail.com <mailto:hoodie.judy@gmail.com>> > writes: > > I'm going to have to really look at all my BEFORE UPDATE triggers > and make > > sure we're not missing any more. > > And I have to stop telling management that a trigger means we > always know > > when a value changes. > > Well, you can rely on that, just not like this. Use an AFTER trigger > (else, you can't be sure it fires after all the BEFORE triggers) > and instead of triggering it with a column parameter, have it do > something like "if old.col is distinct from new.col". > > Yeah, it's a bit slower that way, but there's no free lunch, > especially if you don't trust your other triggers. (Although, > if you have so many triggers that that's a problem, I think you > might have some other design issues.) > > regards, tom lane > > > > -- > > ---------------------------------------------------------- > *Judy Loomis* > 469.235.5839 -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver <adrian.klaver@aklaver.com> writes: > On 9/13/19 8:07 PM, Judy Loomis wrote: >> At the very least that note about this behavior should be highlighted, >> probably on the Trigger Behavior page and not buried in a bunch of notes >> on the Create Trigger page. > I know this after the fact. Still, as a general rule the best place to > start when learning about a command is on it's respective page under here: > https://www.postgresql.org/docs/11/sql-commands.html > Further I usually go to the Notes after reading the synopsis, as Notes > is where the exceptions to the rules and gotchas are called out. The only mention of this feature in 38.1 "Overview of Trigger Behavior" is UPDATE triggers can moreover be set to fire only if certain columns are mentioned in the SET clause of the UPDATE statement. which seems to me to be plenty specific enough --- it is carefully *not* saying that the trigger will fire if the column changes value. The CREATE TRIGGER man page never says that, either. regards, tom lane
On 9/14/19 9:54 AM, Tom Lane wrote:
[snip
Given that the UPDATE "*can* ... be set to fire only if certain columns are mentioned in the SET clause of the UPDATE statement", it logically follows that the default behavior is something else (for example, if the field value changes for whatever reason.
[snip
The only mention of this feature in 38.1 "Overview of Trigger Behavior" is UPDATE triggers can moreover be set to fire only if certain columns are mentioned in the SET clause of the UPDATE statement. which seems to me to be plenty specific enough --- it is carefully *not* saying that the trigger will fire if the column changes value. The CREATE TRIGGER man page never says that, either.
Given that the UPDATE "*can* ... be set to fire only if certain columns are mentioned in the SET clause of the UPDATE statement", it logically follows that the default behavior is something else (for example, if the field value changes for whatever reason.
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.
On Sat, 14 Sep 2019 10:00:18 -0500, Ron <ronljohnsonjr@gmail.com> wrote: >On 9/14/19 9:54 AM, Tom Lane wrote: >[snip >> The only mention of this feature in 38.1 "Overview of Trigger Behavior" >> is >> >> UPDATE triggers*can* moreover be set to fire only if certain columns >> are mentioned in the SET clause of the UPDATE statement. >> >> which seems to me to be plenty specific enough --- it is carefully >> *not* saying that the trigger will fire if the column changes value. >> The CREATE TRIGGER man page never says that, either. > >Given that the UPDATE "*can* ... be set to fire only if certain columns are >mentioned in the SET clause of the UPDATE statement", it logically follows >that the default behavior is something else (for example, if the field value >changes for whatever reason. But the default could be "any column mentioned", not necessarily any value changed. George