1

I was working on a set of triggers in PostgreSQL and I think I stumbled on a bug on the built-in function/trigger suppress_redundant_updates_trigger(). It's fully reproducible on my configuration (PostgreSQL 12 on my laptop).

First I set up a table, with two "before each row" triggers:

CREATE TABLE test (id int, val text);
INSERT INTO test VALUES (1, 'one'), (2, 'two');

CREATE OR REPLACE FUNCTION am_i_touched() RETURNS trigger LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    RAISE NOTICE 'Yes, I am touched!';
    RETURN NEW;
END;
$BODY$;

CREATE TRIGGER az_test_suppress_redundant_update
    BEFORE UPDATE ON public.test
    FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger();

-- Make sure trigger name is after the previous one 
-- in alphabetical order as it drives execution order
CREATE TRIGGER bz_am_I_touched
    BEFORE UPDATE ON public.test 
    FOR EACH ROW EXECUTE PROCEDURE am_i_touched();

I then run UPDATE test SET id = 1 WHERE id = 1. As expected, the update is suppressed by the first trigger since the row is left unchanged, and bz_am_i_touched() never fires. So far so good.

But then I run:

ALTER TABLE test ADD COLUMN newcol int

Now, I run again UPDATE test SET id = 1 WHERE id = 1... And this time, the update is NOT suppressed and bz_am_i_touched() fires! PGAdmin (v4) reports that one record was updated, not zero like the time before!

This is a one-off occurrence. Further UPDATE test SET id = 1 WHERE id = 1 work as expected... But then I tried UPDATE test SET id = 2 WHERE id = 2... and again I have this strange behavior - the update is not suppressed.

Is that an expected behavior? I can't understand how UPDATE test SET id = 1 WHERE id = 1 can result in the update not being suppressed.

Stephane Desnault
  • 57
  • 6
  • Sounds fishy. You should report that to PostgreSQL as a bug. – Laurenz Albe Sep 09 '20 at 16:01
  • Yes, I am doing so just now... It's not as far-fetched an issue as it looks, since many audit-related setups are PRECISELY adding columns to tables as well as defining the triggers... And that's what I was doing. – Stephane Desnault Sep 09 '20 at 16:07

1 Answers1

2

The way the newcol NULL value is represented is different between the new tuple and the old tuple. So they are not considered to be the same, and so the update is not suppressed.

The tuples are compared in total with memcmp, so differences in even user-invisible bytes will be found significant. It doesn't loop through each field making individual type-dependent decisions about what differences are semantically meaningful. This seems to be intentional, for speed and simplicity. I doubt it would be considered a bug.

jjanes
  • 37,812
  • 5
  • 27
  • 34
  • Thanks for the clarification. Unfortunately, when setting up very standard auditing procedures, adding additional columns to a table just at the same time as setting up triggers is quite common :(. So the issue is not as obscure as one would think. – Stephane Desnault Sep 09 '20 at 16:32
  • On second thought, I agree with Jeff: skipping unnecessary updates is nice to have, but not essential, so it is no drama if the updates are executed once in a blue moon when you add a column and the physical tuple changes. After all, it is just a performance feature, and for that it is important that the trigger is fast. – Laurenz Albe Sep 10 '20 at 01:18
  • Hi Laurenz. It is far from being only a performance feature when used as part of an auditing or archiving feature. The trigger will not work as advertised after table mods , very often of exactly the type you do as part of implementing audit procedures. I replaced it using the standard "NEW IS DISTINCT FROM OLD" operator, which, by contrast, works as advertised. In my case, changed records go in an audit trail and automated, 3rd party tools routinely update millions of rows for just a dozen "real" changes. – Stephane Desnault Sep 10 '20 at 14:52