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

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