Have you tried to use built-in suppress_redundant_updates_trigger[1] for this?
1. https://www.postgresql.org/docs/12/functions-trigger.html <https://www.postgresql.org/docs/12/functions-trigger.html> > On 3. Feb 2020, at 09:27, Condor <con...@stz-bg.com> wrote: > > On 02-02-2020 23:06, Adrian Klaver wrote: >> On 2/2/20 1:24 AM, Condor wrote: >>> Hello, >>> I'm using PostgreSQL 12.1 and trying to avoid update on table when data is >>> the same. I read somewhere if UPDATE is with the same data SQL server on >>> system level does not do update on table but don't know if that is true or >>> not. If that is not true I do: >>> First I create a function that should update data: >>> CREATE OR REPLACE FUNCTION log_last_chaged() RETURNS TRIGGER >>> LANGUAGE plpgsql >>> AS $$ >>> BEGIN >>> UPDATE status_table SET status0 = NEW.status0, lastchage = >>> CURRENT_TIMESTAMP WHERE rowid = OLD.rowid; >>> RETURN NEW; >>> END >>> $$; >>> then create table: >>> CREATE TABLE status_table ( >>> rowid INTEGER, >>> status0 INTEGER, >>> lastchage TIMESTAMP(0) WITHOUT TIME ZONE >>> ); >>> attach trigger: >> Why the DROP TRIGGER on card_sync_tbl? >> More below. >>> DROP TRIGGER last_changes ON card_sync_tbl; >>> CREATE TRIGGER last_changes >>> BEFORE UPDATE ON status_table >>> FOR EACH ROW >>> WHEN (OLD.* IS DISTINCT FROM NEW.*) >>> EXECUTE FUNCTION log_last_chaged(); >>> insert first data: >>> INSERT INTO status_table (rowid, status0) VALUES (11, 1); >>> INSERT INTO status_table (rowid, status0) VALUES (12, 2); >>> and check do everything work fine: >>> UPDATE status_table SET status0 = 1 WHERE rowid = 11; >>> UPDATE status_table SET status0 = 4 WHERE rowid = 12; >>> I receive something on rowid 12 that probably is error: >>> SQL statement "UPDATE status_table SET status0 = NEW.status0, lastchage = >>> CURRENT_TIMESTAMP WHERE rowid = OLD.rowid" >>> PL/pgSQL function log_last_chaged() line 3 at SQL statement >>> After quick look on duckduckgo I change the function to this: >>> CREATE OR REPLACE FUNCTION log_last_chaged() RETURNS TRIGGER >>> LANGUAGE plpgsql >>> AS $$ >>> BEGIN >>> NEW.lastchage := CURRENT_TIMESTAMP; >>> RETURN NEW; >>> END >>> $$; >>> and everything seems work now, but that break the idea update not to hit >>> table if data is the same. >> Some changes based on: >> https://www.postgresql.org/docs/12/sql-createtrigger.html >> "In a BEFORE trigger, the WHEN condition is evaluated just before the >> function is or would be executed, so using WHEN is not materially >> different from testing the same condition at the beginning of the >> trigger function. Note in particular that the NEW row seen by the >> condition is the current value, as possibly modified by earlier >> triggers. " >> https://www.postgresql.org/docs/12/plpgsql-trigger.html#PLPGSQL-DML-TRIGGER >> "Row-level triggers fired BEFORE can return null to signal the trigger >> manager to skip the rest of the operation for this row (i.e., >> subsequent triggers are not fired, and the INSERT/UPDATE/DELETE does >> not occur for this row)." >> CREATE OR REPLACE FUNCTION public.log_last_chaged() >> RETURNS trigger >> LANGUAGE plpgsql >> AS $function$ >> BEGIN >> IF OLD.* IS DISTINCT FROM NEW.* THEN >> RAISE NOTICE 'UPDATE'; >> NEW.lastchage := CURRENT_TIMESTAMP; >> RETURN NEW; >> ELSE >> RETURN NULL; >> END IF; >> END >> $function$ >> CREATE TRIGGER last_changes >> BEFORE UPDATE ON status_table >> FOR EACH ROW >> EXECUTE FUNCTION log_last_chaged(); >> test=> INSERT INTO status_table (rowid, status0) VALUES (11, 1); >> INSERT 0 1 >> test=> INSERT INTO status_table (rowid, status0) VALUES (12, 2); >> INSERT 0 1 >> test=> select ctid, * from status_table ; >> ctid | rowid | status0 | lastchage >> -------+-------+---------+----------- >> (0,1) | 11 | 1 | >> (0,2) | 12 | 2 | >> (2 rows) >> test=> UPDATE status_table SET status0 = 1 WHERE rowid = 11; >> UPDATE 0 >> test=> select ctid, * from status_table ; >> ctid | rowid | status0 | lastchage >> -------+-------+---------+----------- >> (0,1) | 11 | 1 | >> (0,2) | 12 | 2 | >> (2 rows) >> NOTE: UPDATE 0 and no change in ctid >> test=> UPDATE status_table SET status0 = 4 WHERE rowid = 12; >> NOTICE: UPDATE >> UPDATE 1 >> test=> select ctid, * from status_table ; >> ctid | rowid | status0 | lastchage >> -------+-------+---------+--------------------- >> (0,1) | 11 | 1 | >> (0,3) | 12 | 4 | 02/02/2020 13:03:21 >> (2 rows) >> NOTE: UPDATE 1 and ctid change. >>> Any body can help with some hint ? Also I want to know why my first >>> function does not work, probably loop is happened if trigger does not stop >>> update to be sent to table on rowid 12 or syntax error. >>> Regards, >>> HS > > Thank you for detailed explanation. > Have a good day. > > Regards, > HS > >
