BUG #16613: Built in suppress_redundant_updates_trigger() trigger not supressing redundant updates
От | PG Bug reporting form |
---|---|
Тема | BUG #16613: Built in suppress_redundant_updates_trigger() trigger not supressing redundant updates |
Дата | |
Msg-id | 16613-d5d1f061f4d595d3@postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #16613: Built in suppress_redundant_updates_trigger() trigger not supressing redundant updates
|
Список | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 16613 Logged by: Stephane Desnault Email address: stephane.desnault@gmail.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.
В списке pgsql-bugs по дате отправления: