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



В списке pgsql-general по дате отправления:

Предыдущее
От: "Peter J. Holzer"
Дата:
Сообщение: Re: Need support on tuning at the time of index creation
Следующее
От: Andreas Kretschmer
Дата:
Сообщение: Re: How to avoid UPDATE on same data in table ?