From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | stephane(dot)desnault(at)gmail(dot)com |
Subject: | BUG #16613: Built in suppress_redundant_updates_trigger() trigger not supressing redundant updates |
Date: | 2020-09-09 16:25:00 |
Message-ID: | 16613-d5d1f061f4d595d3@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 16613
Logged by: Stephane Desnault
Email address: stephane(dot)desnault(at)gmail(dot)com
PostgreSQL version: 12.4
Operating system: Windows 10
Description:
A full description was a bit too long for the title. A more exact summary
is:
The built-in suppress_redundant_updates_trigger() trigger is not supressing
redundant updates after an ALTER TABLE ... ADD COLUMN...
Here are the steps to reproduce what I saw:
I run the following script in pgAdmin, with autocommit set to true.
--------------
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();
CREATE TRIGGER bz_am_I_touched
BEFORE UPDATE ON public.test
FOR EACH ROW EXECUTE PROCEDURE am_i_touched();
----------------------------
Now, if I run
UPDATE test SET id = 1 WHERE id = 1;
the update is suppressed by the first trigger since the row is left
unchanged, and bz_am_i_touched() never fires, as expected. So far so good.
But then I run:
ALTER TABLE test ADD COLUMN new_col int;
and I then run UPDATE test SET id = 1 WHERE id = 1; again.
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.
Also, if the second trigger doesn't let the update go through
(am_i_touched() returns NULL rather than NEW), then subsequent updates are
NEVER suppressed, it's not a "one-off" behavior anymore.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2020-09-09 17:38:50 | Re: BUG #16613: Built in suppress_redundant_updates_trigger() trigger not supressing redundant updates |
Previous Message | Jehan-Guillaume de Rorthais | 2020-09-09 16:20:02 | Re: BUG #15285: Query used index over field with ICU collation in some cases wrongly return 0 rows |