pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: stephane.desnault@gmail.com
Subject: BUG #16613: Built in suppress_redundant_updates_trigger() trigger not supressing redundant updates
Date: Wed, 09 Sep 2020 16:25:00 +0000
Message-ID: <16613-d5d1f061f4d595d3@postgresql.org> (raw)

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.



view thread (3+ messages)  latest in thread

Message-ID: <16613-d5d1f061f4d595d3@postgresql.org>
Permalink:  ../16613-d5d1f061f4d595d3@postgresql.org/
Also on:    postgresql.org/message-id/16613-d5d1f061f4d595d3@postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, stephane.desnault@gmail.com
  Subject: Re: BUG #16613: Built in suppress_redundant_updates_trigger() trigger not supressing redundant updates
  In-Reply-To: <16613-d5d1f061f4d595d3@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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