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
Attachments:
0001-on_update_set_default.patchapplication/octet-streamDownload
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index e587abd999..b6e96008ee 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -166,6 +166,15 @@ static relopt_bool boolRelOpts[] =
},
true
},
+ {
+ {
+ "on_update_set_default",
+ "Automatically set the column to its default value if the column has a default value in an UPDATE statement",
+ RELOPT_KIND_ATTRIBUTE,
+ ShareUpdateExclusiveLock
+ },
+ false
+ },
/* list terminator */
{{NULL}}
};
@@ -2070,7 +2079,8 @@ attribute_reloptions(Datum reloptions, bool validate)
{
static const relopt_parse_elt tab[] = {
{"n_distinct", RELOPT_TYPE_REAL, offsetof(AttributeOpts, n_distinct)},
- {"n_distinct_inherited", RELOPT_TYPE_REAL, offsetof(AttributeOpts, n_distinct_inherited)}
+ {"n_distinct_inherited", RELOPT_TYPE_REAL, offsetof(AttributeOpts, n_distinct_inherited)},
+ {"on_update_set_default", RELOPT_TYPE_BOOL, offsetof(AttributeOpts, on_update_set_default)}
};
return (bytea *) build_reloptions(reloptions, validate,
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index 1a5dfd0aa4..09ee1a29e8 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -41,6 +41,7 @@
#include "rewrite/rewriteSearchCycle.h"
#include "rewrite/rowsecurity.h"
#include "tcop/tcopprot.h"
+#include "utils/attoptcache.h"
#include "utils/builtins.h"
#include "utils/lsyscache.h"
#include "utils/rel.h"
@@ -840,6 +841,7 @@ rewriteTargetListIU(List *targetList,
{
TargetEntry *new_tle = new_tles[attrno - 1];
bool apply_default;
+ bool on_update_set_default = false;
att_tup = TupleDescAttr(target_relation->rd_att, attrno - 1);
@@ -975,6 +977,17 @@ rewriteTargetListIU(List *targetList,
NameStr(att_tup->attname)),
errdetail("Column \"%s\" is a generated column.",
NameStr(att_tup->attname))));
+
+ if (!new_tle)
+ {
+ AttributeOpts *aopt;
+
+ Assert(!apply_default);
+
+ aopt = get_attribute_options(target_relation->rd_id, attrno);
+ if (aopt != NULL && aopt->on_update_set_default)
+ on_update_set_default = true;
+ }
}
if (att_tup->attgenerated)
@@ -1027,6 +1040,24 @@ rewriteTargetListIU(List *targetList,
pstrdup(NameStr(att_tup->attname)),
false);
}
+ else if(on_update_set_default)
+ {
+ Node *new_expr;
+
+ Assert(!new_tle);
+
+ new_expr = build_column_default(target_relation, attrno);
+ /*
+ * If there is a default value, it should be automatically updated
+ * to the default value; otherwise, we should not modify the value
+ * of that column.
+ */
+ if (new_expr)
+ new_tle = makeTargetEntry((Expr *) new_expr,
+ attrno,
+ pstrdup(NameStr(att_tup->attname)),
+ false);
+ }
if (new_tle)
new_tlist = lappend(new_tlist, new_tle);
diff --git a/src/include/utils/attoptcache.h b/src/include/utils/attoptcache.h
index f684a772af..1fce8bedb5 100644
--- a/src/include/utils/attoptcache.h
+++ b/src/include/utils/attoptcache.h
@@ -21,6 +21,7 @@ typedef struct AttributeOpts
int32 vl_len_; /* varlena header (do not touch directly!) */
float8 n_distinct;
float8 n_distinct_inherited;
+ bool on_update_set_default;
} AttributeOpts;
extern AttributeOpts *get_attribute_options(Oid attrelid, int attnum);
"=?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