pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #16613: Built in suppress_redundant_updates_trigger() trigger not supressing redundant updates
3+ messages / 3 participants
[nested] [flat]
* BUG #16613: Built in suppress_redundant_updates_trigger() trigger not supressing redundant updates
@ 2020-09-09 16:25 PG Bug reporting form <noreply@postgresql.org>
2020-09-09 17:38 ` Re: BUG #16613: Built in suppress_redundant_updates_trigger() trigger not supressing redundant updates Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2020-09-09 16:25 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: stephane.desnault@gmail.com
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #16613: Built in suppress_redundant_updates_trigger() trigger not supressing redundant updates
2020-09-09 16:25 BUG #16613: Built in suppress_redundant_updates_trigger() trigger not supressing redundant updates PG Bug reporting form <noreply@postgresql.org>
@ 2020-09-09 17:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-09-09 18:05 ` Re: BUG #16613: Built in suppress_redundant_updates_trigger() trigger not supressing redundant updates Stephane Desnault <stephane.desnault@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2020-09-09 17:38 UTC (permalink / raw)
To: stephane.desnault@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> The built-in suppress_redundant_updates_trigger() trigger is not supressing
> redundant updates after an ALTER TABLE ... ADD COLUMN...
This doesn't seem like a bug. The trigger code is looking for bitwise
equality between new tuple and old tuple, which it won't find because
the new tuple will have a larger number-of-attributes field; not to
mention a possibly-wider nulls bitmap.
Sure, we could complicate the trigger to try to understand such cases,
but that would likely make things net slower in most applications.
The hard part of having such a trigger is that it's pure overhead
for real updates, so that overhead has to be minimized.
Possibly the documentation could be clarified. What it says now is
"prevent any update that does not actually change the data in the
row from taking place", which is pretty vague about what the criteria
really are. Not sure about better wording though.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #16613: Built in suppress_redundant_updates_trigger() trigger not supressing redundant updates
2020-09-09 16:25 BUG #16613: Built in suppress_redundant_updates_trigger() trigger not supressing redundant updates PG Bug reporting form <noreply@postgresql.org>
2020-09-09 17:38 ` Re: BUG #16613: Built in suppress_redundant_updates_trigger() trigger not supressing redundant updates Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-09-09 18:05 ` Stephane Desnault <stephane.desnault@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Stephane Desnault @ 2020-09-09 18:05 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
Hi Tom,
Thanks for the prompt and detailed answer. I think it needs to be explained
in the documentation. Many typical auditing setups - including some in the
official documentation - add new columns to tables at the same time they
add triggers. So the issue is not as obscure as it may look. I solved my
issue by testing for "NEW IS NOT DISTINCT FROM OLD" in my own plpgSQL
trigger.
Best regards,
Stephane
Le mer. 9 sept. 2020 à 19:38, Tom Lane <tgl@sss.pgh.pa.us> a écrit :
> PG Bug reporting form <noreply@postgresql.org> writes:
> > The built-in suppress_redundant_updates_trigger() trigger is not
> supressing
> > redundant updates after an ALTER TABLE ... ADD COLUMN...
>
> This doesn't seem like a bug. The trigger code is looking for bitwise
> equality between new tuple and old tuple, which it won't find because
> the new tuple will have a larger number-of-attributes field; not to
> mention a possibly-wider nulls bitmap.
>
> Sure, we could complicate the trigger to try to understand such cases,
> but that would likely make things net slower in most applications.
> The hard part of having such a trigger is that it's pure overhead
> for real updates, so that overhead has to be minimized.
>
> Possibly the documentation could be clarified. What it says now is
> "prevent any update that does not actually change the data in the
> row from taking place", which is pretty vague about what the criteria
> really are. Not sure about better wording though.
>
> regards, tom lane
>
--
Stéphane Desnault
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2020-09-09 18:05 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-09-09 16:25 BUG #16613: Built in suppress_redundant_updates_trigger() trigger not supressing redundant updates PG Bug reporting form <noreply@postgresql.org>
2020-09-09 17:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-09-09 18:05 ` Stephane Desnault <stephane.desnault@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox