Re: How to avoid UPDATE on same data in table ?

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