Automatic update of time column

[赵宇鹏(宇彭)]

Hello,
    
CREATE TABLE test (
    id INT,
    create_time TIMESTAMPTZ DEFAULT now(),
    update_time TIMESTAMPTZ DEFAULT now());
    
With a table like this, users often want the update_time to automatically update
to the current timestamp when executing an UPDATE statement, without having to
explicitly specify the value of update_time. This is a very common requirement,
with lots of information available online.
    
Using the syntax UPDATE test SET id=1, update_time = DEFAULT; is not advisable
because, based on user feedback, it is considered a best practice for
applications not to directly modify columns like update_time. Currently, in
PostgreSQL, this can only be achieved through triggers. However, triggers add
complexity to database management and can degrade performance. In my instance,
the performance dropped by more than 50% (you can verify this with the attached
script).
    
MySQL offers a similar feature through ON UPDATE CURRENT_TIMESTAMP. Is there any
consideration for PostgreSQL to implement this functionality?
    
I have developed a patch, tentatively called on_update_set_default, which uses
attoptions to store a boolean value and implements the logic in the
rewriteTargetListIU function. This approach avoids modifying system table
structures, adding extra syntax, and has no impact on update performance (as can
be validated with the attached script).
    
Thanks for your feedback.
    
Best regards,
Yupeng Zhao

0001-on_update_set_default.patch
Description: Binary data

test.sql

Description: Binary data