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