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