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
"=?UTF-8?B?6LW15a6H6bmPKOWuh+W9rSk=?=" <zhaoyupeng.zyp@alibaba-inc.com> writes:
MySQL offers a similar feature through ON UPDATE CURRENT_TIMESTAMP. Is there any
consideration for PostgreSQL to implement this functionality?
It's been considered and rejected multiple times before. I don't
really see why we'd alter that decision now. The argument that
a trigger is an expensive solution doesn't impress me, because a
built-in fix such as you propose has a distributed cost that is
paid in every query whether the user wants such functionality
or not. There are also interesting semantic problems such as
how can a dump-and-restore happen without causing the stored
timestamps to update? (pg_dump gets around that for triggers
by not installing triggers till after loading data. Yeah,
you could make something similar happen for this feature too,
but that's more code and more complexity that has to propagate
to every tool doing dump-and-restore-like tasks.)
Having said that, there's plenty to be said for providing a
pre-written trigger that's implemented as efficiently as possible,
to minimize users' effort when they want this behavior. We have a
few such things already, such as suppress_redundant_updates_trigger()
and tsvector_update_trigger(). So I think a patch to make a
pre-written trigger for this purpose would be much less controversial.
regards, tom lane