Обсуждение: How to avoid UPDATE on same data in table ?

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:
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.
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

Am 02.02.20 um 10:24 schrieb Condor:
> CREATE TRIGGER last_changes
>   BEFORE UPDATE ON status_table
>   FOR EACH ROW
>   WHEN (OLD.* IS DISTINCT FROM NEW.*) 

try to exclude the column lastchange from the comparison.


Andreas

-- 
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Am 02.02.20 um 14:37 schrieb Andreas Kretschmer:
>
>
> Am 02.02.20 um 10:24 schrieb Condor:
>> CREATE TRIGGER last_changes
>>   BEFORE UPDATE ON status_table
>>   FOR EACH ROW
>>   WHEN (OLD.* IS DISTINCT FROM NEW.*) 
>
> try to exclude the column lastchange from the comparison.
>
>

test=*# select ctid, * from status_table ;
  ctid  | rowid | status0 |      lastchage
-------+-------+---------+---------------------
  (0,3) |    11 |       1 |
  (0,5) |    12 |       4 | 2020-02-02 15:40:42
(2 rows)

test=*# UPDATE status_table SET status0 = 4 WHERE rowid = 12;
UPDATE 1
test=*# commit;
COMMIT
test=# select ctid, * from status_table ;
  ctid  | rowid | status0 |      lastchage
-------+-------+---------+---------------------
  (0,3) |    11 |       1 |
  (0,6) |    12 |       4 | 2020-02-02 15:40:42
(2 rows)

test=*# \d status_table
                          Table "public.status_table"
   Column   |              Type              | Collation | Nullable | 
Default
-----------+--------------------------------+-----------+----------+---------
  rowid     | integer                        |           |          |
  status0   | integer                        |           |          |
  lastchage | timestamp(0) without time zone |           |          |
Triggers:
     last_changes BEFORE UPDATE ON status_table FOR EACH ROW WHEN 
(old.rowid IS DISTINCT FROM new.rowid OR old.status0 IS DISTINCT FROM 
new.status0) EXECUTE FUNCTION log_last_changed()


Andreas

-- 
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Condor <condor@stz-bg.com> writes:
> 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.

It is not, at least not for PG (can't say about SQL Server).  But see
suppress_redundant_updates_trigger here:

https://www.postgresql.org/docs/current/functions-trigger.html

            regards, tom lane

Am 02.02.20 um 18:18 schrieb Tom Lane:
>
> https://www.postgresql.org/docs/current/functions-trigger.html
>
>             regards, tom lane
cool.


Andreas

-- 
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

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
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

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

On 03-02-2020 10:29, Andrei Zhidenkov wrote:
> Have you tried to use built-in suppress_redundant_updates_trigger[1]
> for this?
> 
> 1. https://www.postgresql.org/docs/12/functions-trigger.html
> 

No,
solution point me Adrian Klaver it's working for me. I look at page, but 
this probably is build in function and can't be changed.
In my case I need to change lastchange column to know which row is 
changed and when.

Regards,
HS